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