Hi,
Pleaese help me to get list of interface down alerts through swis query.
Try this:
SELECT Nodes.Caption AS NodeName ,Interfaces.Caption AS InterfaceName ,Alerts.Acknowledged ,Alerts.AcknowledgedDateTime ,Alerts.TriggeredDateTimeFROM Orion.Nodes AS NodesJOIN Orion.NPM.Interfaces AS Interfaces ON Nodes.NodeID = Interfaces.NodeIDJOIN Orion.AlertObjects AS Objects ON Objects.RelatedNodeID = Nodes.NodeID AND Objects.EntityURI = Interfaces.URIJOIN Orion.AlertActive AS Alerts ON Alerts.AlertObjectID = Objects.AlertObjectIDWHERE Interfaces.Status = 2
Hey there,
You're looking for the Orion.NPM.Interfaces entity and the query would be the following:
SELECT Nodes.Caption AS NodeName ,Interfaces.Caption AS InterfaceNameFROM Orion.Nodes AS NodesJOIN Orion.NPM.Interfaces AS Interfaces ON Nodes.NodeID = Interfaces.NodeIDWHERE Interfaces.Status = 2
Thanks. It is working fine . Could you help me to get alertobject I'd and
triggered datetime for interface down alerts. Pls help.
Ah I see what you were asking. Sorry about that. Give this a try. You should have access to both the active alert information if you want additional fields and the alert object information so just add to the SELECT clause as needed:
SELECT Alerts.AlertActiveID ,Alerts.AlertObjectID ,Alerts.TriggeredDateTime ,Objects.RealEntityTypeFROM Orion.AlertActive AS AlertsJOIN Orion.AlertObjects AS Objects ON Alerts.AlertObjectID = Objects.AlertObjectIDWHERE Objects.RealEntityType = 'Orion.NPM.Interfaces'
Am using below query to get interface down alerts, but somewhat am getting duplicate value in results, instead of 2 interface down, it is showing as same interface down twice. please hele me to fix this.
results3=swis.query("SELECT ao.AlertObjectID,I.InterfaceName,ac.AlertMessage as Message,ao.RelatedNodeCaption as Node,ao.AlertID as AlertID
from Orion.NPM.Interfaces I inner join Orion.AlertObjects ao on ao.Node.NodeID=I.Node.NodeID
inner join Orion.AlertActive aa on aa.AlertObjectID=ao.AlertObjectID
inner join Orion.AlertConfigurations ac on ac.AlertID=ao.AlertID
inner join Orion.AlertStatus als on ao.AlertObjectID=als.AlertObjectID
WHERE ac.Severity=2 and als.Acknowledged = 0 and ac.Severity=2 and I.Status=2 and ac.AlertMessage like '%Alert me when an interface goes down%'
and I.InterfaceID in (13961,13962,13963,3246,9060,3522,9088,3445,15372,12437,12438,7100,3404,9470,6741,9123,3362,10986,15282,4738,9119,3528,4289,9939,10142,7176,9118,3499,16236,3439,11797,3453,9823,9824,3545,3546,7005,9491,9492,10741,10742,11849,11852,14605,5133,8800,5274,5549,5584,5585,6725,10122,8710,7159,7762,9161,7974,8763,10551,9323,9399,12185,14549,15846,14588,16378,15847,16375,13956,13957,13959,14715,15754,9934,9996) order by aa.TriggeredDatetime desc")
You don't need that additional join to AlertStatus to get the Acnkowledged state - that's in the AlertActive table. Adjust your query like this:
SELECT ao.AlertObjectID,I.InterfaceName,ac.AlertMessage as Message,ao.RelatedNodeCaption as Node,ao.AlertID as AlertIDfrom Orion.NPM.Interfaces I inner join Orion.AlertObjects ao on ao.Node.NodeID=I.Node.NodeIDinner join Orion.AlertActive aa on aa.AlertObjectID=ao.AlertObjectIDinner join Orion.AlertConfigurations ac on ac.AlertID=ao.AlertIDWHERE ac.Severity=2 and aa.Acknowledged = 0 and ac.Severity=2 and I.Status=2 and ac.AlertMessage like '%Alert me when an interface goes down%'and I.InterfaceID in (13961,13962,13963,3246,9060,3522,9088,3445,15372,12437,12438,7100,3404,9470,6741,9123,3362,10986,15282,4738,9119,3528,4289,9939,10142,7176,9118,3499,16236,3439,11797,3453,9823,9824,3545,3546,7005,9491,9492,10741,10742,11849,11852,14605,5133,8800,5274,5549,5584,5585,6725,10122,8710,7159,7762,9161,7974,8763,10551,9323,9399,12185,14549,15846,14588,16378,15847,16375,13956,13957,13959,14715,15754,9934,9996) order by aa.TriggeredDatetime desc
If i run above query, am not getting any results, but interface down alerts are there.
please help.
What happens when you remove the WHERE clause entirely? Do you get rows?
No Am not getting any results. I execute below query and nothing returned.
results4=swis.query("SELECT I.InterfaceName,ao.RelatedNodeCaption as Node from Orion.NPM.Interfaces I inner join Orion.AlertObjects ao on ao.Node.NodeID=I.Node.NodeID inner join Orion.AlertActive aa on aa.AlertObjectID=ao.AlertObjectID inner join Orion.AlertConfigurations ac on ac.AlertID=ao.AlertID where aa.Acknowledged = 0 and ac.Severity=2 and I.Status=2")
I want you to remove the WHERE clause entirely and see if that returns anything. Try this:
results4=swis.query("SELECT I.InterfaceName,ao.RelatedNodeCaption as Node from Orion.NPM.Interfaces I inner join Orion.AlertObjects ao on ao.Node.NodeID=I.Node.NodeID inner join Orion.AlertActive aa on aa.AlertObjectID=ao.AlertObjectID inner join Orion.AlertConfigurations ac on ac.AlertID=ao.AlertID")
This query gives me exact results but wondering how to join additional attributes
results5=swis.query("SELECT Nodes.Caption AS NodeName ,Interfaces.Caption AS InterfaceName FROM Orion.Nodes AS Nodes JOIN Orion.NPM.Interfaces AS Interfaces ON Nodes.NodeID = Interfaces.NodeID WHERE Interfaces.Status = 2 and Interfaces.InterfaceID in(13961,13962,13963,3246,9060,3522,9088,3445,15372,12437,12438,7100,3404,9470,6741,9123,3362,10986,15282,4738,9119,3528,4289,9939,10142,7176,9118,3499,16236,3439,11797,3453,9823,9824,3545,3546,7005,9491,9492,10741,10742,11849,11852,14605,5133,8800,5274,5549,5584,5585,6725,10122,8710,7159,7762,9161,7974,8763,10551,9323,9399,12185,14549,15846,14588,16378,15847,16375,13956,13957,13959,14715,15754,9934,9996) ")
What additional attributes do you want to add?
Alerobjectid, triggereddatetime , aknowledgement
