
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-20-2014 12:57 PM
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 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
Labels:
- Labels:
-
Built-In Reports
-
Report Center
2 REPLIES 2

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-29-2016 09:19 PM
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-26-2016 10:18 PM
Is it possible to customize in terms of time?
Like, Not seen during last 1 hour or 1 minute.
Like, Not seen during last 1 hour or 1 minute.
