3 Replies Latest reply on Apr 15, 2017 12:02 PM by Parker Robinson

    Report per a switch that includes usernames?

    pigpen85

      Looking in the Reports - there is a report for UDT called Active Endpoints.

       

      But I would like to have a report that would just be for a selected switch and then also have the UDT user to the ports. Is there a way to build a report like that?

       

      Thanks

        • Re: Report per a switch that includes usernames?
          Parker Robinson

          Pigpen,

          After looking at the options with both the Web Report builder and the legacy Report Writer on our Orion instance, I found that you are not able to get the Node(switch name) in the same report as UDT Users.  This is probably due to the fact that the data is kept in different tables.  This can be done, but would need a custom SQL query.  Please give me a few days, and will create a Custom SQL Report for you. 

           

          Regards,

          Parker Robinson

          • Re: Report per a switch that includes usernames?
            Parker Robinson

            I have created a custom SWQL query that will do just that.  Create a new "custom table" report.  Choose "Custom SQL/SWQL Query" from the datasource drop down menu, click the SWQL radio button, and paste the following:

             

            SELECT DISTINCT

            n.Caption AS [NodeName]

            , n.Vendor AS [Vendor]

            , p.Name AS [PortName]

            , u.IPAddress AS [IPAddress]

            , u.UserName AS [ADUser]

            , ToLocal(u.LogonDateTime) AS [LogonTime]

            FROM Orion.Nodes n

            JOIN Orion.UDT.IPAddressCurrent ip ON ip.RouterNodeID = n.NodeID

            JOIN Orion.UDT.UserLastActivity u ON u.IPAddress = ip.IPAddress

            JOIN Orion.UDT.Port p ON p.PortID = ip.RouterPortID

            WHERE u.LastLogonDays = 0

            ORDER BY u.LogonDateTime DESC

             

             

            I will post later how you can get this to display on your summary page with a search box where you can search for a switch and it will display the current AD users logged plugged into it.

             

            Regards,

            Parker Robinson

              • Re: Report per a switch that includes usernames?
                Parker Robinson

                Now, to get this report to display on your UDT summary page with a Node Name(switch name) search box, do the following:

                Add a "Custom Query" resource to your UDT summary page.  Paste that custom report SWQL query into the box, and select "Enable Search".  You are then going to paste the same query into the second box, but with an extra line(see screenshot below).

                 

                SELECT DISTINCT

                n.Caption AS [NodeName]

                , n.Vendor AS [Vendor]

                , p.Name AS [PortName]

                , u.IPAddress AS [IPAddress]

                , u.UserName AS [ADUser]

                , ToLocal(u.LogonDateTime) AS [LogonTime]

                FROM Orion.Nodes n

                JOIN Orion.UDT.IPAddressCurrent ip ON ip.RouterNodeID = n.NodeID

                JOIN Orion.UDT.UserLastActivity u ON u.IPAddress = ip.IPAddress

                JOIN Orion.UDT.Port p ON p.PortID = ip.RouterPortID

                WHERE u.LastLogonDays = 0 AND n.Caption LIKE '%${SEARCH_STRING}'

                ORDER BY u.LogonDateTime DESC

                 

                 

                Please note that I was not able to test these in our environment, but should work just fine.

                 

                Regards,

                Parker Robinson