Friday, May 18, 2007

Out of date Hardware Inventory sms

Here is a query to create a collection of computers (last hardware scan > 60 days ) that haven't had there HW inventory updated in 60 days, you can change this to what ever time period you want. This will work on both SCCM or SMS

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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-60,GetDate()) )

You could also add a query to this collection to pull all computers with no SMS client then you have all your comptuers in one collection to work with. You could force HW scans on the collection, force Client installs or whatever maintenance you need.