Windows devices with Uptime more than 30 days

[PavlinS]

Hello, everyone!

Sorry for the noob question, but I have to create a report for all Windows devices with Uptime more than 30 days. I have managed to do the report and filter it to Windows only AssetType, however I can not figure out how to filter the Uptime criteria to show only devices with Uptime more than 30 days. 
Perhaps the criteria for tblAssets.Uptime or UptimeSinceLastReboot should be modified?

Thank you in advance!

Cheers!

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot
From tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tsysAssetTypes.AssetTypename = 'Windows' And tblState.Statename = 'Active'
And tblAssets.Uptime Is Not Null
Order By tblAssets.Uptime Desc,
tblAssets.IPNumeric,
tblAssets.Domain,
tblAssets.AssetName

[FrankSc]

Hello Pavlin, 
There is a built-in report that shows the uptime of assets, since last reboot. 

You can use the same uptime conditions and joins from that report. To add a where clause on tblassets.uptime, you can use < getdate() -30 for example.

[PavlinS]

Hello, FrankSc!
Thank you very much for the answer! However when I add  < getdate() -30 as a criteria on tblassets.uptime in the build-in report there is an error that prevents the report to be executed - "Arithmetic overflow error converting expression to data type datetime."  I have tried with and without the "tblAssets.Uptime Is Not Null" and still the error pops up.
Can you please tell me what I am doing wrong by checking the code above or where should I edit the code?

Cheers!

[Jacob_H · Accepted solution]

Yo Pavlin - I'm not official support, but tblassets.uptime is not a date - it's actually stored as a numeric value for how many seconds the asset has been up -  that's why it's throwing that error.  Here's how you can fix that:

Where tblState.Statename = 'Active' 
  And tblAssets.Uptime Is Not Null 
  And DATEADD(SECOND, -tblAssets.Uptime, GETDATE()) < DATEADD(DAY, -30, GETDATE())

[PavlinS]

Thank you very much! It worked perfectly! You helped me a lot!