Windows 7 EOL report to mark devices lastseen older than 90 days as non-active

[Lance]

Hi,

I've been using the following report to upgrade any devices older than Windows 10.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblOperatingsystem.Caption As [Operating System],
  tblAssets.SP As SP,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tsysOS.OSname = 'Win XP' Then '#ffadad'
    When tsysOS.OSname = 'Win 7' And tblAssets.SP <> 1 Then '#ffdb8e'
    When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then '#ffbe38'
    When GetDate() > '12/14/2019' And GetDate() < '01/14/2020' And
      tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then '#ff8b00'
    When GetDate() > '01/14/2020' And tsysOS.OSname = 'Win 7' And
      tblAssets.SP = 1 Then '#ffadad'
    Else '#d4f4be'
  End As backgroundcolor,
  Case
    When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then DateDiff(dd,
      GetDate(), '01/14/2020')
  End As DaysRemaining
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

We've come to realize that there are many devices that are no longer in use that are reporting. To more accurately report, we've decided to mark anything lastseen older than 90 days to non-active so that those don't show up on report but would come back to active if it gets scanned again.

The problem I'm facing with the current report is that although I can see which are lastseen older than 90 days, it doesn't report devices lastseen older than 90 days only.

My ultimate goal here is to mark all active devices lastseen older than 90 days without doing it individually. I was thinking with the report, there might be a way I can do mass mark of non-active.

Am I looking the right way or is there a better way for this?

Thank you

[RCorbeil]

If you want a version of the report with only results where the machines were last seen more than 90 days ago, copy the existing report and modify the WHERE clause. Remove the tblAssetCustom.state=1 (active assets) and add DateDiff(d, tblAssets.Lastseen, GetDate()) > 90 (the number of days between last-seen date and right now is more than 90).

[devansmain]

Forgive me if I'm missing something here, but is there a reason enabling the below options in Configuration > Server Options wouldn't work?

Set assets to non-active if not seen in the last XX days
Automatically make non-active assets active when they are rescanned.