Query on SWQL for KPI Widget for network devices that are down

jasonford1971

Need some swql for kpi widget for network interfaces that are down - i have tried the following but it gives me all the node details i just want the actual amount rather than the interfaces for each node etc, 

SELECT
i.InterfaceName,
i.Caption,
i.Status,
i.Node.Caption
FROM Orion.NPM.Interfaces i

Thanks 

 

taylor.crowley

SELECT COUNT(InterfaceID) AS DownInterfaces
FROM Orion.NPM.Interfaces
WHERE Status = 2

So above we are using the COUNT function, which will just count however many rows are brought back by whatever item you specify in the SELECT.

Here we are just counting the InterfaceIDs as all interfaces have one, and filtering where the status of the interface equals two, which translates to DOWN

If you want to see all of the status numbers and their relevant string translation, they are all listed in the Orion.StatusInfo SWQL table. I will also paste them here:

StausID	StatusName
0	Unknown
1	Up
2	Down
3	Warning
4	Shutdown
5	Testing
6	Dormant
7	NotPresent
8	LowerLayerDown
9	Unmanaged
10	Unplugged
11	External
12	Unreachable
14	Critical
15	PartlyAvailable
16	Misconfigured
17	Undefined
19	Unconfirmed
22	Active
24	Inactive
25	Expired
26	MonitoringDisabled
27	Disabled
28	NotLicensed
29	OtherCategory
30	NotRunning
31	Online
32	OnBattery
33	OnSmartBoost
34	TimedSleeping
35	SoftwareBypass
36	Off
37	Rebooting
38	SwitchedBypass
39	HardwareFailureBypass
40	SleepingUntilPowerReturn
41	OnSmartTrim
42	EcoMode
43	HotStandBy
44	OnBatteryTest
45	Missing