- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-17-2017 04:10 PM
SELECT tblAssets.AssetID, tblAssets.AssetName, tsysOS.OSname, tblAssets.Username, tblAssets.IPAddress, tblAssets.Description,
MAX(CONVERT(datetime, InstalledOn)) as lastPatchDate,
tblAssets.Lastseen
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
Format of column InstalledOn is mm/dd/yyyy and Convert function nicely converts it to datetime. The issue here could be that you may have wrong date in some cell and convert might fail. You should note this report name (db view) because it will be required for the next query.
Next, you can create report with Chart: prefix. This may be better utilized SQL code but it was my first shot. In this query, I wanted only clients using where OSname In ('Win 7', 'Win 8', 'Win 8.1',
'Win 10'). You may customize date period as needed.
Select Case
When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day, -7,
GetDate()) Then '1. less than a week'
When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate < DateAdd(day, -7,
GetDate()) And PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day,
-30, GetDate()) Then '2. less than a month'
When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate < DateAdd(day, -31,
GetDate()) And PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day,
-90, GetDate()) Then '3. less than 3 months' Else '4. more than 3 months'
End As DateRange,
count(*) As NumberOfAssets
From PREVIOUSLYCREATEDREPORTNAME
Where PREVIOUSLYCREATEDREPORTNAME.OSname In ('Win 7', 'Win 8', 'Win 8.1',
'Win 10')
Group By Case
When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day, -7,
GetDate()) Then '1. less than a week'
When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate < DateAdd(day, -7,
GetDate()) And PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day,
-30, GetDate()) Then '2. less than a month'
When PREVIOUSLYCREATEDREPORTNAME.lastPatchDate < DateAdd(day, -31,
GetDate()) And PREVIOUSLYCREATEDREPORTNAME.lastPatchDate >= DateAdd(day,
-90, GetDate()) Then '3. less than 3 months' Else '4. more than 3 months'
End
Now you can call this report via Chart widget and get nice dashboard report on windows updates on clients
- Labels:
-
Finished Reports
-
Report Center
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-08-2022 03:26 PM
I needed a kind of report which shows compliance with internal patch policy. The policy states that servers must be updated monthly, yearly or are excepted from the policy. For example, if we look at two servers that were updated 3 months ago, first one is on monthly patch policy, second is on yearly policy, only second is compliant.
Requirement for this report is to utilize Custom19 field on Assets table and set it as "combobox" giving values monthly, yearly and exception.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname As OS,
tblAssets.Domain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Custom19 As [WU Class],
Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
GetDate()) As [WU Age in Days],
tblAssets.Lastseen,
Case
When tblAssetCustom.Custom19 = 'Yearly' And
DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
GetDate()) >= 365 Then 'Not Compliant'
When tblAssetCustom.Custom19 = 'Monthly' And
DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
GetDate()) >= 60 Then 'Not Compliant'
Else 'Compliant'
End As Status,
Case
When tblAssetCustom.Custom19 = 'Yearly' And
DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
GetDate()) >= 365 Then '#FFCCCC'
When tblAssetCustom.Custom19 = 'Monthly' And
DateDiff(day, Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)),
GetDate()) >= 60 Then '#FFCCCC'
Else '#CCFFCC'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Join tblQuickFixEngineering On
tblAssets.AssetID = tblQuickFixEngineering.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Join tblComputerSystem on tblComputerSystem.AssetID = tblAssets.AssetID
Where tblAssets.AssetType = -1 AND tblComputerSystem.DomainRole > 1 -- servers only, change scope if required
Group By tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Domain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Custom19,
tblAssets.Lastseen
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-02-2021 04:23 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-31-2021 04:43 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-01-2021 02:01 PM
Alex Wong wrote:
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-24-2020 03:39 PM
https://www.lansweeper.com/report/latest-windows-update-audit-chart/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-25-2018 10:23 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-17-2018 02:17 PM
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!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-24-2018 09:16 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-05-2018 11:26 PM
I tried the different date formats but still unable to query results over X amount of days.
I believe the problem is the column data type, the InstalledOn is nvarchar but ideally should be datetime.