Add IP Column to Node report

Tomlish

Hi all,

I am looking to add an IP column to the current code i have. It should look like this

NodeName | IP | Downtime | Duration

For some reason, i can't get it to work without it breaking the code.

Current code:

select NodeName as [Node Name]
     , concat (
          SUBSTRING(tostring(MAX(e.EVENTTIME)), 1, 4)
          , SUBSTRING(tostring(MAX(e.EVENTTIME)), 5, 2)
          , SUBSTRING(tostring(tolocal(MAX(e.EVENTTIME))), 12, 8)
          ) as Downtime
     , concat (
          HOURDIFF(tolocal(max(e.eventtime)), getdate()) / 24
          , ' Day(s) '
          , HOURDIFF(tolocal(max(e.eventtime)), getdate()) - (HOURDIFF(tolocal(max(e.eventtime)), getdate()) / 24) * 24
          , 'h '
          , MINUTEDIFF(tolocal(max(e.eventtime)), getdate()) - (MINUTEDIFF(tolocal(max(e.eventtime)), getdate()) / 60) * 60
          , 'm'
          ) as Duration
-- ,MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate()) as minutes_since
--HOURDIFF(tolocal(max(e.eventtime)),getdate())/24 as Days,
--HOURDIFF(tolocal(max(e.eventtime)),getdate()) - (HOURDIFF (tolocal(max(e.eventtime)),getdate())/24)*24 as hours,
--MINUTEDIFF(tolocal(max(e.eventtime)),getdate()) - --(MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60 as mins,
from ORION.NODES NODES
inner join ORION.EVENTS e on NODES.NODEID = e.NETWORKNODE
where status = 2
     and E.Eventtype = 1 --and nodes.customproperties.SystemGrouping Like '%CPE%'
group by NodeName
order by MINUTEDIFF(tolocal(MAX(E.EventTime)), getdate()) desc

KMSigma.SWI

You don't need to use a JOIN - you can use a navigation property.

SELECT [E].Nodes.Caption
     , [E].Nodes.IP_Address
     , CONCAT (
          SUBSTRING(ToString(Max([E].EventTime)), 1, 4)
          , SUBSTRING(ToString(Max([E].EventTime)), 5, 2)
          , SUBSTRING(ToString(ToLocal(Max([E].EventTime))), 12, 8)
          ) AS Downtime
     , CONCAT (
          HOURDIFF(ToLocal(Max([E].EventTime)), GetDate()) / 24
          , ' Day(s) '
          , HOURDIFF(ToLocal(Max([E].EventTime)), GetDate()) - (HOURDIFF(ToLocal(Max([E].EventTime)), GetDate()) / 24) * 24
          , 'h '
          , MINUTEDIFF(ToLocal(Max([E].EventTime)), GetDate()) - (MINUTEDIFF(ToLocal(Max([E].EventTime)), GetDate()) / 60) * 60
          , 'm'
          ) AS Duration
FROM Orion.Events AS [E]
GROUP BY [E].Nodes.Caption
     , [E].Nodes.IP_Address
ORDER BY MINUTEDIFF(ToLocal(Max(E.EventTime)), GetDate()) DESC

But I think your logic for "downtime" is a little off.  If you have other SWQL questions, you might be best served by asking in the Orion SDK Forum.