Find all workstations that have not rebooting in a week – Part 2

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:

 

SELECT

            CS.Name0 as ‘PC Name’,

            CS.UserName0 as ‘User ID’,

            OS.lastbootuptime0 as ‘Boot Time’

FROM

            v_GS_COMPUTER_SYSTEM CS,

            v_GS_OPERATING_SYSTEM OS,

            v_GS_WORKSTATION_STATUS WS

WHERE

            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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *