cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tythesly
Engaged Sweeper III
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


So I am trying to figure out how to only show assets that have a last patched date that is 45 days older than the current day. Any ideas?
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
I see, so i dont have this field populated but does this work :

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

View solution in original post

6 REPLIES 6
Tythesly
Engaged Sweeper III
Perfect. That works 😄

Thank you again!
Andy_Sismey
Champion Sweeper III
I see, so i dont have this field populated but does this work :

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
Tythesly
Engaged Sweeper III
Yeah I've started filling out the fields and have the report set to ONLY display assets with that field populated. But I also what to make it so that not only will it only show those with a date set but also only show computers that have a date that is past 45 days from the current date. So like something with a LastPatched date of 6/1/21 would not show up but something from 2/14/21 would appear.
Andy_Sismey
Champion Sweeper III
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
Tythesly
Engaged Sweeper III
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


Yeah unfortunately that is using the dates from windows update. I am trying to get it to use LastPatched from tblAssetCustom.
Andy_Sismey
Champion Sweeper III
Hi,
Looking at the database field descriptions, unless you manually update these they will be null ?

LastPatched datetime ☒ Last time the asset was patched. This field is meant to be filled in manually.