cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

SWQL report Cisco Switch Stacks missing information

I have found an issue with the SWQL searches in regards to Muted interfaces on stacked cisco switches.  If you run an SWQL query to report on Muted and Unmanaged interfaces, the query does not return muted interfaces on cisco switch stacks.  It will returned unmanaged interfaces on cisco switch stacks and it will returned muted/unmanaged interfaces on cisco switches that arent in switch stacks.  However it will not return muted interfaces on cisco switch stacks.  For reference, this is the SWQL query i am using. 

SELECT

'Unmanaged' as [Status]

,n.Caption AS [Node]

,i.interfacename as [Interface]

,tostring(tolocal(i.UnManageFrom)) AS [From]

,case when i.UnManageUntil is null or i.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'

else tostring(tolocal(i.UnManageUntil)) end AS [Until]

,case when i.UnManageUntil is null or i.UnManageUntil = '9999-01-01 00:00:00' then '-'

else tostring(daydiff(getutcdate(), i.unmanageuntil)) end as [Days Left]

,n.DetailsURL AS [_LinkFor_Node]

,i.DetailsURL AS [_LinkFor_Interface]

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

,'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface]

,CASE

WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'

ELSE ae.AccountID

END AS [Account]

FROM

Orion.npm.interfaces i

join orion.nodes n on n.nodeid=i.nodeid

JOIN (SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent

FROM Orion.AuditingEvents rec

WHERE rec.AuditingActionType.ActionType = 'Orion.InterfaceUnManaged'

GROUP BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = i.interfaceid

JOIN (SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

FROM Orion.AuditingEvents ae

WHERE ae.AuditingActionType.ActionType = 'Orion.InterfaceUnManaged') ae ON

ae.NetObjectID = i.interfaceid and ae.timeloggedutc=mostrecent.recent

WHERE i.Status = 9 and n.status != 9

union all

(SELECT

'Muted' as [Status]

,n.caption

,i.interfacename as [Interface]

,tostring(tolocal(SuppressFrom)) as [From]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'

else tostring(tolocal(SuppressUntil )) end AS [Until]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'

else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]

,n.DetailsURL AS [_LinkFor_Node]

,i.DetailsURL AS [_LinkFor_Interface]

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

,'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface]

, ae.AccountID AS [Account]

FROM Orion.AlertSuppression asup

join orion.npm.interfaces i on asup.entityuri=i.uri

join orion.nodes n on n.nodeid=i.nodeid

join (

SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent

FROM Orion.AuditingEvents ae

WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')

group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = i.NodeID

join (

SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

FROM Orion.AuditingEvents ae

WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')

Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = i.NodeID and ae.timeloggedutc=mostrecent.recent

)

ORDER BY [node] asc, [status] desc

 

 

Labels (2)
0 Kudos
2 Replies

Since I recognize most of the query there came from one of my queries I feel like I have some guesses on this. I don't have any switch stacks that I can mute to test handy but you should try to confirm that maybe there's something missing in the auditing event tables for stacked switched, change those last 2 joins into left joins and see if your muted interfaces show up. If they still don't show up then that makes it seem like there could be something unusual going on with the interface URI that is connecting the alert suppression table to the interfaces. Maybe stacks use their stack uri on that table instead of the plain interface uri or something like that.
- Marc Netterfield, Github
0 Kudos

Thanks for the response.  Prior to trying your solution I upgraded to 2020.2 and this appears to have resolved the issue.  I can now see muted interfaces on Cisco switch stacks.