‎10-26-2020 02:27 PM
‎11-12-2020 12:50 AM
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
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
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%'
‎11-04-2020 10:06 AM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now