Notification

Icon
Error

<12
RobG
#30RobG 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
#31HqrNL207 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
#32polar 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
#33ToeJoe Member Original PosterPosts: 14  
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/
Alex Wong
#34Alex Wong Member Posts: 8  
posted: 3/31/2021 3:43:00 PM(UTC)
Dear All, This is a very good report. Is there anyway can add 1 more info where showing the KB release month of KBs installed?

example: Win 10 PC install KB4601319, will show KB release month February 2021.


AssetName | OSName | UserName | IPAddress | LastPatchDate | LastSeen | ***KB release month


Appreciate your assistance to guide on this query.
Thanks.
ToeJoe
#35ToeJoe Member Original PosterPosts: 14  
posted: 7/1/2021 1:01:51 PM(UTC)
Originally Posted by: Alex Wong Go to Quoted Post
Dear All, This is a very good report. Is there anyway can add 1 more info where showing the KB release month of KBs installed?

example: Win 10 PC install KB4601319, will show KB release month February 2021.


AssetName | OSName | UserName | IPAddress | LastPatchDate | LastSeen | ***KB release month


Appreciate your assistance to guide on this query.
Thanks.


If I understand you correctly, you want to show the date when the last KB that got installed was actually released? I think that it could be possible but it would require additional table where each KB relates to release date. I doubt that this info exists within lansweeper database.
Alex Wong
#36Alex Wong Member Posts: 8  
posted: 7/2/2021 3:23:11 AM(UTC)
Dear All,

Thank for your assistance on my request. Lansweeper support has replied below that current scanner does not scan the KB release month of KBs installed.

---

Thank you for contacting Lansweeper technical support.

Unfortunately, Lansweeper does not scan the KB release month of KBs installed. As such, it cannot be added to the report. We've tagged your ticket as a feature request and have added "scan the KB release month of KBs installed" to our customer wish list. Features on our customer wish list get development priority based on a combination of customer demand and difficulty to implement. As such we aren't able to guarantee that this feature will be implemented or provide you with an estimated release date at this time.
<12

Active Discussions

Lansweeper Ticket Content Default Value
by  CPG   Go to last post Go to first unread
Last post: Yesterday at 8:45:12 PM(UTC)
Lansweeper New status to mimic Closed
by  chris.anderson  
Go to last post Go to first unread
Last post: Yesterday at 7:14:27 PM(UTC)
Lansweeper Active Directory Groups not scanned properly
by  cross_eur   Go to last post Go to first unread
Last post: Yesterday at 5:45:00 PM(UTC)
Lansweeper Change Management - Voting and Tracking
by  brodiemac-too  
Go to last post Go to first unread
Last post: Yesterday at 2:48:01 PM(UTC)
Lansweeper No incoming Mails after update 8.4.100.9
by  EDELL   Go to last post Go to first unread
Last post: Yesterday at 2:38:50 PM(UTC)
Lansweeper Anti-Virus on Mac
by  Ian.Prentice  
Go to last post Go to first unread
Last post: Yesterday at 9:30:32 AM(UTC)
Lansweeper how to scan intune managed win10 clients?
by  brodiemac-too   Go to last post Go to first unread
Last post: 7/27/2021 9:17:07 PM(UTC)
Lansweeper Sort by in Helpdesk
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/27/2021 9:15:05 PM(UTC)