Report of all computers in company that are using the 'sleep' function

[Jaffo]

I'm trying to build a report using an SQL query to see what computers on our network are using the 'sleep' function of windows when the lid is closed or clicking the 'sleep' option from the power menu.

Can someone help me with this. I know I can get some of this knowledge from each computer using uptime. I'd just like to find the ones altogether at once if possible.

[Mister_Nobody]

Try this:

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Max(Case
    When tblUptime.EventType = 3 Then tblUptime.EventTime
    Else Null
  End) As start_sleep,
  Max(Case
    When tblUptime.EventType = 4 Then tblUptime.EventTime
    Else Null
  End) As end_sleep,
  DateDiff(MINUTE, Max(Case
    When tblUptime.EventType = 3 Then tblUptime.EventTime
    Else Null
  End), Max(Case
    When tblUptime.EventType = 4 Then tblUptime.EventTime
    Else Null
  End)) As [Minutes Asleep]
From tblassets
  Inner Join tblUptime On tblassets.AssetID = tblUptime.AssetId
Where tblUptime.EventType In (3, 4)
Group By tblassets.AssetID,
  tblassets.AssetName
Order By [Minutes Asleep] Desc

[Jaffo]

This is actually looking good. Can you modify it to only list Windows 10 Computers (ones we are targeting here to do the Win11 upgrade to) Also limit to those successfully scanned in the last 90 days, otherwise i'll export and sort to remove those. Thanks for you help

[Mister_Nobody]

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Max(Case
    When tblUptime.EventType = 3 Then tblUptime.EventTime
    Else Null
  End) As start_sleep,
  Max(Case
    When tblUptime.EventType = 4 Then tblUptime.EventTime
    Else Null
  End) As end_sleep,
  DateDiff(MINUTE, Max(Case
    When tblUptime.EventType = 3 Then tblUptime.EventTime
    Else Null
  End), Max(Case
    When tblUptime.EventType = 4 Then tblUptime.EventTime
    Else Null
  End)) As [Minutes Asleep],
  tblassets.OScode,
  tblassets.Lastseen
From tblassets
  Inner Join tblUptime On tblassets.AssetID = tblUptime.AssetId
Where tblUptime.EventType In (3, 4)
Group By tblassets.AssetID,
  tblassets.AssetName,
  tblassets.OScode,
  tblassets.Lastseen
Order By [Minutes Asleep] Desc

[Jaffo]

Its looking good. However it shows OS: Microsoft Windows 11 Pro (x64) and I only want to display the ones that are OS: Microsoft Windows 10 Pro (x64) or any windows 10 only machines that are utilizing sleep. So I can target them for upgrade to windows 11. Thank you for your time helping me with this.

[Mister_Nobody]

You have to add conditions to where section:

And tblassets.OScode= 'your win10build' 

[Jacob_H]

Hm - well... sleep events are logged in the event logs, but are 'informational'  and that category is not captured by default as it would really balloon the database size and slow it down to a crawl.  BUT - you could deploy a powershell script that looks for the events and writes the latest one to the registry - which Lansweeper can then scan the keys:

# Target registry path
$regPath = "HKLM:\SOFTWARE\Lansweeper\SleepTracking"
New-Item -Path $regPath -Force | Out-Null

# Clear previous values
Remove-ItemProperty -Path $regPath -Name LastSleepTime -ErrorAction SilentlyContinue
Remove-ItemProperty -Path $regPath -Name LastWakeTime -ErrorAction SilentlyContinue

# Define start time for filtering
$startTime = (Get-Date).AddDays(-14)

# Get the last sleep event (Event ID 42)
$sleepEvent = Get-WinEvent -FilterHashtable @{
    LogName = 'System'
    ID = 42
    StartTime = $startTime
} | Sort-Object TimeCreated -Descending | Select-Object -First 1

# Get the last wake event (Event ID 1)
$wakeEvent = Get-WinEvent -FilterHashtable @{
    LogName = 'System'
    ID = 1
    StartTime = $startTime
} | Sort-Object TimeCreated -Descending | Select-Object -First 1

# Write results to registry
if ($sleepEvent) {
    Set-ItemProperty -Path $regPath -Name "LastSleepTime" -Value ($sleepEvent.TimeCreated.ToString("yyyy-MM-dd HH:mm:ss"))
}
if ($wakeEvent) {
    Set-ItemProperty -Path $regPath -Name "LastWakeTime" -Value ($wakeEvent.TimeCreated.ToString("yyyy-MM-dd HH:mm:ss"))
}

Then, you can add the below to the registry scanning:

HKEY_LOCAL_MACHINE\SOFTWARE\Lansweeper\SleepTracking  VALUE: LastSleepTime

HKEY_LOCAL_MACHINE\SOFTWARE\Lansweeper\SleepTracking VALUE: LastWakeTime

You can then run SQL reports for the registry keys

-Jacob