How to use SWQL for create KPI Widgets

Hi, try this. Hope this help.

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 

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)