→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jscanlon
Engaged Sweeper
Hello,
I have 140 pcs approx that automatically switch on everyday at 5am approx and off again at 7am approx.
Is there a way to run a report on lansweeper that will list pcs that did not perform this switch on/off task?
Currently I run a ping at 6am and 8am and manually check one against the other, I would like a more automated way of doing this if possible.
Thanks!
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
Honestly, I'm not sure of the best way to tackle this request. I believe your best bet would be write a query based off of the data from the tblUptime table.

I did put together a quick query that checks for any power on events at 5 AM and power off events at 7 AM within the last 24 hours. The check columns will report back the hour (5 or 7) if a match is found, otherwise it will be null.

Select Distinct Top 100000 tblAssets.AssetID,
tblAssets.AssetName,
T1.PowerOnCheck,
T1.PowerOffCheck
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join (Select tblUptime.AssetId,
tblUptime.EventTime,
tblUptime.EventType,
(Select Top 1 DatePart(HOUR, TU1.EventTime) From tblUptime TU1
Where TU1.AssetId = tblUptime.AssetId And TU1.EventTime >= DateAdd(hh, -24,
GetDate()) And DatePart(HOUR, TU1.EventTime) = 5 And TU1.EventType =
1) As PowerOnCheck,
(Select Top 1 DatePart(HOUR, TU2.EventTime) From tblUptime TU2
Where TU2.AssetId = tblUptime.AssetId And TU2.EventTime >= DateAdd(hh, -24,
GetDate()) And DatePart(HOUR, TU2.EventTime) = 7 And TU2.EventType =
2) As PowerOffCheck
From tblUptime
Where tblUptime.EventTime >= DateAdd(hh, -24, GetDate()) And
(tblUptime.EventType = 1 Or tblUptime.EventType = 2)) T1
On T1.AssetId = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Order By tblAssets.AssetName

View solution in original post

1 REPLY 1
MikeMc
Champion Sweeper II
Honestly, I'm not sure of the best way to tackle this request. I believe your best bet would be write a query based off of the data from the tblUptime table.

I did put together a quick query that checks for any power on events at 5 AM and power off events at 7 AM within the last 24 hours. The check columns will report back the hour (5 or 7) if a match is found, otherwise it will be null.

Select Distinct Top 100000 tblAssets.AssetID,
tblAssets.AssetName,
T1.PowerOnCheck,
T1.PowerOffCheck
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join (Select tblUptime.AssetId,
tblUptime.EventTime,
tblUptime.EventType,
(Select Top 1 DatePart(HOUR, TU1.EventTime) From tblUptime TU1
Where TU1.AssetId = tblUptime.AssetId And TU1.EventTime >= DateAdd(hh, -24,
GetDate()) And DatePart(HOUR, TU1.EventTime) = 5 And TU1.EventType =
1) As PowerOnCheck,
(Select Top 1 DatePart(HOUR, TU2.EventTime) From tblUptime TU2
Where TU2.AssetId = tblUptime.AssetId And TU2.EventTime >= DateAdd(hh, -24,
GetDate()) And DatePart(HOUR, TU2.EventTime) = 7 And TU2.EventType =
2) As PowerOffCheck
From tblUptime
Where tblUptime.EventTime >= DateAdd(hh, -24, GetDate()) And
(tblUptime.EventType = 1 Or tblUptime.EventType = 2)) T1
On T1.AssetId = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Order By tblAssets.AssetName