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

Lansweeper SSH - Keyboard Interactive Authentication
by  blackmoonwolf   Go to last post Go to first unread
Last post: Today at 5:12:41 AM(UTC)
Lansweeper Get My Documents Folder Size
by  cbraafhart  
Go to last post Go to first unread
Last post: Yesterday at 2:25:18 PM(UTC)
Lansweeper Lansweeper Errors - Licencing and scanning
by  dnkleaf   Go to last post Go to first unread
Last post: 11/27/2020 1:02:13 PM(UTC)
Lansweeper Search for Blank Fields
by  CS Caritas Socialis IT  
Go to last post Go to first unread
Last post: 11/27/2020 12:19:05 PM(UTC)
Lansweeper Uninstalled software still listed
by  Dennis Gewillig   Go to last post Go to first unread
Last post: 11/27/2020 9:19:19 AM(UTC)
Lansweeper Software deployment initiated by user
by  LS_enthusiast_4444   Go to last post Go to first unread
Last post: 11/26/2020 9:28:18 PM(UTC)
Lansweeper Help Desk not disabling for regular users?
by  FrankSc  
Go to last post Go to first unread
Last post: 11/26/2020 8:18:38 PM(UTC)