[QueryCorner][June2022] Deep Diving Into Reboots

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.  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!



Added Disclaimer
[edited by: GlennSen at 3:45 PM (GMT -7) on 5 Apr 2023]
  • This is a helpful commend. One thing, the Sophos AutoUpdate reg key:

    HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Sophos\AutoUpdate\UpdateStatus\VolatileFlags

    is an in-memory only volatile key as the name suggests.Evidence being, you can't, even with tamper disabled, create a subkey, e.g. with PS:

    new-item -Path "HKLM:\SOFTWARE\WOW6432Node\Sophos\AutoUpdate\UpdateStatus\VolatileFlags\test"

    This returns the error: "Cannot create a stable subkey under a volatile parent key."

    So it will "vanish" on reboot but the first update post reboot, SophosUpdae.exe will re-create the key at the end of the update, regardless of if the computer requires a reboot. You probably need to check the value of RebootRequired being 1 under the key rather than using the presence of the key/value.

    Would the following do:

    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')

    Thanks.

  • Thanks for sharing your findings! We've edited the post to include this now.

    Kushal Lakhan
    Team Lead, Global Community Support
    Connect with Sophos Support, get alerted, and be informed.
    If a post solves your question, please use the "Verify Answer" button.
    The New Home of Sophos Support Videos!  Visit Sophos Techvids