Notification

Icon
Error

12>
RobG
#0RobG Member Posts: 3  
posted: 3/17/2018 2:17:08 PM(UTC)
Hi All,

Just thought I would provide an update; I had more success with the following query

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
tblAssets.Lastseen
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where ISDATE(tblQuickFixEngineering.InstalledOn) = 1
And OSname Not In ('Win 7', 'Win XP', 'Win 8', 'Win 8.1', 'Win 10')
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate Desc

This should return all your MS servers (or others outside the OSnames specified in the where clause) you can obviously remove "And OSname Not In ('Win 7', 'Win XP', 'Win 8', 'Win 8.1', 'Win 10')" if you want all systems.

I was not able to add "tblQuickFixEngineering.InstalledOn <= DateAdd(day, -90, GetDate())" to this where clause and eventually gave up, instead like the others above I created and saved this report to be queried by another

Select Top 1000000 [your report name].AssetName,
[your report name].OSname,
[your report name].Username,
[your report name].IPAddress,
[your report name].lastPatchDate
From [your report name]
Where [your report name].lastPatchDate <= DateAdd(day,
-90, GetDate())
Ordery By [your report name].AssetName

Thanks for the inspiration!!
HqrNL207
#1HqrNL207 Member Posts: 1  
posted: 4/24/2018 8:16:38 PM(UTC)
Looks like we have to use the HAVING clause here for the date exclusion piece.

Quote:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
tblAssets.Lastseen
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where IsDate(tblQuickFixEngineering.InstalledOn) = 1 And
tsysOS.OSname Not In ('Win 7', 'Win XP', 'Win 8', 'Win 8.1', 'Win 10')
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Having Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < GetDate() - 90
Order By lastPatchDate Desc


Hopes this helps someone.
polar
#2polar Member Posts: 4  
posted: 9/25/2018 9:23:52 AM(UTC)
Here's the color coded version if anyone needs it :) I'm not that fluent in SQL, but this does the job and it's still compatible with chart.

Code:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
  tblAssets.Lastseen,
  Case
    When Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) >=
      DateAdd(day, -7, GetDate()) Then '#d4f4be'
    When Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) <
      DateAdd(day, -7, GetDate()) And
      Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) >= DateAdd(day,
      -30, GetDate()) Then '#f7f0ca'
    When Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) <
      DateAdd(day, -31, GetDate()) And
      Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) >= DateAdd(day,
      -90, GetDate()) Then '#f2d59f'
    Else '#f7caca'
  End As backgroundcolor
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen
Order By lastPatchDate


EDIT: corrected a color coding bug
ToeJoe
#3ToeJoe Member Original PosterPosts: 13  
posted: 11/24/2020 3:39:00 PM(UTC)
Well, this report was included in the reports library. Nice!

https://www.lansweeper.com/report/latest-windows-update-audit-chart/
12>

Active Discussions

Lansweeper Rest API for Scanning
by  Brian Smith   Go to last post Go to first unread
Last post: Today at 7:35:27 PM(UTC)
Lansweeper Language translation not working anymore
by  Florian  
Go to last post Go to first unread
Last post: Today at 6:03:19 PM(UTC)
Lansweeper Use current user for scanning credentials?
by  pryan67   Go to last post Go to first unread
Last post: Today at 6:01:33 PM(UTC)
Lansweeper Bluetooth Info
by  FrankSc  
Go to last post Go to first unread
Last post: Today at 5:42:59 PM(UTC)
Lansweeper Question about Scanning Assets and Password Encryption
by  FrankSc   Go to last post Go to first unread
Last post: Today at 5:23:05 PM(UTC)
Lansweeper How to enable HTTPS in ver 6.0.150.60
by  Duncan.Miles  
Go to last post Go to first unread
Last post: Today at 1:11:19 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: Yesterday at 3:22:29 PM(UTC)
Lansweeper MS Edge Chromium LanSweeper Extension development
by  Slim D  
Go to last post Go to first unread
Last post: 1/15/2021 11:39:29 AM(UTC)