Notification

Icon
Error

Adding Date Paramiter

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

Cieran

Member Original PosterPosts: 4
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
Charles.X
#1Charles.X Member Administration Posts: 1,250  
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: 4  
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 Agent Update (Sent to Agent)
by  vidwhal   Go to last post Go to first unread
Last post: Today at 8:41:32 AM(UTC)
Lansweeper Wrong Ticket ID in Email Subject
by  WhatPasta  
Go to last post Go to first unread
Last post: Today at 12:27:41 AM(UTC)
Lansweeper New Deployment features
by  jprins747   Go to last post Go to first unread
Last post: Today at 12:16:32 AM(UTC)
Lansweeper Deployment ended: Incorrect function on Java update
by  JoshD  
Go to last post Go to first unread
Last post: Yesterday at 10:34:41 PM(UTC)
Lansweeper List all LS users
by  netadminmicron   Go to last post Go to first unread
Last post: Yesterday at 10:22:45 PM(UTC)
Lansweeper Deployment of Updates/Anti Virus
by  KrisNelson  
Go to last post Go to first unread
Last post: Yesterday at 10:09:28 PM(UTC)
Lansweeper Login Lansweeper
by  Andrea   Go to last post Go to first unread
Last post: Yesterday at 12:29:51 PM(UTC)
Lansweeper Lansweeper deployment
by  Alexandru  
Go to last post Go to first unread
Last post: Yesterday at 11:30:28 AM(UTC)