I created this resource as part of a dashboard I'm building to summarize ASA VPN Tunnel info now that we have that natively in NPM. Thought this might be helpful for any of you who are working on anything similar, or just to see an example of some more aesthetically designed SWQL queries. Feel free to reach out to me directly if you want to see the rest of the dashboard or have any ideas for what you would want to see on an overall ASA Summary view.
Add a Custom Query resource to a summary view and paste this into the box:
select n.node.caption as [Node]
,n.node.IP
,count(n.SourceIPAddress) as [Total Tunnels]
,case when down.down > 0 then '/Orion/images/StatusIcons/Containers/Small-down.gif'
when inac.inactive > 0 then '/Orion/images/StatusIcons/Containers/Small-up.gif'
else '/Orion/images/StatusIcons/Containers/Small-unknown.gif'
end as [_iconfor_Total Tunnels]
,isnull(up.[Up],0) as Up
,'/Orion/ASA/Images/StatusIcons/SiteToSiteTunnel_Up.png' as [_iconfor_Up]
,isnull(down.Down,0) as Down
,'/Orion/ASA/Images/StatusIcons/SiteToSiteTunnel_Down.png' as [_iconfor_Down]
,isnull(inac.Inactive,0) as Inactive
,'/Orion/ASA/Images/StatusIcons/SiteToSiteTunnel_Inactive.png' as [_iconfor_Inactive]
from orion.vpn.L2LTunnel n
left join (select nodeid, count(SourceIPAddress) as [Up] from orion.vpn.L2LTunnel where Status=1 group by nodeid) up on up.nodeid=n.nodeid
left join (select nodeid, count(SourceIPAddress) as [Down] from orion.vpn.L2LTunnel where Status=2 group by nodeid) down on down.nodeid=n.nodeid
left join (select nodeid, count(SourceIPAddress) as [Inactive] from orion.vpn.L2LTunnel where Status=24 group by nodeid) inac on inac.nodeid=n.nodeid
--Where (n.node.caption like '%${SEARCH_STRING}%' or n.node.ip like '%${SEARCH_STRING}%')
group by n.node.caption ,n.node.IP, up.[Up], down.Down, inac.Inactive
order by n.node.caption ,n.node.IP
In the second box paste the same query in, but remove the comment marks (--) from the Where line near the end.
You should have something like this:
-Marc Netterfield
Loop1 Systems: SolarWinds Training and Professional Services
