«
»

Query and report all workstations that haven’t rebooted the last 7 days

Kent Agerlund - 3rd October 2009

I recently got a very good question from a student – how can we check the workstations that haven’t restarted during the last 7 days. The intention is to automatically restart the computers. However not until the end-users have been contacted. For that reason I need to create a report and a query.

The report

image

The SQL statement

Create a new empty report and copy this SQL code into the SQL statement box:

SELECT     TOP (100) PERCENT dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, DATEDIFF(Day,
                      dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) AS [Days since last boot], dbo.v_GS_OPERATING_SYSTEM.Caption0
FROM         dbo.v_GS_OPERATING_SYSTEM INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE     (DATEDIFF(Day, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) > 7) AND (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%xp%') OR
                      (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%Windows 7%') OR
                      (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%vista%')
ORDER BY [Days since last boot]

The Query

To use this in a query based collection or normal query paste this WQL into the query statement:

SELECT DISTINCT SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, SMS_G_System_OPERATING_SYSTEM.Caption
FROM SMS_R_System INNER JOIN SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
WHERE (SMS_G_System_OPERATING_SYSTEM.Caption like "%xp%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%vista%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%windows 7%") and (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) >7)

This post was originally posted to: http://scug.dk/blogs/configurationmanager/archive/2009/10/02/report-all-workstations-that-haven-t-rebooted-the-last-7-days.aspx

Category: Config Mgr. Inventory and Reporting, Inventory, Software distribution  |  Comment (RSS)

5 Comments

  1. bigMIKE:

    Hi guys,

    firstly great site with some good info on for us SCCM learners.

    The report code above does not work when pasted into an SCCM report – although if I go to the original post and copy their code all works OK.

    Just thought you should know

    Mike

    16th October 2009, 11:53
  2. Kent Agerlund

    Kent Agerlund:

    Hi Mike,

    Thanks for pointing it out. It was the ‘ thas was formatted wrongly.

    16th October 2009, 13:05

  3. Carlos:

    Hey Kent. When pasting the query into an collection i get all computers that havent been rebooted the last 7 days, but also computers/virtual machines that havent been turned on for quiet a while >2 weeks…. Why is that?

    9th February 2010, 14:52
  4. Kent Agerlund

    Kent Agerlund:

    Hi Carlos,
    The query looks at the lastbootuptime in the database, if that value is greater the 7 it will disply the computer. Your virtual computers are in the database with a value greater than 7 which is why they will be displayed in the report. You could modify the query to rule out virtual machines.

    9th February 2010, 15:07

  5. Carlos:

    I guess i am looking for a different query then. What we need is to find computers with an uptime of more than 48 hours and reboot them. But thanks for your reply anyway:)

    9th February 2010, 15:35

Leave a comment