Skip to content

Report 06A Question

Featured Replies

Hello,

 

I am new to SCCM and reporting, I inherited it. In the report Software 06A - Search for installed software I am trying to add the Uninstall String/UninstallString field to the report but I am unable to get it to appear. I thought I had added it correctly but I am missing something.

 

Thank you.

Edited by loon

  • Author

Thank you Garth. I added the UninstallString in the Query Designer and my tests failed. Below are the changes I added.

 

declare @Loc_Publisher nvarchar(1000)
declare @Loc_ProductName nvarchar(1000)
declare @Loc_Version nvarchar(1000)
if(ISNULL(@Publisher, N'') = N'')
select @Loc_Publisher = N''
else
select @Loc_Publisher = N'%' + @Publisher + N'%'
if(ISNULL(@ProductName, N'') = N'')
select @Loc_ProductName = N''
else
select @Loc_ProductName = N'%' + @ProductName + N'%'
if(ISNULL(@Version, N'') = N'')
select @Loc_Version = N''
else
select @Loc_Version = @Version + N'%'
if ISNULL(@NumberOfRows, 0) <= 0
SELECT @NumberOfRows = 0
IF OBJECT_ID(N'tempdb..#TempMemb_Soft06A') IS NOT NULL
DROP TABLE #TempMemb_Soft06A
CREATE TABLE #TempMemb_Soft06A(ResourceID int, PRIMARY KEY(ResourceID))
INSERT #TempMemb_Soft06A(ResourceID)
select distinct v_FullCollectionMembership_Alias.ResourceID
FROM fn_rbac_FullCollectionMembership(@UserSIDs) v_FullCollectionMembership_Alias inner join fn_rbac_R_System_Valid(@UserSIDs) v_R_System_Valid_Alias
ON v_R_System_Valid_Alias.ResourceID = v_FullCollectionMembership_Alias.ResourceID
where CollectionID = @CollectionID
select TOP(convert(bigint,@NumberOfRows))
NormalizedName as [Product Name],
NormalizedPublisher as [Publisher],
NormalizedVersion as [Version],
FamilyName as [Family Name],
CategoryName as [Category Name],
UninstallString as [uninstall String],
count(ResourceID) as [instance Count],
@CollectionID as [Collection ID]
from
(
Select
ResourceID,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedName,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedPublisher,
dbo.fn_TrimVersionTail(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedVersion, 2) as [NormalizedVersion],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.FamilyName,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.CategoryName,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.UninstallString
from fn_rbac_GS_INSTALLED_SOFTWARE_CATEGORIZED(@UserSIDs) v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias
Where
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.ResourceID IN
(
select ResourceID FROM #TempMemb_Soft06A
) AND
(@Loc_ProductName = '' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedName like @Loc_ProductName)
and (@Loc_Publisher ='' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedPublisher like @Loc_Publisher)
and (@Loc_Version ='' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedVersion like @Loc_Version )
) S_Data
group by FamilyName,
CategoryName,
UninstallString,
NormalizedName,
NormalizedPublisher,
NormalizedVersion
order by [instance Count] desc
DROP TABLE #TempMemb_Soft06A
  • Author

Sorry for got to add that, yes I did.

  • Author

Yes it is and I ordered the "book" too.

I hope that you find it useful. I hang out here so if you have questions, feel free to post them.

But did you notice that within your query above you are missing the "0" for the column name, in all three places?

  • Author

Thank you, got it to work:

 

declare @Loc_Publisher nvarchar(1000)
declare @Loc_ProductName nvarchar(1000)
declare @Loc_Version nvarchar(1000)
if(ISNULL(@Publisher, N'') = N'')
select @Loc_Publisher = N''
else
select @Loc_Publisher = N'%' + @Publisher + N'%'
if(ISNULL(@ProductName, N'') = N'')
select @Loc_ProductName = N''
else
select @Loc_ProductName = N'%' + @ProductName + N'%'
if(ISNULL(@Version, N'') = N'')
select @Loc_Version = N''
else
select @Loc_Version = @Version + N'%'
if ISNULL(@NumberOfRows, 0) <= 0
SELECT @NumberOfRows = 0
IF OBJECT_ID(N'tempdb..#TempMemb_Soft06A') IS NOT NULL
DROP TABLE #TempMemb_Soft06A
CREATE TABLE #TempMemb_Soft06A(ResourceID int, PRIMARY KEY(ResourceID))
INSERT #TempMemb_Soft06A(ResourceID)
select distinct v_FullCollectionMembership_Alias.ResourceID
FROM fn_rbac_FullCollectionMembership(@UserSIDs) v_FullCollectionMembership_Alias inner join fn_rbac_R_System_Valid(@UserSIDs) v_R_System_Valid_Alias
ON v_R_System_Valid_Alias.ResourceID = v_FullCollectionMembership_Alias.ResourceID
where CollectionID = @CollectionID
select TOP(convert(bigint,@NumberOfRows))
NormalizedName as [Product Name],
NormalizedPublisher as [Publisher],
NormalizedVersion as [Version],
FamilyName as [Family Name],
CategoryName as [Category Name],
UninstallString0 as [uninstall String],
count(ResourceID) as [instance Count],
@CollectionID as [Collection ID]
from
(
Select
ResourceID,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedName,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedPublisher,
dbo.fn_TrimVersionTail(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedVersion, 2) as [NormalizedVersion],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.FamilyName,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.CategoryName,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.UninstallString0
from fn_rbac_GS_INSTALLED_SOFTWARE_CATEGORIZED(@UserSIDs) v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias
Where
v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.ResourceID IN
(
select ResourceID FROM #TempMemb_Soft06A
) AND
(@Loc_ProductName = '' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedName like @Loc_ProductName)
and (@Loc_Publisher ='' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedPublisher like @Loc_Publisher)
and (@Loc_Version ='' OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedVersion like @Loc_Version )
) S_Data
group by FamilyName,
CategoryName,
UninstallString0,
NormalizedName,
NormalizedPublisher,
NormalizedVersion
order by [instance Count] desc
DROP TABLE #TempMemb_Soft06A

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.