Version 3

    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