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.

Trouble building widget to bring in ServiceNow Incident Number and node caption

I am attempting to write a SWQL query for a widget that contains the Incident Number and Caption that is associated with that incident number. I also only want it to display these results for nodes that are in a DOWN state.

I can't find anything in common between Orion.ServiceNow.AlertIncident and Orion.Nodes.

Ideally, this is what i'm going for:

CaptionIncidentNumberAssignedToDescription

I just can't find a table to join these on.  Orion.ServiceNow.AlertIncident has DisplayName, but all the values are null.

Any help would be greatly appreciated. Thank you.

  • i got it! here is the query:

    select e.EntityCaption as [Device], '/Orion/images/StatusIcons/Small-' + o.StatusIcon AS [_IconFor_Device], e.EntityCaption as [_LinkFor_Device],

    tolocal(max(ev.EventTime)) as [Down Time]

    , TOSTRING(HOURDIFF(tolocal(max(ev.eventtime)),getdate())/24) + ' Day(s) ' + TOSTRING((MINUTEDIFF(TOLOCAL(max(ev.EventTime)), GETDATE())) / 60) + 'h ' + TOSTRING((MINUTEDIFF(tolocal(max(ev.eventtime)),getdate())   -   abs((MINUTEDIFF(tolocal(max(ev.eventtime)),getdate())/60)*60))) + 'm' AS Duration, a.IncidentNumber as [Incident Number], a.Assignee as [Owner]

    from Orion.ServiceNow.AlertIncident a

    join Orion.AlertObjects e on a.AlertObjectID = e.AlertObjectID

    join Orion.Nodes o on o.Caption = e.EntityCaption

    join Orion.Events ev on o.NodeID = ev.NetworkNode

    WHERE STATUS = 2 and ev.Eventtype=1 and caption Like '%-FW%' and state not in ('Closed Complete', 'Complete') and IncidentUrl not like '%dev%'

    GROUP BY IncidentNumber, NodeName, StatusIcon, DetailsUrl 

    order by Duration desc