How to use SWQL for create KPI Widgets

I would suggest to use one of the following swql statments

If you want to have one tile for Node A,B,C use the following statment fur up interfaces, For down interfaces just set the OperStatus =2

SELECT count(1) As [Status]
FROM Orion.NPM.Interfaces AS [Interf]

WHERE [Interf].OperStatus =1
AND   [Interf].Node.Caption IN ('A','B','C')

For a tile per Node:

SELECT count(1) As [Status]
FROM Orion.NPM.Interfaces AS [Interf]

WHERE [Interf].OperStatus =1
AND   [Interf].Node.Caption = 'A'

SELECT count(1) As [Status]
FROM Orion.NPM.Interfaces AS [Interf]

WHERE [Interf].OperStatus =1
AND   [Interf].Nodeid = <Fill the node id Number>

And to count all interfaces just replace

WHERE [Interf].AdminStatus =1
AND   [Interf].Node.Caption IN ('A','B','C')

By

WHERE [Interf].Node.Caption IN ('A','B','C')

I would suggest to use one of the following swql statments

If you want to have one tile for Node A,B,C use the following statment fur up interfaces, For down interfaces just set the OperStatus =2

SELECT count(1) As [Status]
FROM Orion.NPM.Interfaces AS [Interf]

WHERE [Interf].OperStatus =1
AND   [Interf].Node.Caption IN ('A','B','C')

For a tile per Node:

SELECT count(1) As [Status]
FROM Orion.NPM.Interfaces AS [Interf]

WHERE [Interf].OperStatus =1
AND   [Interf].Node.Caption = 'A'

SELECT count(1) As [Status]
FROM Orion.NPM.Interfaces AS [Interf]

WHERE [Interf].OperStatus =1
AND   [Interf].Nodeid = <Fill the node id Number>

And to count all interfaces just replace

WHERE [Interf].AdminStatus =1
AND   [Interf].Node.Caption IN ('A','B','C')

By

WHERE [Interf].Node.Caption IN ('A','B','C')

Thank You 

Hi edwinhoekman 

I am stuck in SWQL query as mentioned below. I am not good in query. Can u please help to fix the below query.

SELECT n.Caption,n.IP_Address,n.Status,n.DetailsUrl
FROM Orion.Nodes n
WHERE (Caption LIKE '%GRCRY%')

I want to monitor those nodes which is polling through the ICMP only.

Hi ImNishat ,

Are you looking for something like:

SELECT [n].Caption,[n].IP_Address,[n].Status,[n].DetailsUrl
FROM Orion.Nodes AS [n]
WHERE [n].Caption LIKE '%GRCRY%' AND [n].ObjectSubType = 'ICMP' 

Hi edwinhoekman 

Thanks for your quick response and it's working as expected. Need one more modification in this, please see the snapshot of the result as shown below.

I would like to add an additional column immediately following the 'Node Status' column. This new column should display the last date and time for the items in each specific row, essentially indicating the most recent status change of each node from 'UP' to 'DOWN' and vice versa.

I only know how to get the last time the device was up.

SELECT [n].Caption,[n].IP_Address,[n].Status,[n].DetailsUrl, TOLOCAL([n].LastSystemUpTimePollUtc) AS [Down Since]
FROM Orion.Nodes AS [n]
WHERE [n].Caption LIKE '%GRCRY%' AND [n].ObjectSubType = 'ICMP'

But this will probably not work with ICMP. but you can try it in your environent.

Hi edwinhoekman 

I have tried the above query but it's not showing any data, Plz see the below screenshot FYR.

Hi ImNishat ,
Like i already wrote the information of the downtime is not recorded for ICMP. This is why you see the dash
If you change ICMP into SNMP you will probably get some good results (if you have down devices with SNMP)

Noted...!!!

select n.caption, n.IP_Address, si.StatusName, e.EventTime as [Down at]
from orion.Events e
left join orion.nodes n on n.nodeid = e.nodes.nodeid
left join orion.Statusinfo si on si.StatusId = n.Status
where e.EventType = 1
and n.status in( 2,1,3,14)

Using above query i am able to see, but noticing time difference. Plz see one node sample snapshot as given below.

Hi ImNishat ,

It took me a bit longer but maybe this is something you are looking for:

First the Code:

SELECT 
	  CONCAT('<A href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:',[n].NodeID,'" target="_blank">', [n].Caption, '</A>')  AS [Name]
    , [n].IP_Address
    , [n].Status
    , [n].ChildStatus
    , [n].DetailsUrl
    , TOLOCAL(MAX([n].Events.EventTime)) AS Time
FROM Orion.Nodes AS [n]
WHERE       [n].Caption LIKE '%GRCRY%' 
        AND [n].ObjectSubType = 'ICMP'
        AND [n].Events.Eventtype=1
        AND [n].Status IN (1,2,3,14)
GROUP BY  [n].Caption
        , [n].IP_Address
        , [n].Status
        , [n].DetailsUrl
        , [n].ChildStatus
        , [n].NodeID

=================================================================

Next I used a Cusom Table widget.

Added the code as a datasource and used the following layout:

=================================================================

=================================================================

In the Name Column you need to tick the Allow HTML Tags

=================================================================

=================================================================

The end result should look something like:

Thanks edwinhoekman 

I will review this and provide an update as soon as possible. Apologies for not mentioning earlier that I am attempting to implement this on the Modern Dashboard, and the previous query and results I shared pertain to the Modern Dashboard as well.