Last Patch Date

[Dass]

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

[abetzold]

Dan S wrote:
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:

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:

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

[Andy_Sismey]

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 ?