Notification

Icon
Error

Last Patch Date

Posted: Monday, October 26, 2020 2:27:47 PM(UTC)
Dan S

Dan S

Member Original PosterPosts: 3
0
Like
I'm trying to generate a report to show the last patch date of a server.

Logging on to a server and looking at it by Powershell and WMI it looks correct.

Windows PowerShell
Copyright (C) 2014 Microsoft Corporation. All rights reserved.

PS C:\Users> Get-HotFix | Select-Object -Last 1 -Property InstalledOn

InstalledOn
10/3/2020 12:00:00 AM

$lastpatch = Get-WmiObject -ComputerName "Server" Win32_Quickfixengineering | select @
ame="InstalledOn";Expression={$_.InstalledOn -as [datetime]}} | Sort-Object -Property Installedon | select-object -pro
rty installedon -last 1
PS C:\Users\adm.dasmith> Get-Date $lastpatch.InstalledOn -format yyyy-MM-dd
2020-10-03
PS C:\Users\adm.dasmith>

When I look in lansweeper it's months behind. The lastpatchdate shows 16/07/2020

The report I'm running is below, am I looking at the wrong WMI value?



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


Options
Andy.S
#1Andy.S Member Posts: 51  
posted: 11/4/2020 10:06:19 AM(UTC)
Dan, rather than using a report, what date do you see if you look at the patches on that particular asset in Lansweepr - Config - Windows - Quick Fix ?
0
abetzold
#2abetzold Member Posts: 19  
posted: 11/12/2020 12:50:38 AM(UTC)
Originally Posted by: Dan S Go to Quoted Post
I'm trying to generate a report to show the last patch date of a server.

Logging on to a server and looking at it by Powershell and WMI it looks correct.

Windows PowerShell
Copyright (C) 2014 Microsoft Corporation. All rights reserved.

PS C:\Users> Get-HotFix | Select-Object -Last 1 -Property InstalledOn

InstalledOn
10/3/2020 12:00:00 AM

$lastpatch = Get-WmiObject -ComputerName "Server" Win32_Quickfixengineering | select @
ame="InstalledOn";Expression={$_.InstalledOn -as [datetime]}} | Sort-Object -Property Installedon | select-object -pro
rty installedon -last 1
PS C:\Users\adm.dasmith> Get-Date $lastpatch.InstalledOn -format yyyy-MM-dd
2020-10-03
PS C:\Users\adm.dasmith>

When I look in lansweeper it's months behind. The lastpatchdate shows 16/07/2020

The report I'm running is below, am I looking at the wrong WMI value?



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




Hi Dan--

I want to first point out, with the utmost respect, it is bad practice to post your domain admin account which is shown in your original post.

Here is how I do it, I have a query that runs in SQL to update the last patch date using a report that I created. This is the Query:

Code:

UPDATE lansweeperdb.dbo.tblAssetCustom
SET lansweeperdb.dbo.tblAssetCustom.LastPatched = lansweeperdb.dbo.web50repa76b58d39adc4e4a89cd352473add01c.lastPatchDate
From lansweeperdb.dbo.web50repa76b58d39adc4e4a89cd352473add01c
  Join lansweeperdb.dbo.tblAssets On lansweeperdb.dbo.tblAssets.AssetID = lansweeperdb.dbo.web50repa76b58d39adc4e4a89cd352473add01c.AssetID
  Join lansweeperdb.dbo.tsysOS On lansweeperdb.dbo.tblAssets.OScode = lansweeperdb.dbo.tsysOS.OScode
  Join lansweeperdb.dbo.tblAssetCustom On lansweeperdb.dbo.tblAssets.AssetID = lansweeperdb.dbo.tblAssetCustom.AssetID
  Join lansweeperdb.dbo.tblComputersystem On lansweeperdb.dbo.tblAssets.AssetID = lansweeperdb.dbo.tblComputersystem.AssetID


You can see I am referencing a custom report that I use for patch auditing. You can find your generated reportID in the address bar when you are viewing reports. The query for that report looks like this:

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
  tblAssets.Lastseen,
  tblAssetCustom.Custom1
From tblQuickFixEngineering
  Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblQuickFixEngineering.InstalledOn Like '%/%' And tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 6
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssets.Lastseen,
  tblAssetCustom.Custom1
Having tblAssets.AssetName Not Like 'TS%'


The Max(Convert(datetime2,tblQuickFixEngineering.InstalledOn)) As lastPatchDate has errored on me before when bad data got in the field. Correcting the data resolved the issue. Hope it can help!

Adam
0

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)