‎06-07-2021 08:15 PM
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.Lastseen,
tblassetcustom.LastPatched,
tblassets.IPAddress,
tblassets.Lasttried,
tblassets.Username
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Where tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.LastPatched Is
Not Null And tblassetcustom.State = 1
Order By tblassetcustom.LastPatched,
tblassets.Lastseen Desc
Solved! Go to Solution.
‎06-08-2021 04:10 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssetCustom.LastPatched,
tblAssets.IPAddress,
tblAssets.Lasttried,
tblAssets.Username,
Case
When tblAssetCustom.LastPatched >= GetDate() - 45 Then '#d4f4be'
Else '#ffadad'
End As Backgroundcolor,
Case
When tblAssetCustom.LastPatched >= GetDate() - 45 Then 'OK'
Else 'Out Of Date'
End As Status
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.LastPatched Is
Not Null And tblAssetCustom.State = 1
Order By tblAssetCustom.LastPatched,
tblAssets.Lastseen Desc
‎06-08-2021 04:19 PM
‎06-08-2021 04:10 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssetCustom.LastPatched,
tblAssets.IPAddress,
tblAssets.Lasttried,
tblAssets.Username,
Case
When tblAssetCustom.LastPatched >= GetDate() - 45 Then '#d4f4be'
Else '#ffadad'
End As Backgroundcolor,
Case
When tblAssetCustom.LastPatched >= GetDate() - 45 Then 'OK'
Else 'Out Of Date'
End As Status
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.LastPatched Is
Not Null And tblAssetCustom.State = 1
Order By tblAssetCustom.LastPatched,
tblAssets.Lastseen Desc
‎06-08-2021 03:45 PM
‎06-08-2021 01:37 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
tblAssets.Lastseen,
Case
When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < DateAdd(day,
-31, GetDate()) And Max(Convert(date,tblQuickFixEngineering.InstalledOn))
>= DateAdd(day, -90, GetDate()) Then 'Yes'
Else 'No'
End As [Over 90 Days]
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By [Over 90 Days] Desc,
lastPatchDate
‎06-08-2021 02:16 PM
Andy.S wrote:
Hi ,
I was looking at this today too and I think I have done it using help from this report :- https://www.lansweeper.com/report/last-windows-update-audit/Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
tblAssets.Lastseen,
Case
When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < DateAdd(day,
-31, GetDate()) And Max(Convert(date,tblQuickFixEngineering.InstalledOn))
>= DateAdd(day, -90, GetDate()) Then 'Yes'
Else 'No'
End As [Over 90 Days]
From tblQuickFixEngineering
Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Description,
tblAssets.Lastseen
Order By [Over 90 Days] Desc,
lastPatchDate
‎06-08-2021 03:26 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now