SQL Query to Count the Number of Software by Name
Siddram
December 23rd, 2008
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. |
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
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
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
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
nice sql script
Thanks,
Eshwar
handy sql code
Would you like to reply?
Login or Register to post your comment.