Share via

Query to find the number of machines with a specific product/application

  • Article

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

 

  1. 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.

  2. 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