Jump to content


  • 0
loon

Report 06A Question

Question

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

Share this post


Link to post
Share on other sites

9 answers to this question

Recommended Posts

  • 0

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

Share this post


Link to post
Share on other sites

  • 0

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?

Share this post


Link to post
Share on other sites

  • 0

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

Share this post


Link to post
Share on other sites

Join the conversation

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

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.