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
Options
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.
0
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.
0

Active Discussions

Lansweeper Changing picture in Assets, New Location error.
by  jmani   Go to last post Go to first unread
Last post: Today at 2:44:27 PM(UTC)
Lansweeper USB printers with toner levels
by  rader   Go to last post Go to first unread
Last post: Today at 12:29:27 AM(UTC)
Lansweeper Showing all assets without a department
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 8:30:43 AM(UTC)
Lansweeper Patch Volume Across Fleet
by  darren.kimber   Go to last post Go to first unread
Last post: Yesterday at 8:29:33 AM(UTC)
Lansweeper Count by Department
by  Brandon   Go to last post Go to first unread
Last post: 4/21/2021 7:42:00 PM(UTC)
Lansweeper Report Request - List assets with old software when newer versions are found
by  Andy.S   Go to last post Go to first unread
Last post: 4/20/2021 3:49:09 PM(UTC)
Lansweeper Adding computer type to Windows 10 report
by  Brandon   Go to last post Go to first unread
Last post: 4/19/2021 6:38:45 PM(UTC)
Report Center Hardware Inventory
by  Cori   Go to last post Go to first unread
Last post: 4/16/2021 4:05:02 PM(UTC)