In yesterday’s Blog I showed how to find all workstations that have not rebooted in the last 7 days – a simple version. While writing this Blog I noticed that I have not shown how to expand simple queries to more complicated queries or to fix common issues with SMS and its data.
Today I will expand this query to fix a common issue with yesterday’s Blog query. In order to truly understand why the query needs to be fixed you must understand how SMS works.
By default whenever a PC is decommissioned or turned off its SMS data is kept within the SMS database until the ”Delete Aged Inventory History” task deletes its records, by default this is set to 90 days. The default hardware inventory is set to 7 days, although most organizations set it to 1 day. Now if you run the query from yesterday you will have to manually filter out all PCs which have been decommissioned for more than 7 days and less than 90 days. Now wouldn’t it be useful to have the query do that for you?
Look at today’s query:
CS.Name0 as ‘PC Name’,
CS.UserName0 as ‘User ID’,
OS.lastbootuptime0 as ‘Boot Time’
CS.ResourceID = OS.ResourceID
and CS.ResourceID = WS.ResourceID
and datediff(dd,lastbootuptime0, Getdate()) > 7
and datediff(dd,WS.LastHWScan,getdate()) < 7
I have added a new view “v_GS_WORKSTATION_STATUS” and done a date compare against the last hardware scan date.