How to use SWQL for create KPI Widgets
1. Count All Interface node A,B,C
2. Count All Interface node A,B,C Status Up
3. Count All Interface node A,B,C Status Down
Example
Hi, try this. Hope this help.
I would suggest to use one of the following swql statmentsIf 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 =1AND [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 =1AND [Interf].Node.Caption = 'A'
SELECT count(1) As [Status]FROM Orion.NPM.Interfaces AS [Interf]WHERE [Interf].OperStatus =1AND [Interf].Nodeid =
And to count all interfaces just replace
WHERE [Interf].AdminStatus =1AND [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].DetailsUrlFROM Orion.Nodes AS [n]WHERE [n].Caption LIKE '%GRCRY%' AND [n].ObjectSubType = 'ICMP'
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.
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 dashIf 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 eleft join orion.nodes n on n.nodeid = e.nodes.nodeidleft join orion.Statusinfo si on si.StatusId = n.Statuswhere e.EventType = 1and 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('', [n].Caption, '') AS [Name] , [n].IP_Address , [n].Status , [n].ChildStatus , [n].DetailsUrl , TOLOCAL(MAX([n].Events.EventTime)) AS TimeFROM 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.
Hi @ImNishat ,So you are looking for something like this:You can load this Modern dashboard into your environment via SWQL Studio. for more detail on how see Import and export modern dashboards.The XML to import:
{"version":1,"dashboards":[{"widgets":[{"unique_key":"812f5c06-5f00-443e-9531-6f66408c1fdf","location":{"x":0,"y":0,"cols":5,"rows":1},"reference":false},{"unique_key":"ef694a25-b8f1-474d-b92c-c86dfd905627","location":{"x":0,"y":2,"cols":5,"rows":1},"reference":false},{"unique_key":"c5a5a318-4378-451b-b476-081ae90fdbd2","location":{"x":0,"y":1,"cols":5,"rows":1},"reference":false},{"unique_key":"da0baf62-5557-42f8-b6d3-780ed6f6a345","location":{"x":0,"y":3,"cols":5,"rows":1},"reference":false}],"parent":null,"feature":null,"groupId":null,"groupMemberName":null,"unique_key":"xlr_icmp_status_overview","name":"XLR: ICMP Status Overview","private":null}],"widgets":[{"type":"table","configuration":{"header":{"properties":{"title":"ICMP Down Nodes","subtitle":"","url":"","description":"","collapsible":false,"collapsed":true}},"table":{"providers":{"dataSource":{"providerId":"TableSwqlDatasourceService","properties":{"swql":"SELECT \n\t [n].Caption AS [Name]\n\t, [n].DetailsUrl\n , [n].IP_Address\n , [n].Status\n , [n].ChildStatus\n , TOLOCAL(MAX([n].Events.EventTime)) AS Time\nFROM Orion.Nodes AS [n]\nWHERE [n].Caption LIKE '%%' \n AND [n].ObjectSubType = 'ICMP'\n AND [n].Events.Eventtype=1\n AND [n].Status =2\nGROUP BY [n].Caption\n , [n].IP_Address\n , [n].Status\n , [n].DetailsUrl\n , [n].ChildStatus\n , [n].NodeID","dataFields":[{"id":"Name","label":"Name","dataType":"System.String"},{"id":"DetailsUrl","label":"DetailsUrl","dataType":"System.String"},{"id":"IP_Address","label":"IP_Address","dataType":"System.String"},{"id":"Status","label":"Status","dataType":"System.Int32"},{"id":"ChildStatus","label":"ChildStatus","dataType":"System.Int32"},{"id":"Time","label":"Time","dataType":"System.DateTime"}],"type":"hand-edit"}},"adapter":{"properties":{"dataSource":{"properties":{"swql":"SELECT \n\t [n].Caption AS [Name]\n\t, [n].DetailsUrl\n , [n].IP_Address\n , [n].Status\n , [n].ChildStatus\n , TOLOCAL(MAX([n].Events.EventTime)) AS Time\nFROM Orion.Nodes AS [n]\nWHERE [n].Caption LIKE '%%' \n AND [n].ObjectSubType = 'ICMP'\n AND [n].Events.Eventtype=1\n AND [n].Status =2\nGROUP BY [n].Caption\n , [n].IP_Address\n , [n].Status\n , [n].DetailsUrl\n , [n].ChildStatus\n , [n].NodeID","dataFields":[{"id":"Name","label":"Name","dataType":"System.String"},{"id":"DetailsUrl","label":"DetailsUrl","dataType":"System.String"},{"id":"IP_Address","label":"IP_Address","dataType":"System.String"},{"id":"Status","label":"Status","dataType":"System.Int32"},{"id":"ChildStatus","label":"ChildStatus","dataType":"System.Int32"},{"id":"Time","label":"Time","dataType":"System.DateTime"}],"type":"hand-edit"}}}}},"properties":{"configuration":{"columns":[{"id":"column_1e5d2e96-747b-48f8-b72c-27c33417e501","formatter":{"componentType":"StatusFormatterComponent","properties":{"dataFieldIds":{"value":"Status"}}},"isActive":true,"label":"Status"},{"id":"column_9cdd618a-b93a-47dc-bbde-d404770a716d","formatter":{"componentType":"LinkFormatterComponent","properties":{"dataFieldIds":{"link":"DetailsUrl","value":"Name"},"targetSelf":false}},"isActive":true,"label":"Name"},{"id":"column_b52772f6-08d3-4f31-8334-0a5d285f4476","formatter":{"componentType":"RawFormatterComponent","properties":{"dataFieldIds":{"value":"IP_Address"}}},"isActive":true,"label":"IP_Address"},{"id":"column_34671585-748f-40eb-a5ea-66bc449cdc5e","formatter":{"componentType":"DatetimeFormatterComponent","properties":{"dataFieldIds":{"value":"Time"},"option":"0","replaceDate":false}},"isActive":true,"label":"Time"}],"sorterConfiguration":{"sortBy":"column_1e5d2e96-747b-48f8-b72c-27c33417e501","descendantSorting":true},"hasVirtualScroll":true,"searchConfiguration":{"enabled":true}}}}},"subtitle":"","description":"","unique_key":"812f5c06-5f00-443e-9531-6f66408c1fdf","name":"ICMP Down Nodes","private":false},{"type":"table","configuration":{"header":{"properties":{"title":"ICMP Warning Nodes","subtitle":"","url":"","description":"","collapsible":false,"collapsed":true}},"table":{"providers":{"dataSource":{"providerId":"TableSwqlDatasourceService","properties":{"swql":"SELECT \n\t [n].Caption AS [Name]\n\t, [n].DetailsUrl\n , [n].IP_Address\n , [n].Status\n , [n].ChildStatus\n , TOLOCAL(MAX([n].Events.EventTime)) AS Time\nFROM Orion.Nodes AS [n]\nWHERE [n].Caption LIKE '%%' \n AND [n].ObjectSubType = 'ICMP'\n AND [n].Events.Eventtype=1\n AND [n].Status = 3\nGROUP BY [n].Caption\n , [n].IP_Address\n , [n].Status\n , [n].DetailsUrl\n , [n].ChildStatus\n , [n].NodeID","dataFields":[{"id":"Name","label":"Name","dataType":"System.String"},{"id":"DetailsUrl","label":"DetailsUrl","dataType":"System.String"},{"id":"IP_Address","label":"IP_Address","dataType":"System.String"},{"id":"Status","label":"Status","dataType":"System.Int32"},{"id":"ChildStatus","label":"ChildStatus","dataType":"System.Int32"},{"id":"Time","label":"Time","dataType":"System.DateTime"}],"type":"hand-edit"}},"adapter":{"properties":{"dataSource":{"properties":{"swql":"SELECT \n\t [n].Caption AS [Name]\n\t, [n].DetailsUrl\n , [n].IP_Address\n , [n].Status\n , [n].ChildStatus\n , TOLOCAL(MAX([n].Events.EventTime)) AS Time\nFROM Orion.Nodes AS [n]\nWHERE [n].Caption LIKE '%%' \n AND [n].ObjectSubType = 'ICMP'\n AND [n].Events.Eventtype=1\n AND [n].Status = 3\nGROUP BY [n].Caption\n , [n].IP_Address\n , [n].Status\n , [n].DetailsUrl\n , [n].ChildStatus\n , [n].NodeID","dataFields":[{"id":"Name","label":"Name","dataType":"System.String"},{"id":"DetailsUrl","label":"DetailsUrl","dataType":"System.String"},{"id":"IP_Address","label":"IP_Address","dataType":"System.String"},{"id":"Status","label":"Status","dataType":"System.Int32"},{"id":"ChildStatus","label":"ChildStatus","dataType":"System.Int32"},{"id":"Time","label":"Time","dataType":"System.DateTime"}],"type":"hand-edit"}}}}},"properties":{"configuration":{"columns":[{"id":"column_1e5d2e96-747b-48f8-b72c-27c33417e501","formatter":{"componentType":"StatusFormatterComponent","properties":{"dataFieldIds":{"value":"Status"}}},"isActive":true,"label":"Status"},{"id":"column_9cdd618a-b93a-47dc-bbde-d404770a716d","formatter":{"componentType":"LinkFormatterComponent","properties":{"dataFieldIds":{"link":"DetailsUrl","value":"Name"},"targetSelf":false}},"isActive":true,"label":"Name"},{"id":"column_b52772f6-08d3-4f31-8334-0a5d285f4476","formatter":{"componentType":"RawFormatterComponent","properties":{"dataFieldIds":{"value":"IP_Address"}}},"isActive":true,"label":"IP_Address"},{"id":"column_34671585-748f-40eb-a5ea-66bc449cdc5e","formatter":{"componentType":"DatetimeFormatterComponent","properties":{"dataFieldIds":{"value":"Time"},"option":"0","replaceDate":false}},"isActive":true,"label":"Time"}],"sorterConfiguration":{"sortBy":"column_1e5d2e96-747b-48f8-b72c-27c33417e501","descendantSorting":true},"hasVirtualScroll":true,"searchConfiguration":{"enabled":true}}}}},"subtitle":"","description":"","unique_key":"ef694a25-b8f1-474d-b92c-c86dfd905627","name":"ICMP Warning Nodes","private":false},{"type":"table","configuration":{"header":{"properties":{"title":"ICMP Critical Nodes","subtitle":"","url":"","description":"","collapsible":false,"collapsed":true}},"table":{"providers":{"dataSource":{"providerId":"TableSwqlDatasourceService","properties":{"swql":"SELECT \n\t [n].Caption AS [Name]\n\t, [n].DetailsUrl\n , [n].IP_Address\n , [n].Status\n , [n].ChildStatus\n , TOLOCAL(MAX([n].Events.EventTime)) AS Time\nFROM Orion.Nodes AS [n]\nWHERE [n].Caption LIKE '%%' \n AND [n].ObjectSubType = 'ICMP'\n AND [n].Events.Eventtype=1\n AND [n].Status =14\nGROUP BY [n].Caption\n , [n].IP_Address\n , [n].Status\n , [n].DetailsUrl\n , [n].ChildStatus\n , [n].NodeID","dataFields":[{"id":"Name","label":"Name","dataType":"System.String"},{"id":"DetailsUrl","label":"DetailsUrl","dataType":"System.String"},{"id":"IP_Address","label":"IP_Address","dataType":"System.String"},{"id":"Status","label":"Status","dataType":"System.Int32"},{"id":"ChildStatus","label":"ChildStatus","dataType":"System.Int32"},{"id":"Time","label":"Time","dataType":"System.DateTime"}],"type":"hand-edit"}},"adapter":{"properties":{"dataSource":{"properties":{"swql":"SELECT \n\t [n].Caption AS [Name]\n\t, [n].DetailsUrl\n , [n].IP_Address\n , [n].Status\n , [n].ChildStatus\n , TOLOCAL(MAX([n].Events.EventTime)) AS Time\nFROM Orion.Nodes AS [n]\nWHERE [n].Caption LIKE '%%' \n AND [n].ObjectSubType = 'ICMP'\n AND [n].Events.Eventtype=1\n AND [n].Status =14\nGROUP BY [n].Caption\n , [n].IP_Address\n , [n].Status\n , [n].DetailsUrl\n , [n].ChildStatus\n , [n].NodeID","dataFields":[{"id":"Name","label":"Name","dataType":"System.String"},{"id":"DetailsUrl","label":"DetailsUrl","dataType":"System.String"},{"id":"IP_Address","label":"IP_Address","dataType":"System.String"},{"id":"Status","label":"Status","dataType":"System.Int32"},{"id":"ChildStatus","label":"ChildStatus","dataType":"System.Int32"},{"id":"Time","label":"Time","dataType":"System.DateTime"}],"type":"hand-edit"}}}}},"properties":{"configuration":{"columns":[{"id":"column_1e5d2e96-747b-48f8-b72c-27c33417e501","formatter":{"componentType":"StatusFormatterComponent","properties":{"dataFieldIds":{"value":"Status"}}},"isActive":true,"label":"Status"},{"id":"column_9cdd618a-b93a-47dc-bbde-d404770a716d","formatter":{"componentType":"LinkFormatterComponent","properties":{"dataFieldIds":{"link":"DetailsUrl","value":"Name"},"targetSelf":false}},"isActive":true,"label":"Name"},{"id":"column_b52772f6-08d3-4f31-8334-0a5d285f4476","formatter":{"componentType":"RawFormatterComponent","properties":{"dataFieldIds":{"value":"IP_Address"}}},"isActive":true,"label":"IP_Address"},{"id":"column_34671585-748f-40eb-a5ea-66bc449cdc5e","formatter":{"componentType":"DatetimeFormatterComponent","properties":{"dataFieldIds":{"value":"Time"},"option":"0","replaceDate":false}},"isActive":true,"label":"Time"}],"sorterConfiguration":{"sortBy":"column_1e5d2e96-747b-48f8-b72c-27c33417e501","descendantSorting":true},"hasVirtualScroll":true,"searchConfiguration":{"enabled":true}}}}},"subtitle":"","description":"","unique_key":"c5a5a318-4378-451b-b476-081ae90fdbd2","name":"ICMP Critical Nodes","private":false},{"type":"table","configuration":{"header":{"properties":{"title":"ICMP Up Nodes","subtitle":"","url":"","description":"","collapsible":false,"collapsed":true}},"table":{"providers":{"dataSource":{"providerId":"TableSwqlDatasourceService","properties":{"swql":"SELECT \n\t [n].Caption AS [Name]\n\t, [n].DetailsUrl\n , [n].IP_Address\n , [n].Status\n , [n].ChildStatus\n , TOLOCAL(MAX([n].Events.EventTime)) AS Time\nFROM Orion.Nodes AS [n]\nWHERE [n].Caption LIKE '%%' \n AND [n].ObjectSubType = 'ICMP'\n AND [n].Events.Eventtype=1\n AND [n].Status IN (1)\nGROUP BY [n].Caption\n , [n].IP_Address\n , [n].Status\n , [n].DetailsUrl\n , [n].ChildStatus\n , [n].NodeID","dataFields":[{"id":"Name","label":"Name","dataType":"System.String"},{"id":"DetailsUrl","label":"DetailsUrl","dataType":"System.String"},{"id":"IP_Address","label":"IP_Address","dataType":"System.String"},{"id":"Status","label":"Status","dataType":"System.Int32"},{"id":"ChildStatus","label":"ChildStatus","dataType":"System.Int32"},{"id":"Time","label":"Time","dataType":"System.DateTime"}],"type":"hand-edit"}},"adapter":{"properties":{"dataSource":{"properties":{"swql":"SELECT \n\t [n].Caption AS [Name]\n\t, [n].DetailsUrl\n , [n].IP_Address\n , [n].Status\n , [n].ChildStatus\n , TOLOCAL(MAX([n].Events.EventTime)) AS Time\nFROM Orion.Nodes AS [n]\nWHERE [n].Caption LIKE '%%' \n AND [n].ObjectSubType = 'ICMP'\n AND [n].Events.Eventtype=1\n AND [n].Status IN (1)\nGROUP BY [n].Caption\n , [n].IP_Address\n , [n].Status\n , [n].DetailsUrl\n , [n].ChildStatus\n , [n].NodeID","dataFields":[{"id":"Name","label":"Name","dataType":"System.String"},{"id":"DetailsUrl","label":"DetailsUrl","dataType":"System.String"},{"id":"IP_Address","label":"IP_Address","dataType":"System.String"},{"id":"Status","label":"Status","dataType":"System.Int32"},{"id":"ChildStatus","label":"ChildStatus","dataType":"System.Int32"},{"id":"Time","label":"Time","dataType":"System.DateTime"}],"type":"hand-edit"}}}}},"properties":{"configuration":{"columns":[{"id":"column_1e5d2e96-747b-48f8-b72c-27c33417e501","formatter":{"componentType":"StatusFormatterComponent","properties":{"dataFieldIds":{"value":"Status"}}},"isActive":true,"label":"Status"},{"id":"column_9cdd618a-b93a-47dc-bbde-d404770a716d","formatter":{"componentType":"LinkFormatterComponent","properties":{"dataFieldIds":{"link":"DetailsUrl","value":"Name"},"targetSelf":false}},"isActive":true,"label":"Name"},{"id":"column_b52772f6-08d3-4f31-8334-0a5d285f4476","formatter":{"componentType":"RawFormatterComponent","properties":{"dataFieldIds":{"value":"IP_Address"}}},"isActive":true,"label":"IP_Address"},{"id":"column_34671585-748f-40eb-a5ea-66bc449cdc5e","formatter":{"componentType":"DatetimeFormatterComponent","properties":{"dataFieldIds":{"value":"Time"},"option":"0","replaceDate":false}},"isActive":true,"label":"Time"}],"sorterConfiguration":{"sortBy":"column_1e5d2e96-747b-48f8-b72c-27c33417e501","descendantSorting":true},"hasVirtualScroll":true,"searchConfiguration":{"enabled":true}}}}},"subtitle":"","description":"","unique_key":"da0baf62-5557-42f8-b6d3-780ed6f6a345","name":"ICMP Up Nodes","private":false}],"remove":null}
Change your settingsaccording to your environment
Yes, Like this as shown in the below Snapshot. But in one table widget and status will change time to time as per ICMP link status. I am able to make, as i shown to you in my earlier response. but the issue is data is not matching as per down or UP status.
