cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
pice
Engaged Sweeper III
We are searching for a way to identify computers which are not shut down by their users after the normal office hours and which are producing network load and power consumption through the nights...

Is there a way to generate such a report or to overview the uptimes of alle computers?

Regards PICE
2 REPLIES 2
RCorbeil
Honored Sweeper II
If you want to see where the computers stand based on current uptime, that's easy enough to do. If somebody restarts their computer at the start and end of every day, though, the current uptime won't show you that it was left on for 24h.
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
DateAdd(second, 0 - tblAssets.Uptime, tblAssets.Lastseen) AS LastBoot,
Convert(Decimal(6,2),tblAssets.Uptime / 60 / 60 / 24) AS DaysUp,
Convert(VARCHAR(12),Floor(tblAssets.Uptime / 60 / 60 / 24)) + ' days, '
+ Right('0' + Convert(VARCHAR(12),Floor(tblAssets.Uptime / 60 / 60 % 24)), 2) + ':'
+ Right('0' + Convert(VARCHAR( 2),Floor(tblAssets.Uptime / 60 % 60)), 2) + ':'
+ Right('0' + Convert(VARCHAR( 2),Floor(tblAssets.Uptime % 60)), 2) AS DaysUpPretty
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1
AND DateDiff(d, tblAssets.Lastseen, GetDate()) < 8 -- not interested in machines not seen within a week
AND tblComputersystem.Domainrole < 2 -- not interested in servers (>1)
ORDER BY
tblAssets.Uptime Desc

Probably more useful to you would be something similar to what we see in the uptime calendar that we see on the asset summary page. I believe I read in another thread that being able to report on something like that is a wishlist item.
pice
Engaged Sweeper III
RC62N wrote:
Probably more useful to you would be something similar to what we see in the uptime calendar that we see on the asset summary page. I believe I read in another thread that being able to report on something like that is a wishlist item.


That would be exactly what I am looking for !