We’re currently experiencing a high volume of support requests, which may result in longer response times — thank you for your patience and understanding.
Community FAQ
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Jaffo
Engaged Sweeper II

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.

 

 

6 REPLIES 6
Mister_Nobody
Honored Sweeper III

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
Engaged Sweeper II

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
Honored Sweeper III
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
Engaged Sweeper II

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
Honored Sweeper III

You have to add conditions to where section:

And tblassets.OScode= 'your win10build' 

Jacob_H
Lansweeper Employee
Lansweeper Employee

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

2025-07-16 12_47_53-Administrator_ Windows PowerShell ISE.png

You can then run SQL reports for the registry keys

-Jacob

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now