Community FAQ
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.

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now