Report Filtering Uptime

[CyberCitizen]

Hi Guy's,

I was hoping an SQL wiz could help me.

I have the below report. I am wanting to limit the report to two criteria.
1st Machine last seen must be within 2 days
2nd Uptime must me more than 7 days

This report will be used to target users to frequently do not restart that cause us issues with updates and problems etc.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Model,
  Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
  As OS,
  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

[CyberCitizen]

The outcome we are wanting to achieve is finding machines that have been scanned within 24 hours but haven't been restarted within a week. So looking at 7 days uptime with the potential 2x days last scanned. So basically machines should be restarted once a business week.

[RCorbeil]

...
WHERE
  ...
  AND DateDiff(n, tblAssets.LastSeen, GetDate()) < 2*24*60
  AND tblAssets.Uptime / 60 / 60 / 24 > 7
...

When dealing with time differences, I prefer to calculate to a finer resolution than what the difference is based on. If you don't care about that, you can calculate DateDiff() in days.

When working with DateDiff(), you have to be careful of what you're asking for, what you're expecting returned, and what's actually returned.

Given:

• Last seen: 2021-02-17 12:01:03.977
  
• GetDate(): 2021-02-19 08:15:44.103
  

Then:

• DateDiff(n, LastSeen, GetDate()) / 60 / 24 = 1
  
• DateDiff(n, LastSeen, GetDate()) / 60.0 / 24 = 1.843055541
  
• DateDiff(d, LastSeen, GetDate())= 2

If you only care that it was last seen at some point during the day two days ago, then calculating using days is sufficient. If you care that it was actually less than 48 hours ago, calculating the difference in hours or minutes is required.