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

Lansweeper Identifying systems for INTEL-SA-00166
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 3:32:36 PM(UTC)
Lansweeper Inventory for all Connected Headphone
by  Pritpal Karir  
Go to last post Go to first unread
Last post: Yesterday at 2:21:58 AM(UTC)
Report Center PuTTY 0.71 Report
by  Esben.D  
Go to last post Go to first unread
Last post: 3/20/2019 3:34:55 PM(UTC)
Report Center Google Chrome Zero-day vulnerability
by  df1823   Go to last post Go to first unread
Last post: 3/19/2019 3:28:53 PM(UTC)
Report Center Microsoft Patch Tuesday Report – March 2019
by  Esben.D  
Go to last post Go to first unread
Last post: 3/19/2019 3:17:50 PM(UTC)
Lansweeper VMware report: # of Windows Guests
by  Teebs88   Go to last post Go to first unread
Last post: 3/18/2019 8:32:47 PM(UTC)
Lansweeper Get last installed windows update (date)
by  AlexanderH  
Go to last post Go to first unread
Last post: 3/18/2019 4:44:19 PM(UTC)