Adding Date Paramiter

[Cieran]

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

[Cieran]

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 · Accepted solution]

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