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

ASA Details (swql)

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.

pastedImage_0.png

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

pastedImage_6.png

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

Labels (1)
Tags (2)
Version history
Revision #:
1 of 1
Last update:
‎11-01-2019 08:32 AM
Updated by: