Login to participate
Endpoint Management & Virtualization DownloadsRSS

SQL Query to Count the Number of Software by Name

Siddram's picture

The following is the SQL query to count the number of software by name.

Example

Microsoft Project Professional 2007 xxxx
Microsoft Project Standard 2007 xxxx

-------------------Begin Of Code------------------

SELECT 'Microsoft Project Professional 2007', Count(IA.[Name])
FROM
Inv_AeX_AC_Identification IA
JOIN [Inv_AeX_OS_Add_Remove_Programs] AR
ON IA.[_ResourceGuid] = AR.[_ResourceGuid]
WHERE (AR.[Name] Like '%Microsoft Project Professional%2007%')

Union 

SELECT 'Microsoft Project Standard 2007', Count(IA.[Name])
FROM
Inv_AeX_AC_Identification IA
JOIN [Inv_AeX_OS_Add_Remove_Programs] AR
ON IA.[_ResourceGuid] = AR.[_ResourceGuid]
WHERE (AR.[Name] Like '%Microsoft Project Standard%2007%') 

-----------------End Of Code-------------------

Note: If you want to include the name of few more software and get the result as above; then use "UNION" as shown in above query to join two query.

Thanks,
Sid

License: AJSL
By clicking the download link below, you agree to the terms and conditions in the Altiris Juice Software License
Support: User-contributed tools on the Juice are not supported by Altiris Technical Support. If you have questions about a tool, please communicate directly with the author by visiting their profile page and clicking the 'contact' tab.
DeborahAlbrecht's picture

where statement

You may have to tweak your WHERE clause, when I ran this it did not come up with any Project 2007 returns, however, when I changed it to 'Microsoft%Project Professional 2007' it worked like a charm.

There is probably the registered trademark symbol after Microsoft and before Project that is making the wild card required for the proper returns.

-d

-d

Dominique's picture

A lot of manufacturer are

A lot of manufacturer are changing the name of there software between versions and it is a mess after awhile but Deborah is correct the trademark and sometimes some misspelling in the name of the product are causing issues..

Dom

Siddram's picture

In such cases go for Wildcard

In such cases go for Wildcard charecter % between the name as for example

AR.[Name] Like '%Microsoft%Project%Standard%2007%'

Use Wildcard charecter % where you feel there is inconsistency in ARP name of the software

Siddram's picture

If you want to get the Count

If you want to get the Count of application in exact as per Add Remove Programs (ARP) entiry in your environment
In such case, get exact name of the application as in Add Remove Programs.
Note: For example, if the exact name in Add Remove Programs entry is Microsoft Project Standard 2007 then change the following line of code in the querry

WHERE (AR.[Name] = 'Microsoft Project Standard 2007')

Thanks
Sid

Eshwar's picture

nice sql script

 

Thanks,
Eshwar

sskuma5's picture

handy sql code