Report 06A Question

[loon]

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 May 12, 2016 by loon

[GarthMJ]

How exactly did you add the column to the report?

[loon]

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

[GarthMJ]

But did you add a new column to the table?

[loon]

Sorry for got to add that, yes I did.

[GarthMJ]

The column name is UninstallString0

[loon]

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

[GarthMJ]

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?

[loon]

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

[GarthMJ]

I'm glad you got it working.