Report Help - Only show assets past a certain time

[Tythesly]

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?

[Tythesly]

Perfect. That works 😄

Thank you again!

[Andy_Sismey · Accepted solution]

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]

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]

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]

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]

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.