Report editing

[jkrato21]

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

[NV5_IT_GRW]

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.