Notification

Icon
Error

Report editing - Edit Report

Posted: Thursday, March 4, 2021 8:35:34 PM(UTC)
jkrato21

jkrato21

Member Original PosterPosts: 1
0
Like
I have the report that shows all the devices not on the current Windows 10 build, which works fine. However, when I run it some machines from previous years show up on it.

How can I add to this report, criteria that only queries devices that have been seen in the last 30 days.



I added:

< GetDate()- 30

But it did not change the results.


Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname As OS,
tblAssets.OScode + '.' + tblAssets.BuildNumber As Build,
tblAssets.Version As [OS Version],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Where (tsysOS.OSname = 'Win 10' And tblAssets.Lastseen < GetDate() - 30 And
tblAssetCustom.State = 1 And tblAssets.BuildNumber Is Not Null And
Cast(Replace(Replace(Replace(tblAssets.OScode, '.', ''), 'S', ''), 'R',
'') As bigint) Not In (Select Max(Cast(Replace(Replace(Replace(tsysOS.OScode,
'.', ''), 'S', ''), 'R', '') As bigint)) As Expr1 From tsysOS
Where tsysOS.Sortorder = 18)) Or
(tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1 And
tblAssets.BuildNumber Is Not Null And tblAssets.BuildNumber Not In (Select
Max(tblAssets.BuildNumber) As Expr1 From tblAssets
Where Cast(Replace(Replace(Replace(tblAssets.OScode, '.', ''), 'S', ''),
'R', '') As bigint) In (Select
Max(Cast(Replace(Replace(Replace(tsysOS.OScode, '.', ''), 'S',
''), 'R', '') As bigint)) As Expr1 From tsysOS
Where tsysOS.Sortorder = 18)))
Order By tblAssets.AssetName
Gary W.
#1Gary W. Member Posts: 3  
posted: 3/5/2021 11:49:50 AM(UTC)
Did you try a (GetDate() - 30) instead? I think the SQL will cheerfully ignore the - 30 (outside the paranthesis, as you show it) as some spurious text. It might also need something like a DATEDIFF command to properly filter the records. My SQL is admittedly gone a little soft since my DBA days, but I think that might help you figure out how to filter the records for the date range you're looking for.

Active Discussions

Lansweeper LsAgent.ini
by  Orion Poplawski   Go to last post Go to first unread
Last post: Yesterday at 4:49:12 PM(UTC)
Lansweeper LsAgent for Windows command line options?
by  Orion Poplawski  
Go to last post Go to first unread
Last post: Yesterday at 4:47:53 PM(UTC)
Lansweeper LSagent force a scan
by  Orion Poplawski   Go to last post Go to first unread
Last post: Yesterday at 4:46:49 PM(UTC)
Lansweeper MS Edge Chromium LanSweeper Extension development
by  steveb  
Go to last post Go to first unread
Last post: 4/16/2021 10:59:56 PM(UTC)
Lansweeper Office 365 v2 Scanning Error
by  DJX   Go to last post Go to first unread
Last post: 4/16/2021 7:37:57 PM(UTC)
Lansweeper Database size growing too large
by  JTempleton  
Go to last post Go to first unread
Last post: 4/16/2021 5:19:22 PM(UTC)
Lansweeper Limit New Ticket Notifications
by  Jeff WP   Go to last post Go to first unread
Last post: 4/16/2021 4:40:43 PM(UTC)
Lansweeper Monitor as an asset
by  tman247  
Go to last post Go to first unread
Last post: 4/16/2021 4:04:19 PM(UTC)