Device offline report

I build a report that does something similar.  It runs every 2 weeks and kicks back a list of devices that have been offline for 20+ days.  It returns the Device name, IP, Contact, Location, and last seen date/time.

I had to write it in SQL because I couldn't figure out how to get the report writer to do date comparisons.

So if you go into the Report Wizard and select custom SQL/SWQL and paste this, it should work for you.

If you want to adjust the number of days, the last line where it says "day, -20, GETDATE()" is where you'd do that.  Basically it says subtract 20 days from today.  If you change it to Month -1 it should do one month or you can just increase or decrease the number of days to meet your needs.  Hope that helps.

SELECT
n.Caption as 'Display Name'
, n.IP_address as 'IP Address'
, n.Contact as "Asset/SN"
, n.Location as "Location"
, s.LastSystemUpTimePollUtc as "Last Online"

FROM [OrionDB].[dbo].[NodesStatistics] as s
join OrionDB.dbo.Nodes as n on n.nodeid = s.NodeID

  where n.status = 2
  and
  s.LastSystemUpTimePollUtc <= DATEADD(day, -20, GETDATE())