These reports are great but is anyone else finding the dates are wrong when compared to looking up qfe, for example if you run the following in powershell
Get-WmiObject -Class Win32_QuickFixEngineering | Select-Object -Property Description, HotFixID, InstalledOn | Format-Table -Autosize
Look for your most recent date and compare that to above reports, I find that the date on the reports doesn't match with any entries in qfe
However when I check the QuickFix within Lansweeper (Select an Asset | Click Windows | Click QuickFix) these dates match with powershell, so the information must be there, could it be the conversion or Max is failing?
The report Im using has an added line to only retrieve results older than 90 days
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(date,tblQuickFixEngineering.Lastchanged)) As lastPatchDate,
tblAssets.Lastseen
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblQuickFixEngineering.InstalledOn Like '%/%' And
tblQuickFixEngineering.Lastchanged <= DateAdd(day, -90, GetDate())
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate
I believe to resolve the issue the date queried shouldn't be lastchanged but instead InstalledOn, as these values are often more recent, but I've come across an error when trying to query results older than 90 days
Conversion failed when converting date and/or time from character string.
The query :
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(datetime,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
tblAssets.Lastseen
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblQuickFixEngineering.InstalledOn Like '%/%' And
tblQuickFixEngineering.InstalledOn <= DateAdd(day, -90, GetDate())
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By lastPatchDate