Notification

Icon
Error

Report Filtering Uptime

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

CyberCitizen

Member Original PosterPosts: 415
0
Like
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.

Code:
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
RC62N
#1RC62N Member Posts: 522  
posted: 2/19/2021 4:27:12 PM(UTC)
Code:
...
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.
CyberCitizen
#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 Cloud relay data stored time
by  Inaki Gomez   Go to last post Go to first unread
Last post: Today at 10:57:12 AM(UTC)
Lansweeper Scan UPS
by  FrankSc  
Go to last post Go to first unread
Last post: Today at 10:54:23 AM(UTC)
Lansweeper I Need a List of Hostname
by  Nicolas Burd   Go to last post Go to first unread
Last post: Yesterday at 7:31:34 PM(UTC)
Lansweeper User View Customization
by  DarkOne77  
Go to last post Go to first unread
Last post: Yesterday at 5:26:25 PM(UTC)
Lansweeper Acknowlege from Alerts
by  Brandon   Go to last post Go to first unread
Last post: Yesterday at 4:44:06 PM(UTC)
Lansweeper Helpdesk Custom field for working hours
by  Robert Tecklenburg  
Go to last post Go to first unread
Last post: Yesterday at 9:48:28 AM(UTC)
Lansweeper LsAgent.ini
by  IT-EPUDF   Go to last post Go to first unread
Last post: 4/21/2021 11:26:00 PM(UTC)
Lansweeper Lansweeper cisco LWAPP's
by  Scott G  
Go to last post Go to first unread
Last post: 4/21/2021 7:43:18 PM(UTC)