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


Andy.S
#1Andy.S Member Posts: 38  
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 ?
abetzold
#2abetzold Member Posts: 18  
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

Active Discussions

Report Center Default Browser and Version
by  RC62N  
Go to last post Go to first unread
Last post: 11/26/2020 8:00:54 PM(UTC)
Lansweeper Trying to make a custom report that sorts assets by state
by  mzipperer   Go to last post Go to first unread
Last post: 11/25/2020 5:36:52 PM(UTC)
Lansweeper Report - All Assets with Specfic Default Gateway
by  RC62N  
Go to last post Go to first unread
Last post: 11/25/2020 4:18:08 PM(UTC)
Report Center Windows update report
by  ToeJoe   Go to last post Go to first unread
Last post: 11/24/2020 3:39:00 PM(UTC)
Lansweeper Report - assets
by  Andy.S  
Go to last post Go to first unread
Last post: 11/23/2020 2:42:01 PM(UTC)
Lansweeper Run Report on PCs from a list
by  RC62N   Go to last post Go to first unread
Last post: 11/16/2020 4:23:12 PM(UTC)