Thursday, February 17, 2011

Collection query to find machines without MS Office versions

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where Client = 1 and Obsolete = 0 and Active = 1 and OperatingSystemNameandVersion not like "%server%" and ResourceId not in (select RESOURCEID from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where (SMS_G_System_SoftwareFile.FileName like "excel.exe" or SMS_G_System_SoftwareFile.FileName like "winword.exe"))

Description: There are different ways to reach out to this solution. The above method is by using software inventory and querying machines with 'excel.exe' or 'winword.exe'. If these exe's present on the machines, it is assumed that it have MS office installed. You can go for additional conditions in the filepath column and restrict it to find only in program files folder etc etc. But this should almost do your job. Once you write this query, then using SUBSELECT method you can filter the machines which don;t have this software.

Also you can make use of mso.dll and adding a filepath condition should do. Another method is by making hardware inventory data from your add or remove programs and querying the displayname. This i won't recommend because you have to put lot of displaynames (ms office 2003, ms office 2007, ms office XP proffessional, ms office enterprise% etc etc) to make sure you are getting accurate result. If you are sure about what are the exact displaynames then go for this. whichever method you choose, you need to make a subselect query to get it done.

Good Luck!