This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Add IP Column to Node report

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

  • 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.