This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

ASA Details (swql)

cscoengineer over 4 years ago

I had a need to display the ASA information for all of the ASA (it can be filtered down to one if needed). It takes up a lot less screen than the built in widgets.

SELECT

toupper(SUBSTRING(vpn.Node.Caption,1,case when charindex('.',vpn.Node.Caption,1) <=4 then length(vpn.Node.Caption) else (charindex('.',vpn.Node.Caption,1)-1) end)) AS [Node Name]

,vpn.node.DetailsUrl AS [_LinkFor_Node Name]

,'/Orion/images/StatusIcons/Small-' + vpn.node.StatusIcon AS [_IconFor_Node Name]

,case when vpn.status=1 then tostring(vpn.StartTime) else concat('Failed at ',vpn.FailPhaseDescription) end as [Start Time]

,vpn.DisplayName as [DisplayName]

,case when vpn.status=1 then '/Orion/images/StatusIcons/Small-Up.gif'

else '/Orion/images/StatusIcons/Small-Down.gif' end as [_Iconfor_DisplayName]

,CONCAT(HOURDIFF(tolocal(vpn.StartTime),getdate())/24,' Day(s) ',

HOURDIFF(tolocal(vpn.StartTime),getdate())-(HOURDIFF(tolocal(vpn.StartTime),getdate())/24)*24,'h ',

MINUTEDIFF(tolocal(vpn.StartTime),getdate())-(MINUTEDIFF(tolocal(vpn.StartTime),getdate())/60)*60,'m') AS Duration

FROM Orion.VPN.L2LTunnel vpn

group by vpn.Node.Caption, vpn.StartTime, vpn.FailPhaseDescription, vpn.Status, vpn.DisplayName, vpn.nodeid, vpn.node.DetailsUrl, vpn.node.StatusIcon

order by vpn.Status desc

TOUPPER(SUBSTRING(ras.Node.Caption,1,case when charindex('.',ras.Node.Caption,1) <=4 then length(ras.Node.Caption) else (charindex('.',ras.Node.Caption,1)-1) end)) as [Node Name]

,ras.node.DetailsUrl AS [_LinkFor_Node Name]

,'/Orion/images/StatusIcons/Small-' + ras.node.StatusIcon AS [_IconFor_Node Name]

,ras.DisplayName as [User Name]

,case when ras.DisconnectedTime is null then '/Orion/images/StatusIcons/Small-up.gif'

else '/Orion/images/StatusIcons/Small-down.gif' end as [_iconfor_User Name]

,ras.SessionGroup

, case when ras.InTotalBytes/1024/1024/1024/1024 >= 1 then concat(round(ras.InTotalBytes/1024/1024/1024/1024,0),' TB')

when ras.InTotalBytes/1024/1024/1024 >= 1 then concat(round(ras.InTotalBytes/1024/1024/1024,0),' GB')

when ras.InTotalBytes/1024/1024 >= 1 then concat(round(ras.InTotalBytes/1024/1024,0),' MB')

when ras.InTotalBytes/1024 >= 1 then concat(round(ras.InTotalBytes/1024,0),' KB')

else concat(ras.InTotalBytes,' B') end as [Rx]

, case when ras.OutTotalBytes/1024/1024/1024/1024 >= 1 then concat(round(ras.OutTotalBytes/1024/1024/1024/1024,0),' TB')

when ras.OutTotalBytes/1024/1024/1024 >= 1 then concat(SUBSTRING(tostring(round(ras.OutTotalBytes/1073741824.0,2)),1,charindex('.',tostring(round(ras.OutTotalBytes/1073741824.0,2)),1)+2),' GB')

when ras.OutTotalBytes/1024/1024 >= 1 then concat(round(ras.OutTotalBytes/1024/1024,0),' MB')

when ras.OutTotalBytes/1024 >1 then concat(round(ras.OutTotalBytes/1024,0),' KB')

else concat(ras.OutTotalBytes,' B') end as [Tx]

,case when ras.DisconnectedTime is null then tostring(ras.ConnectedTime) else tostring(ras.DisconnectedTime) end as [Conn/Disconn Time]

FROM Orion.ASA.RemoteAccessSessions ras

Thank you,

Amit

FROM Orion.ASA.RemoteAccessSessions ras