Notification

Icon
Error

Adding Date Paramiter

Posted: Friday, January 4, 2019 12:05:56 PM(UTC)
Cieran

Cieran

Member Original PosterPosts: 5
0
Like
This issue has been solved! Click here to view the solution
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
Esben.D
#1Esben.D Member Administration Posts: 1,497  
posted: 1/7/2019 2:49:22 PM(UTC)
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.

Code:
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
Cieran
#2Cieran Member Original PosterPosts: 5  
posted: 1/8/2019 12:01:20 PM(UTC)
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

Active Discussions

Installer PuTTY 0.71
by  KevinA-REJIS   Go to last post Go to first unread
Last post: 3/20/2019 7:59:24 PM(UTC)
Installer Google Chrome v72.0.3626.121
by  Alexandru  
Go to last post Go to first unread
Last post: 3/15/2019 8:09:58 AM(UTC)
Installer Upgrade Windows 10 to 1803
by  CyberCitizen   Go to last post Go to first unread
Last post: 3/7/2019 2:30:39 AM(UTC)
Installer Winrar 5.7 Install/Update
by  Esben.D  
Go to last post Go to first unread
Last post: 2/28/2019 5:06:34 PM(UTC)
Installer Cmd - WSUS Offline Update installation
by  RumbleZilla   Go to last post Go to first unread
Last post: 2/27/2019 8:39:19 PM(UTC)
Installer Citrix install pack
by  mzipperer  
Go to last post Go to first unread
Last post: 1/17/2019 11:10:21 PM(UTC)
Installer Script - Change Desktop and Lock Screen
by  Kboyer   Go to last post Go to first unread
Last post: 1/8/2019 12:36:03 AM(UTC)
Installer LsAgent for Windows
by  David Goodwin  
Go to last post Go to first unread
Last post: 12/20/2018 2:24:19 PM(UTC)