
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago
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.
- Labels:
-
User-Generated Reports
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago - last edited 2 weeks ago
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago - last edited 2 weeks ago
You have to add conditions to where section:
And tblassets.OScode= 'your win10build'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 weeks ago
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
