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