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 Microsoft EDGE browser support
by  Vapoured   Go to last post Go to first unread
Last post: Today at 4:54:30 AM(UTC)
Lansweeper Asset Management - Unattached assets
by  MrDiamond  
Go to last post Go to first unread
Last post: Yesterday at 8:15:16 PM(UTC)
Lansweeper Help Desk not disabling for regular users?
by  Maveritsch   Go to last post Go to first unread
Last post: Yesterday at 3:22:03 PM(UTC)
Lansweeper Lansweeper Fresh Install Hanging, High CPU, Unusable
by  ebars  
Go to last post Go to first unread
Last post: 11/24/2020 7:53:38 PM(UTC)
Lansweeper Forwarding tickets to regular email recipents
by  ChristerT   Go to last post Go to first unread
Last post: 11/24/2020 9:48:15 AM(UTC)
Lansweeper Outlook task and calendar
by  Squack the Duck  
Go to last post Go to first unread
Last post: 11/24/2020 12:40:37 AM(UTC)
Lansweeper Cap on number of routers?
by  MarkPayton   Go to last post Go to first unread
Last post: 11/23/2020 10:04:12 PM(UTC)
Lansweeper Authenticate from CLI
by  blunderdog  
Go to last post Go to first unread
Last post: 11/23/2020 9:57:04 PM(UTC)