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 Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace   Go to last post Go to first unread
Last post: Yesterday at 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU  
Go to last post Go to first unread
Last post: Yesterday at 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin   Go to last post Go to first unread
Last post: Yesterday at 6:08:42 PM(UTC)
Lansweeper Install Adobe Reader Error
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:46:54 PM(UTC)
Report Center Microsoft Outlook email bug EX255650
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 2:30:16 PM(UTC)
Lansweeper Stop deployment
by  Tyler M.  
Go to last post Go to first unread
Last post: Yesterday at 1:59:22 PM(UTC)
Lansweeper "Out of memory" in errorlog.txt
by  dua   Go to last post Go to first unread
Last post: Yesterday at 1:00:42 PM(UTC)
Lansweeper Installation issues
by  Troy  
Go to last post Go to first unread
Last post: Yesterday at 9:24:54 AM(UTC)