→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
CyberCitizen
Honored Sweeper
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
2 REPLIES 2
CyberCitizen
Honored Sweeper
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
Honored Sweeper II
...
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.