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

  • Artículo

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