Tuesday, August 7, 2007

Query computers to find a specific file

First you will need to make sure that turn on the file inventory, not File collection (we don't want to copy all those files into SMS). A sample is shown to the left. Be sure to exclude the Windows directory at the least.
Once a SW inventory has run you will be able to pull this informaiton. Depending on the state of your machines it might take a while before all have run the check. Be aware that this could decrease the performance of the system if this is the first run of the search.
Now that we have all the information in SMS we need to get it out. The quickest way is to build a query. A more detailed approach would be a report.
select SMS_G_System_COMPUTER_SYSTEM.Name, SMS_G_System_SoftwareFile.FilePath, SMS_G_System_SoftwareFile.CreationDate, SMS_G_System_SoftwareFile.FileDescription, SMS_R_System.ResourceType, SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "test.pdf"
This query can now be used to form a collection or exported to Excel. Limit the collection to the computers you see fit.