KQL script report last reboot/reset endpoint devices (Workstations/Laptops)

Question

I'm reaching out for assistance with a challenge I'm facing in Microsoft Defender. In my organization, we have numerous endpoint devices with vulnerabilities, and I suspect that the issues may stem from either inadequate patching or misconfigured Group Policy Object (GPO) settings preventing updates or reboots.

To investigate further, I need a KQL script that can generate a report showing when each endpoint device was last rebooted or reset, along with the computer name and the last user who logged in to that device.

I've attempted to use the following KQL script in different ways without success:

DeviceEvents | where ActionType == "Restarted" or ActionType == "Shutdown" | summarize LastReboot = max(EventTime) by DeviceName

Despite trying various approaches and searching through online forums, I haven't been able to obtain the desired results. I'm unsure if this information can be retrieved through Defender or if there's an alternative method I should explore.

Any guidance or suggestions would be greatly appreciated as I work to identify and resolve these issues. Thank you for your assistance!

Best regards, Sergio

Answer 1

To generate a report showing when each endpoint device was last rebooted or reset, along with the computer name and the last user who logged in to that device using Microsoft Defender's KQL, you willl need to approach this problem in minimum of three ways. If you have SCCM would have been very better to manage all your devices and generate report. Microsoft Defender doesn't directly provide this information. You will use a combination of Windows Event Logs and Active Directory logs to gather the required data.

In my opinion I would provide step by step guide to do this:

1. To Query Windows Event Logs for System Reboots and Shutdowns:

// Query Windows Event Logs for system reboots and shutdowns

 Event
    | where EventID in (12, 13, 1074, 1076, 6005, 6006) // Event IDs related to system reboots and shutdowns
    | summarize LastReboot = max(EventTime) by Computer

2. To generate query from Active Directory Logs for User Logins:

// Query Active Directory logs for successful user logins

// Replace 'Security' with the appropriate log name if necessary

 SecurityEvent
    | where EventID == 4624 // Event ID for successful logon
    | summarize LastLogin = max(TimeGenerated) by TargetUserName, ComputerName
    


    

3. To join the Results to Get the Desired Report:

   // Join the results to get the final report

    | join kind=fullouter (
    // Query Windows Event Logs for system reboots and shutdowns
    Event
    | where EventID in (12, 13, 1074, 1076, 6005, 6006) // Event IDs related to system reboots and shutdowns
    | summarize LastReboot = max(EventTime) by Computer
   

   ) on Computer | project Computer, LastReboot, LastLogin, TargetUserName

Privacy and Security implication need to be put in to consideration as well as making sure you have appropriate permissions to access these logs and adjust the queries as necessary based on your environment setup.