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 Adding computer type to Windows 10 report
by  Brandon   Go to last post Go to first unread
Last post: Yesterday at 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)
Lansweeper Windows Activation
by  Hendrik.VE   Go to last post Go to first unread
Last post: 4/16/2021 4:01:47 PM(UTC)
Lansweeper Microsoft True Up: Licenses combined with AD
by  swaelti   Go to last post Go to first unread
Last post: 4/15/2021 12:25:41 PM(UTC)
Lansweeper Cisco Duo and associated registry keys
by  dhoward  
Go to last post Go to first unread
Last post: 4/15/2021 12:30:14 AM(UTC)
Lansweeper Calls closed within SLA
by  TimHolmes1973  
Go to last post Go to first unread
Last post: 4/13/2021 5:25:38 PM(UTC)