Need Help with custom Sleeptime Report.

[PapaTuck]

I need help with a report that shows sleeptime  for systems. Something like this:

SELECT

Name AS ‘Computer Name’,

LastWakeTime,

LastSleepTime,

DATEDIFF(MINUTE, LastWakeTime,

LastSleepTime) AS ‘Minutes Asleep’

FROM

Computers

WHERE

LastSleepTime IS NOT NULL AND

LastWakeTime IS NOT NULL

ORDER BY

‘Minutes Asleep’ DESC;

Suggestions are much appreciated.

[Mister_Nobody · Accepted solution]

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

[Mister_Nobody]

I have read DB documentation:

You have a chance to solve your task.

[Mister_Nobody]

Hmm, I think there are no such attributes.

https://community.lansweeper.com/t5/general-discussions/device-uptime/m-p/30973/highlight/true#M1566...