Query to find the number of machines with a specific product/application
Hi All,
Many a times we have seen customers coming to us requesting us to create a custom report which will list the number of machines with a particular software.
For example suppose you are trying to find the number of machine with SQL server 2008 the best you can do is run the following report.
"Count inventoried products and version for a specific product"
Software\companies and products
The problem with the above report is
It will not give a total count of machine having SQL 2008 rather it will display each machine machines with the version of SQL installed.
The second but the bigger problem is it will not display the name as “SQL server 2008” rather it will give the output something like “10.0.1600.22”
The work around for the above issue is to run the below query in the database. The below query can be used to find the number of machines with the specific application. For this example I am selecting SQL. But you can replace SQL (highlighted in yellow”) with the product you want to query.
select xyz.ProductName, count(*) as Number_of_Machines from
(Select distinct v_R_System_Valid.Netbios_Name0 AS "Computer Name",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS "ProductName",
"Publisher" = CASE
when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher
End,
"Version" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion
End,
"Install Date" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 is NULL ) then 'Unknown'
Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 as varchar)
End,
"Registered User" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0
End
FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED
INNER JOIN v_R_System_Valid on v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID
JOIN v_GS_OPERATING_SYSTEM ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
Where v_GS_OPERATING_SYSTEM.Caption0 like '%server%' and
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName like '%SQL%' and
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName not like '%arcserve%' and
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName not like '%hotfix%' and
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName not like '%books%' and
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName not like '%setup support%'
--order by v_R_System_Valid.Netbios_Name0,v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName, Publisher, Version
)xyz
group by xyz.ProductName
Comments
- Anonymous
January 26, 2015
Thanks - Anonymous
June 12, 2015
Hello SCCM Experts - Any assistance you can provide would be greatly appreciated!
I am trying to edit the current SCCM 2007 Report - "2E - Installed software on a specific computer" so it will report against a collection than a single computer.
The current report SQL Report is below, what should I update that it will allow the use of a collection? What are your thoughts?
Select distinct
v_R_System_Valid.Netbios_Name0 AS "Computer Name",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS "Product Name",
"Publisher" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher
End,
"Version" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion
End,
"Language" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 < 0) then 'Unknown'
Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Language0 as varchar)
End,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallType0 as "Installation Type",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.FamilyName AS "Product Family",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CategoryName AS "Product Category",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductID0 AS "ProductID",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID as "Software ID",
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CM_DSLID0 AS "DSL ID",
"Installed Location" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0 IS NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstalledLocation0
End,
"Install Source" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallSource0
End,
"Uninstall String" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.UninstallString0
End,
"Install Date" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 is NULL ) then 'Unknown'
Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 as varchar)
End,
"Registered User" = CASE when (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 is NULL or v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 = '-1') then 'Unknown'
Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0
End
FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED
INNER JOIN v_R_System_Valid on v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID
Where v_R_System_Valid.Netbios_Name0 = @Name
order by v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName, Publisher, Version