Report Filtering Uptime

Posted: Friday, February 19, 2021 2:27:46 AM(UTC)


Member Original PosterPosts: 415
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,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
  As OS,
  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 =
  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,
#1RC62N Member Posts: 522  
posted: 2/19/2021 4:27:12 PM(UTC)
  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.

  • Last seen: 2021-02-17 12:01:03.977
  • GetDate(): 2021-02-19 08:15:44.103
  • 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.
#2CyberCitizen Member Original PosterPosts: 415  
posted: 2/22/2021 12:28:22 AM(UTC)
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.

Active Discussions

Lansweeper Verify a successful copy to user computer
by  Brandon   Go to last post Go to first unread
Last post: Yesterday at 9:09:50 PM(UTC)
Lansweeper Pablo
by  Pablo  
Go to last post Go to first unread
Last post: 4/20/2021 8:05:07 PM(UTC)
Lansweeper Can Actions work when using when using a Ubuntu machine
by  mzipperer   Go to last post Go to first unread
Last post: 4/1/2021 10:16:34 PM(UTC)
Lansweeper Remove user from local admin group
Go to last post Go to first unread
Last post: 3/24/2021 4:27:30 PM(UTC)
Action Password Status
by  mcgr5   Go to last post Go to first unread
Last post: 3/24/2021 2:07:51 PM(UTC)
Lansweeper Network Scanning
by  MZU  
Go to last post Go to first unread
Last post: 3/7/2021 8:23:10 AM(UTC)
Lansweeper Creating a schduled task on remote computer
by  steveb   Go to last post Go to first unread
Last post: 2/26/2021 7:44:48 PM(UTC)
Lansweeper Uninstall Software action not working?
by  Brandon  
Go to last post Go to first unread
Last post: 2/15/2021 10:03:22 PM(UTC)