Disclaimer: This information is provided as-is for the benefit of the Community. Please contact Sophos Professional Services if you require assistance with your specific environment.

Purpose

Rebooting a computer can be a delicate situation. It can disrupt your users' productivity. It can cause loss of data. And if you don't reboot, often your machines will be left vulnerable and exposed to an unnecessary risk. 

Prerequisites

You may be experiencing results of data that return empty. Often, it is a culprit of a weak auditing policy enabled in Windows. By Default, Windows does not audit for every security event out of the box. You will want to verify that you are auditing for the events above by reviewing Microsoft's Security Auditing Overview documentation. 

Query Focus #1

The following queries are found in the Live Discover & Response Forum. 

This query will return the event logs from the last 90 days that explain why the machines were rebooted. Often, this is valuable for both IT administrative and security endeavors. 

SELECT DISTINCT
      eventid,
      CASE eventid
      WHEN '41' THEN 'BugcheckCode: ' || JSON_EXTRACT(data, '$.EventData.BugcheckCode') || CHAR(10) || 'PowerButtonTimestamp: ' || JSON_EXTRACT(data, '$.EventData.PowerButtonTimestamp') || CHAR(10) || 'If both 0, check scenario 3.' || CHAR(10) || 'else-If PowerButton is not 0, check scenario 2.' || CHAR(10) || 'Else check scenario 1.' || CHAR(10) || 'Review Codes Here: https://docs.microsoft.com/en-us/windows/client-management/troubleshoot-event-id-41-restart' 
      WHEN '1074' THEN JSON_EXTRACT(data, '$.EventData.param2') || ' was ' || JSON_EXTRACT(data, '$.EventData.param5') || ' from ' || JSON_EXTRACT(data, '$.EventData.param1') || ' by ' || JSON_EXTRACT(data, '$.EventData.param7')
      WHEN '1076' THEN 'This machine restarted due to ' || JSON_EXTRACT(data, '$.EventData.param1') || 'and was first logged into by ' || JSON_EXTRACT(data, '$.EventData.param6')
      WHEN '6005' THEN 'System Start Event Log Service Started'
      WHEN '6006' THEN 'System Start Event Log Service Stopped - Improper Shutdown'
      WHEN '6008' THEN 'Previous System Shutdown Unexpected at  ' || JSON_EXTRACT(data, '$.EventData.Data')
      WHEN '6009' THEN 'OS Version Detected at System Startup is ' || JSON_EXTRACT(data, '$.EventData.Data')
      WHEN '6013' THEN 'System Uptime is ' || (RTRIM(SPLIT(TRIM(JSON_EXTRACT(data, '$.EventData.Data'), ','), ',', 0))) || ' seconds'
   END 'Event_Description',
   datetime(time,'unixepoch') Date_Time
   FROM sophos_windows_events swe
WHERE eventid IN (41, 1074, 1076, 6005, 6006, 6008, 6009, 6013)
AND time > strftime('%s','now','-90 days')

Understanding the Code

In our first post, we talked about how using case statements can filter through important events to highlight the most important data. We will follow that path here.

Notice in line 3 and 4 that we begin the determination of what the event ID means in a human readable text. 

CASE eventid
      WHEN '41' THEN 'BugcheckCode: ' || JSON_EXTRACT(data, '$.EventData.BugcheckCode') || CHAR(10) || 'PowerButtonTimestamp: ' || JSON_EXTRACT(data, '$.EventData.PowerButtonTimestamp') || CHAR(10) || 'If both 0, check scenario 3.' || CHAR(10) || 'else-If PowerButton is not 0, check scenario 2.' || CHAR(10) || 'Else check scenario 1.' || CHAR(10) || 'Review Codes Here: https://docs.microsoft.com/en-us/windows/client-management/troubleshoot-event-id-41-restart' 

When the eventID is 41, then it will take the event details out of the data json description and compile a description.

Query Focus #2

This query will return machines that are pending a reboot to complete an update or installation.

WITH RebootEvidence(evidence) AS (
    SELECT
        CAST(group_concat(path, CHAR(10)) AS TEXT)
    FROM
        registry
    WHERE
        (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations')
        OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations2')
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Updates\UpdateExeVolatile' AND data != 0)
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired')
        OR (path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Services\Pending\%-%')
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\PostRebootReporting')
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\RunOnce\DVDRebootSignal')
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending')
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootInProgress')
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\PackagesPending')
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ServerManager\CurrentRebootAttempts')
        OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netlogon\JoinDomain')
        OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netlogon\AvoidSpnSet')
        OR (
            path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName\ComputerName'
            AND data != (SELECT data FROM registry WHERE path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\ComputerName')
        )
        OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Sophos Endpoint Defense\sophosPFRs')
        OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Sophos Endpoint Defense\cleanPFRs')
        OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\AutoUpdate\UpdateStatus\VolatileFlags\RebootRequired' and data='1')
        OR (path = 'HKEY_LOCAL_MACHINE\Software\Sophos\AutoUpdate\UpdateStatus\VolatileFlags\RebootRequired' and data='1')
)
SELECT
    'Yes' rebootPending,
    evidence
FROM RebootEvidence
WHERE evidence != ''

Understanding the Code

In this query, we are going to use virtual tables. Karl_Ackerman covered this concept extensively in this post here. 

We can see here in lines 2 and 3 that the virtual table is being built from within another, existing table on the agent.

SELECT
        CAST(group_concat(path, CHAR(10)) AS TEXT)

The CAST function is critical here. This will take the registry entries that match the query and return them as text variable types. 

Once the table builds the various registry paths that could trigger a reboot, we then call from the virtual table to output results. By using GROUP earlier, endpoints who require multiple reboots will be seen in one column, rather than seeing the same agent multiple times. 

Happy querying!