12 Replies Latest reply on Apr 26, 2018 10:46 AM by anthony.cox

    Create Custom Table

    anthony.cox

      Hello All,

       

      I am attempting to create a custom table to place on one of my views in Orion.  I am wanting to create a table that displays all trunk ports and etherchannel ports within our environment.  I have already created a group that does a dynamic query to gather all of those interfaces although I am not certain that is how I should continue to proceed.  The table needs to display the following columns (from left to right) Node, Receive % Utilization, Transmit % Utilization, Receive Discards Today, and Transmit Discards Today. 

       

      It might also be nice to know how to best break these out by geographic locations if we wanted to. 

       

      Please let me know if I need to clarify further or if you have any questions.

       

      Thanks!

        • Re: Create Custom Table
          m-milligan

          This SWQL query should get you started. YOUR_CONTAINER_ID is the ID of the container that you've created to hold the interfaces.

           

          Interface.Node.Caption as [Node],

          Interface.InPercentUtil as [Receive % Utilization],

          Interface.OutPercentUtil as [Transmit % Utilization],

          Interface.InDiscardsToday as [Receive Discards Today],

          Interface.OutDiscardsToday as [Transmit Discards Today]

          FROM Orion.NPM.Interfaces Interface

          WHERE Interface.InterfaceID IN

          (

              SELECT CM.MemberPrimaryID

              from Orion.ContainerMembers CM

              WHERE MemberEntityType='Orion.NPM.Interfaces' AND

              CM.ContainerID = YOUR_CONTAINER_ID

          )

          ORDER BY Interface.Node.Caption, Interface.Name

            • Re: Create Custom Table
              anthony.cox

              Thanks for the input.  I am quite the novice in SWQL.  What is the easiest way to find the CM.ContainerID?  I thought it was simply the name of the group that I created, but I am getting an error stating the *Query is not valid.

               

              Name of the group that I created is simply "trunks" for now.  So my query that fails is as follows:

               

              Interface.Node.Caption as [Node],

              Interface.InPercentUtil as [Receive % Utilization],

              Interface.OutPercentUtil as [Transmit % Utilization],

              Interface.InDiscardsToday as [Receive Discards Today],

              Interface.OutDiscardsToday as [Transmit Discards Today]

              FROM Orion.NPM.Interfaces Interface

              WHERE Interface.InterfaceID IN

              (

                  SELECT CM.MemberPrimaryID

                  from Orion.ContainerMembers CM

                  WHERE MemberEntityType='Orion.NPM.Interfaces' AND

                  CM.ContainerID ='Trunks'

              )

              ORDER BY Interface.Node.Caption, Interface.Name

                • Re: Create Custom Table
                  m-milligan

                  To find the ContainerID, to to the detail page for the Group, and look at the URL for the page. The URL will end with "NetObject=C:SomeNumber". SomeNumber is the ContainerID. Alternatively, you can modify the query to use the Group name:

                   

                  SELECT

                  Interface.Node.Caption as [Node],

                  Interface.InPercentUtil as [Receive % Utilization],

                  Interface.OutPercentUtil as [Transmit % Utilization],

                  Interface.InDiscardsToday as [Receive Discards Today],

                  Interface.OutDiscardsToday as [Transmit Discards Today]

                  FROM Orion.NPM.Interfaces Interface

                  WHERE Interface.InterfaceID IN

                  (

                      SELECT CM.MemberPrimaryID

                      from Orion.ContainerMembers CM

                      WHERE MemberEntityType='Orion.NPM.Interfaces' AND

                      CM.Container.Name ='Trunks'

                  )

                  ORDER BY Interface.Node.Caption, Interface.Name

                  1 of 1 people found this helpful
                    • Re: Create Custom Table
                      anthony.cox

                      I made one additional modification to the query to include the interface name.  The results with your method turned out great as well.  Thanks for your help!

                       

                      SELECT

                      Interface.Node.Caption as [Node],

                      Interface.Name as [Interface Name],

                      Interface.InPercentUtil as [Receive % Utilization],

                      Interface.OutPercentUtil as [Transmit % Utilization],

                      Interface.InDiscardsToday as [Receive Discards Today],

                      Interface.OutDiscardsToday as [Transmit Discards Today]

                      FROM Orion.NPM.Interfaces Interface

                      WHERE Interface.InterfaceID IN

                      (

                          SELECT CM.MemberPrimaryID

                          from Orion.ContainerMembers CM

                          WHERE MemberEntityType='Orion.NPM.Interfaces' AND

                          CM.Container.Name ='Trunks'

                      )

                      ORDER BY Interface.Node.Caption, Interface.Name

                       

                       

                      Anyone reading at a later time, you will need to make sure to create your group first that holds the devices you are looking for.

                        • Re: Create Custom Table
                          m-milligan

                          Glad it's working!

                            • Re: Create Custom Table
                              anthony.cox

                              I am new to the Orion world, but it would be very helpful to have a good reference for all of the syntax that is usable in it.  What lines would I need to allow me to make the Node Names and the Interfaces clickable and take them to the details page of the link?  Let me know if I need to clarify.

                               

                              Here is what I am seeing.

                               

                                • Re: Create Custom Table
                                  m-milligan

                                  You'll have to select the columns with the details URL for the nodes and interfaces, and name them the way Solarwinds expects them. This query will do that:

                                   

                                  SELECT
                                  Interface.Node.Caption as [Node],
                                  Interface.Node.detailsURL as [Node|detailsURL],
                                  Interface.Name as [Interface Name],
                                  Interface.detailsURL as [Interface Name|detailsURL],
                                  Interface.InPercentUtil as [Receive % Utilization],
                                  Interface.OutPercentUtil as [Transmit % Utilization],
                                  Interface.InDiscardsToday as [Receive Discards Today],
                                  Interface.OutDiscardsToday as [Transmit Discards Today]
                                  FROM Orion.NPM.Interfaces Interface
                                  WHERE Interface.InterfaceID IN
                                  (
                                      SELECT CM.MemberPrimaryID
                                      from Orion.ContainerMembers CM
                                      WHERE MemberEntityType='Orion.NPM.Interfaces' AND
                                      CM.Container.Name ='SubgroupTEST'
                                  )
                                  ORDER BY Interface.Node.Caption, Interface.Name

                                   

                                  Here's how this works. This selects the name of the node and puts it into a column called "Node":

                                  Interface.Node.Caption as [Node]

                                   

                                  This selects the URL of the node's details page and puts it into a column called "Node|detailsUrl":

                                  Interface.Node.detailsURL as [Node|detailsURL]

                                   

                                  When you tell the Report Writer to display a column with a detail page link, it looks for a second column with the name "detailsURL" or "(column name)|detailsURL". It will use that second column to create the clickable link in the final report. If you want to have more than one column with a link, each column must have a corresponding "(column name)|detailsURL" column.

                      • Re: Create Custom Table
                        anthony.cox

                        I ended up with the following through the UI that I seems to work for what we are looking for.  I am still going to attempt to use your m-milligan suggestion from above to try and create a table using the SWQL.  Will get back to you once I have tested it.

                         

                        The settings through GUI are filtering it down to specific interfaces in a specific datacenter.






                        1 of 1 people found this helpful