cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Cieran
Engaged Sweeper II
Hi Guys,

I've got a report that shows all of the devices that don't have are AV on it, want I want to be able to add now is to show only the devices that have been seen this year.

I know you can do it for like the last however many days using Where tblAssets.Firstseen > GetDate() - 7 And tblAssetCustom.State = 1 but how can I change it so that it is devices last seen after 01/01/2019

Bellow is what I have

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Trend Micro Security Agent') And
Where tblAssets.Lastseen > Date > 01-01-19 And tblAssetCustom.State = 1
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

Any help would be greatly appreciated
1 ACCEPTED SOLUTION
Esben_D
Lansweeper Employee
Lansweeper Employee
You were pretty close. However it seemed you had 2 where clauses in your query, you only need one. Just having tblAssets.Lastseen > '01/01/2019' should suffice.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Trend Micro Security Agent') And
tblAssets.Lastseen > '01/01/2019' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

View solution in original post

2 REPLIES 2
Cieran
Engaged Sweeper II
Ah great thanks for that, do you also know how to add a second variable for another software name, I have the changes bellow but it doesn't add any new devices to the report

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where (tblSoftwareUni.softwareName Like 'Trend Micro Security Agent' Or
tblSoftwareUni.softwareName Like 'Team Viewer')) And
tblAssets.Lastseen > '01/01/2019' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

Thanks

Cieran Paice
Esben_D
Lansweeper Employee
Lansweeper Employee
You were pretty close. However it seemed you had 2 where clauses in your query, you only need one. Just having tblAssets.Lastseen > '01/01/2019' should suffice.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'Trend Micro Security Agent') And
tblAssets.Lastseen > '01/01/2019' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName