Assets: Not seen in the last 30 days (Built-in)

[Daniel_B]

Old name: Device: Not seen in the last 30 days (Built-in)

The report below lists all assets not seen during the last 30 days

The report will only list assets that meet all of the following criteria:

• The asset state is set to "active".
  
• The asset has been successfully scanned at least once.
  
• The last seen date of the asset is more than 30 days ago.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Coalesce(tsysOS.OSname, tblMacOSInfo.SystemVersion, tblLinuxSystem.OSRelease)
  As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblAssets.Lastseen < GetDate() - 30 And tblState.Statename = 'Active'
Order By tblAssets.IPNumeric,
  tblAssets.Domain,
  tblAssets.AssetName

[MikeMc]

You can adjust the where filter and use the DATEADD function for the query (https://msdn.microsoft.com/en-us/library/ms186819.aspx). I updated the original query to check back one hour. You can use 'DATEADD(minute, -1, GETDATE())' to check back one minute.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetName,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Location,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssetCustom.AssetID
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Where tblAssets.Lastseen < DATEADD(hour, -1, GETDATE()) And tblAssetCustom.State = 1
  And tblAssets.Assettype <> -1
Order By tblAssets.AssetName

[sumatia]

Is it possible to customize in terms of time?

Like, Not seen during last 1 hour or 1 minute.