Newsgroup reply…

Select distinct
 R.Netbios_Name0 AS “Computer Name”,
 ISC.NormalizedName AS “Product Name”,
 “Publisher” = CASE when (CAST(ISC.NormalizedPublisher as varchar) is NULL or CAST(ISC.NormalizedPublisher as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.NormalizedPublisher as varchar) End,
 “Version” = CASE when (CAST(ISC.NormalizedVersion as varchar) is NULL or CAST(ISC.NormalizedVersion as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.NormalizedVersion as varchar)End,
 “Language” = CASE when (CAST(ISC.Language0 as varchar) is NULL or CAST(ISC.Language0 as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.Language0 as varchar) End,
 ISC.FamilyName AS “Product Family”,
 ISC.CategoryName AS “Product Category”,
 ISC.ProductID0 AS “ProductID”,
 ISC.SoftwareID as “Software ID”,
 ISC.CM_DSLID0 AS “DSL ID”,
 “Installed Location” = CASE when (CAST(ISC.InstalledLocation0 as varchar) is NULL or CAST(ISC.InstalledLocation0 as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.InstalledLocation0 as varchar) End,
 “Install Source” = CASE when (CAST(ISC.InstallSource0 as varchar) is NULL or CAST(ISC.InstallSource0 as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.InstallSource0 as varchar) End,
 “Uninstall String” = CASE when (CAST(ISC.UninstallString0 as varchar) is NULL or CAST(ISC.UninstallString0 as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.UninstallString0 as varchar) End,
 “Install Date” = CASE when (CAST(ISC.InstallDate0 as varchar) is NULL or CAST(ISC.InstallDate0 as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.InstallDate0 as varchar) End,
 “Registered User” = CASE when (CAST(ISC.RegisteredUser0 as varchar) is NULL or CAST(ISC.RegisteredUser0 as varchar) = ‘-1’) then ‘Unknown’ Else CAST(ISC.RegisteredUser0 as varchar) End
FROM
 dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED ISC INNER JOIN dbo.v_R_System R on R.ResourceID = ISC.ResourceID,
 dbo.v_FullCollectionMembership FCM
Where
R.ResourceID = FCM.ResourceID
and FCM.CollectionID = ‘SMS000DS’
order by
 ISC.NormalizedName,
 Publisher,
 Version

Leave a Reply

Your email address will not be published. Required fields are marked *