Uptime Calendar: Simple report showing the power state changes of the last week

[Bart_M]

All scanned power changes of the active assets limited to the last week.
These are the events the uptime calendar in the web console asset summary page is based on.

Meets all the following criteria:
- Lastseen in last week
- Last power change in last week
- Active asset

Sorted on:
- Domain
- AssetName
- Event time (DESC)

Remark:
As from Windows 8.0 a normal shutdown will be listed as a "Start sleep"

Select Top 1000000 tblAssets.AssetID,
  tblAssets.Domain,
  tblAssets.AssetName,
  tsysOS.OSname As OS,
  tblUptime.EventTime,
  Case tblUptime.EventType When 1 Then 'power on' When 2 Then 'power off'
    When 3 Then 'Start sleep' When 4 Then 'End sleep'
    When 5 Then 'Unexpected shutdown' End As Event,
  tblAssets.Lastseen,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblUptime On tblAssets.AssetID = tblUptime.AssetId
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblUptime.EventTime > GetDate() - 7 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblUptime.EventTime Desc