Approved

Query for System Reboots/Shutdowns

Posted this for easier access as I am sharing it with another community user who looked for this functionality:

SELECT DISTINCT
      eventid,
      CASE eventid
      WHEN '41' THEN 'Rebooted without clean shutdown'
      WHEN '1074' THEN 'Shutdown properly by user or process'
      WHEN '1076' THEN 'First user with shutdown privs logged on server after unexpected restart/shutdown and specified the cause'
      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'
      WHEN '6009' THEN 'OS Version Detected at System Startup'
      WHEN '6013' THEN 'System Uptime in Seconds'
   END 'Event_ID_Desc',
   data,
   time AS Epoch_Time,
   datetime(time,'unixepoch') Data_Time
   FROM sophos_windows_events swe
WHERE eventid IN (41, 1074, 1076, 6005, 6006, 6008, 6009, 6013)
AND time > strftime('%s','now','-90 days') 

This will return the last 90 days of reboots or shutdowns, using a case statement as a brief description, and showing the full event data.

You could use a json extract function to break out the data column further, as an example:

CASE json_extract(swe.data, '$.EventData.<SOMEVALUE>')

Each eventID has a separate <SOMEVALUE> type, as an example "param1" shows in 1074 while "binary" shows in 6005, 6006, and 6008.

Parents
  • thanks. modified it to find bluescreen events (1001)

    SELECT DISTINCT
          eventid,
          CASE eventid
          WHEN '1001' THEN 'BSOD'
       END 'Event_ID_Desc',
       data,
       time AS Epoch_Time,
       datetime(time,'unixepoch') Data_Time
       FROM sophos_windows_events swe
    WHERE eventid IN (41)
    AND time > strftime('%s','now','-90 days')

Comment
  • thanks. modified it to find bluescreen events (1001)

    SELECT DISTINCT
          eventid,
          CASE eventid
          WHEN '1001' THEN 'BSOD'
       END 'Event_ID_Desc',
       data,
       time AS Epoch_Time,
       datetime(time,'unixepoch') Data_Time
       FROM sophos_windows_events swe
    WHERE eventid IN (41)
    AND time > strftime('%s','now','-90 days')

Children
No Data