16 Replies Latest reply on May 22, 2015 7:30 AM by rgeist

    UDT Port details?

    aso1989

      Hi there,

       

      Does anyone know of a way that I can export port details of a switch into excel? There's isnt on UI so wondering if anyone know a way trough SQL or database manager?

       

      PS: I have already raised it with Support and they will request it to their developers to add an export button on the switch port deatils.

       

      Thanks in advance!

        • Re: UDT Port details?
          Craig Norborg

          Can you be more specific as to what details?   Odds are you can easily get it through an SQL or SWQL query.

            • Re: UDT Port details?
              aso1989

              Thanks for your reply Craig,

               

              Specifically, I am looking for hostname , MAC , IP and Vendor. same as what we see on UDT switch. See attahced screenshot.

               

              I can get details from SQL query (UDT_Port) and (UDT_DNSNameCurrent) but I cannot match both the table since there's no common table. There might be a way to do it, I am not an SQL guru

              Port Details.PNG

                • Re: UDT Port details?
                  Craig Norborg

                  Ok, not seeing the total correlation between what you're saying and what you did a screenshot of.   Your asking for hostname, MAC, IP and Vendor.   But the screenshot shows Port #, Hostname, IP, MAC, VLAN and VRF.   Not many common fields there.

                   

                  But, I'll give you a couple examples in SWQL, which you can find in the OrionSDK, that might make you a bit more dangerous yourself.    This query is to show how SWQL can JOIN some tables together for you using what I believe they call their "Navigation Properties".

                   

                  SELECT TOP 100 NodeID, Name, MACAddress, UP.IPAddresses.IPAddress, UP.Node.Caption

                  FROM Orion.UDT.Port UP

                  WHERE NOT (UP.IPAddresses.IPAddress = '')

                   

                  This is a little closer to what your asking for, leveraging both the Navigation Properties and a couple actual JOINS.

                   

                  SELECT TOP 100 UP.NodeID, UP.Name, UP.MACAddress, UP.IPAddresses.IPAddress,  DNC.DNSName, MCI.MACVendor

                  FROM Orion.UDT.Port UP JOIN Orion.UDT.DNSNameCurrent DNC ON (UP.IPAddresses.IPAddress = DNC.IPAddress) JOIN Orion.UDT.MACCurrentInformation MCI ON (UP.NodeID = MCI.NodeID)

                  WHERE NOT (UP.IPAddresses.IPAddress = '')

                   

                  However, sometimes its easier to start somewhere else, this shows how I eliminated using the UDT.Port altogether by starting with the UDT.MACCurrentInformation table instead, eliminating one JOIN and still having the same info as the query above

                   

                  SELECT TOP 100 MCI.NodeID, MCI.PortName, MCI.MACAddress, MCI.IPAddress,  DNC.DNSName, MCI.MACVendor

                  FROM Orion.UDT.MACCurrentInformation MCI JOIN Orion.UDT.DNSNameCurrent DNC ON (DNC.IPAddress = MCI.IPAddress)

                  WHERE NOT (MCI.IPAddress = '')

                   

                  I know none of these are actually what you want, just hoping to get you closer.

                   

                  Oh, you can drop any of these on to any page by customizing your page, adding a "Custom Query" resource and putting this SWQL in there.  Kind of neat compared to regular SQL queries..

              • Re: UDT Port details?
                michalB

                You can use Report Writer (Windows application in the SolarWinds Orion start menu) to create a new Advanced SQL Report. To see the database structure, use Database Manager tool (available via start menu) and look for tables starting with "UDT_". UDT_Port is the one you should look at first. Database Manager also provides export of SQL query results to CSV.

                  • Re: UDT Port details?
                    aso1989

                    Thanks for your reply MichalB,

                     

                    I tried UDT_Port but doesn't give me all the info I need. I did use Report Write when we first installed Solawinds, I will try again to see if it is of any help.

                      • Re: UDT Port details?
                        michalB

                        I have constructed this SQL query:

                         

                        SELECT Nodes.Caption, UDT_Port.Name, UDT_Endpoint.MACAddress,UDT_EndpointIP.IPAddress,UDT_DNSNameCurrent.DNSName, UDT_PortToEndpointCurrent.VLANID,UDT_Vrf.VrfName, UDT_PortToEndpointCurrent.ConnectionType FROM UDT_Port

                        join UDT_PortToEndpointCurrent on UDT_PortToEndpointCurrent.PortID=UDT_Port.PortID

                        join Nodes on Nodes.NodeID=UDT_Port.NodeID

                        join UDT_Endpoint on UDT_Endpoint.EndpointID=UDT_PortToEndpointCurrent.EndpointID

                        left join UDT_IPAddressCurrent on UDT_IPAddressCurrent.EndpointID=UDT_PortToEndpointCurrent.EndpointID

                        left join UDT_EndpointIP on UDT_EndpointIP.IPAddressID=UDT_IPAddressCurrent.IPAddressID

                        left join UDT_DNSNameCurrent on UDT_DNSNameCurrent.IPAddressID=UDT_IPAddressCurrent.IPAddressID

                        left join UDT_Vrf on UDT_Vrf.VrfId = UDT_IPAddressCurrent.VrfID

                        where Nodes.NodeID=288 and UDT_PortToEndpointCurrent.ConnectionType<>2

                         

                        In my limited environment, it shows the same information as the resource on the website.

                          • Re: UDT Port details?
                            rgeist

                            What is UDT_PortToEndpointCurrent.ConnectionType? Is that where trunk/access are coming from? I've been trying to create a report of port type and vlans per a set of nodes for a while. I have one that works, but not for every node.

                            • Re: UDT Port details?
                              rgeist

                              Another question. Have you been able to find another source for the port type other than PortType in NodePortInterfaceMap? I really need the trunk/access port data that is showing up in the port details. I can find it no problem when it shows up within the interface details like it does on some nodes. However, when it comes to the nodes that only shows the vlan and port modes in port details, I'm having trouble finding it in any table.  So right now my query shows unknown for the port mode for any node that only has the VLAN information in the port details. It's got to be stored somewhere...

                               

                              Thanks!

                               

                              shows port type.JPG

                                • Re: UDT Port details?
                                  michalB

                                  There is TrunkMode column in UDT_Port table.

                                   

                                  Values:

                                  Unknown = 0,

                                  Trunking = 1,

                                  NonTrunking = 2

                                   

                                  Ports with two or more VLANs are recognized as trunk ports.

                                    • Re: UDT Port details?
                                      RichardLetts

                                      michalB wrote:

                                       

                                      There is TrunkMode column in UDT_Port table.

                                       

                                      Values:

                                      Unknown = 0,

                                      Trunking = 1,

                                      NonTrunking = 2

                                       

                                      Ports with two or more VLANs are recognized as trunk ports.

                                      Really? That is not a good decision.

                                      Edge mode and trunk mode have very different meanings in spanning tree on some switches (e.g. edge-mode ports start in forwarding, whereas trunk-mode ports start in blocking), and we might only have one VLAN provisioned on a [true] trunk

                                       

                                      on a cisco: 1.3.6.1.4.1.9.9.46.1.6.1.1.14 Cisco SNMP Object Navigator

                                      on a Juniper: 1.3.6.1.4.1.2636.3.40.1.5.1.7.1.5 jnxExVlanPortAccessMode -- http://www.juniper.net/techpubs/en_US/junos13.3/topics/reference/mibs/mib-jnx-vlan.txt

                                      • Re: UDT Port details?
                                        rgeist

                                        Ok. But I noticed that what is showing up in TrunkMode for this one node is not correct, it is showing as unknown rather than trunk when it says trunk on the web for it. My vlans show up correctly when I query it. What is displaying in udt_port.TrunkMode is consistent with what is being displayed in NodePortInterfaceMap.PortType. So I'm still wondering how they are storing trunk...

                                         

                                        I do know that the node I'm checking does not support its dot1qVlanCurrentTable Status (it is a D-Link 1210-10P) and all of its trunk information is showing up as unknown (0) for what I'm finding. BUT...that screenshot I took earlier is showing that on this 1210, it is a trunk port, which does happen to have 3 vlans on it. I'm still confused how/why Solarwinds is showing me it is a trunk port when I'm finding that it marks them as unknown in the database. I'm sure it is in there somewhere...