21 Replies Latest reply on Mar 5, 2015 9:12 AM by d09h

    Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)

    herwig

      Hi,

      Anyone with experience in SQL queries and combining custom MIB pollers to create a combined GUI view or report?
      A solution for following issue would bring benefits to lots of people I think... 


      We want to add a Node detail listing the Spanning Tree Port Status of all important Interfaces of a Switch.
      In NPM this only can be achieved with Custom MIB Pollers, since there is no "out of the box" solution.

      Unfortunately SNMP Queries for StpPortState list based on an unique Index number and so the output is not human readable:

      Again, the left column is not directly related to the IfMIB IfIndex value...

       

      So what we would have to do here is:
      1. Query the dot1dStpPortState Table – it delivers the STP State per port but with an at this point unknown ID as key value!
      2. Query the dot1dBasePortIfIndex Table which then links between IfIndex and StpPortIndex
      3. Query the IfMIBs translation Table for IfIndex to IfDescription
      4. Combine IfDescription with corresponding StpPortState and list in output

      As far as I understand NPM, this combination of pollers can only be done via custom SQL queries and combine values via customized reports which are then added as a resource onto the Node detail View, right?

      Is there a tough guy accepting this challenge?
      I already opened a support ticket - unfortunately the outcome was "We are not doing DBA support and do not have the knowledge to create such queries"... 

       

      Thanks for any help on this!
      Cheers
      Herwig 

       

      p.s.: Monitoring Spanning Tree is essential for enterprise LAN networks,
      unfortunately this is not implemented in Solarwinds products as out of the box solution.
      There are several threads here and most without propper solution.

        • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
          netlogix

          It could be something like:

          select Table1.value as interface, table4.value as dot1dstpportforwrding
          from table as table1
            inner join table as table2 on table1.column = table2.value
            inner join table as table3 on table2.column = table3.value
            inner join table as table4 on table3.column = table4.value

          But with out the actual data, its hard to set it up and get it right.

            • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
              herwig

              yes, that seems to go in the direction...
              here is some additional information I gathered from my DB.
              At the moment there is no third poller gathering the IfIndex - IfDescription Table,
              but maybe this is enough data for writing queries? 

              The poller tables:

               

              The 2 existing pollers:

               

              The assignement table mapping pollerID with Assignment ID

              Finally the custom poller status table referenced with assignment ID:

                • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                  netlogix

                  I can't check my work but this *might* do it (or at least be close).

                  SELECT Nodes.Caption, PortIfIndex.Status as PortIfIndex, StpPortState.Status as StpPortState
                    FROM [CustomPollers]
                      inner join [CustomPollerAssignment] on [CustomPollerAssignment].[CustomPollerID] = [CustomPollers].[CustomPollerID]
                      inner join [Nodes] on [nodes].NodeID = CustomPollerAssignment.NodeID
                      inner join [CustomPollerAssignment] as StpPortStateAssignment on ([StpPortStateAssignment].[NodeID] = [Nodes].[NodeID] and [CustomPollerAssignment].[CustomPollerID] = (Select CustomPollerID from CustomPollers where [UniqueName] = 'dot1dStpPortState'))
                      inner join [CustomPollerStatus] as PortIfIndex on [PortIfIndex].CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID
                      inner join [CustomPollerStatus] as StpPortState on (PortIfIndex.RowID = StpPortState.RowID and StpPortStateAssignment.CustomPollerAssignmentID = StpPortState.CustomPollerAssignmentID)
                    Where [CustomPollers].[UniqueName] = 'dot1dBasePortIfIndex'

                    • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                      herwig

                      ouch, this one is a brain blower for me
                      it seems there is something wrong with the StpIfIndex to IfIndex mapping part, but I could not figure out in detail.

                      I changed your query to show up all the joins and hope you can read any useful information from the output.
                      A CSV output of this query is now attached to this posting (in txt, please rename to .csv)

                      Select *

                      From CustomPollers
                          inner join [CustomPollerAssignment] on [CustomPollerAssignment].[CustomPollerID] = [CustomPollers].[CustomPollerID]
                          inner join [Nodes] on [nodes].NodeID = CustomPollerAssignment.NodeID
                          inner join [CustomPollerAssignment] as StpPortStateAssignment on ([StpPortStateAssignment].[NodeID] = [Nodes].[NodeID] and [CustomPollerAssignment].[CustomPollerID] = (Select CustomPollerID from CustomPollers where [UniqueName] = 'dot1dStpPortState'))
                          inner join [CustomPollerStatus] as PortIfIndex on [PortIfIndex].CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID
                          inner join [CustomPollerStatus] as StpPortState on (PortIfIndex.RowID = StpPortState.RowID and StpPortStateAssignment.CustomPollerAssignmentID = StpPortState.CustomPollerAssignmentID)
                        Where [CustomPollers].[UniqueName] = 'dot1dStpPortState

                      br
                      Herwig

                       

                      • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                        herwig

                        At least I found out whats wrong.
                        When we focus on a node, we see 6 entries for 3 active/monitored interfaces.
                        The first 3 lines represent StpIfIndex (e.g. 513) to IfIndex (e.g. 35)
                        The lowest 3 lines represent the StpIfIndex to StpState entries.

                        Looks like a final join to a combined IfIndex <-> StpState  is missing?

                         

                        Thanks for re-checking
                        Herwig 

                          • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                            netlogix

                            "dot1dBasePortIfIndex" is right, but I guess the tie isn't working, but I don't see why it isn't.

                            try this one:


                            SELECT Nodes.Caption, PortIfIndex.Status as PortIfIndex, StpPortState.Status as StpPortState
                              FROM [CustomPollers]
                                inner join [CustomPollerAssignment]
                                  on [CustomPollerAssignment].[CustomPollerID] = [CustomPollers].[CustomPollerID]
                                inner join [Nodes] on [nodes].NodeID = CustomPollerAssignment.NodeID
                                inner join [CustomPollerAssignment] as StpPortStateAssignment
                                  on ([StpPortStateAssignment].[NodeID] = [Nodes].[NodeID]
                                      and [CustomPollerAssignment].[CustomPollerID] = (Select CustomPollerID
                                                                                        from CustomPollers
                                                                                        where [UniqueName] = 'dot1dStpPortState')
                                     )
                                inner join [CustomPollerStatus] as PortIfIndex
                                  on [PortIfIndex].CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID
                                inner join [CustomPollerStatus] as StpPortState
                                  on StpPortStateAssignment.CustomPollerAssignmentID = StpPortState.CustomPollerAssignmentID)
                              Where [CustomPollers].[UniqueName] = 'dot1dBasePortIfIndex' and PortIfIndex.RowID = StpPortState.RowID

                            it's probably something simple, but I am not seeing it.

                    • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                      Michael Melton

                      Have you just tried a simple report like this?

                      The report yields output like this:

                       After that it is just embedding the report into a view. I believe this is what you are after, well as close as I can get it at least. The problem with this report is that it will show the status of the interfaces on every device.

                       

                      Hope this helps some, and good luck.

                        • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                          herwig

                          mmelton,

                          thanks for your valueable input! That's quire close to our requirements...
                          One issue I have with it is that although I choosed a current report template its results show the STP state X times per interface instead of only one entry.

                          Another drawback as you already noted- status of every switch is shown and SQL filter on web view templates per device doesn't seem to work...

                          br

                          • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                            herwig

                            mmelton,

                            it was just too good to be true...

                            unfortunately your report does a wrong join between interfaces and STP forwarding states.
                            This happens, because the StpPortState table uses a unique port identifier which is not the IfIndex of nodes interfaces. (like you can see in my initial post starting this thread).

                            So I have to go on with netlogix approach which tries to create the correct link between the different table key values:

                            dot1dStpPortState | dot1dBasePortIfIndex <-->  dot1dBasePortIfIndex  |  IfMIB:IfIndex <--> IfMIB_Ifindex | IfDescription

                              • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                                Karlo.Zatylny

                                Hi,

                                We do keep track of the PortID in Orion now.  We have the NodePortInterfaceMap that is used for our Topology calculation and is gathered by the Layer 2 Topology poller.  You can join to this data:

                                SELECT p.[NodeID]
                                      ,p.[PortID]
                                      ,p.[IfIndex]
                                      ,i.InterfaceID
                                      ,i.Caption
                                  FROM [dbo].[NodePortInterfaceMap] p
                                  JOIN [dbo].[Interfaces] i ON i.NodeID = p.NodeID AND i.InterfaceIndex = p.IfIndex

                                Maybe this data can get you to poll one less level of UnDPs and just get you STP Port State by joining to the query above.

                                Thanks

                                  • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                                    herwig

                                    Hi Karlo,

                                    yes, this is very helpful indeed
                                    with your and netlogix help I figured out something like that:

                                    SELECT
                                         n.[Caption] Node
                                       , n.[NodeID] NodeID
                                       , i.[Caption] InterfaceID
                                       , p.[VlanId] Vlan
                                       , s.[Status] Status
                                       FROM [CustomPollerStatus] s
                                         JOIN [CustomPollerAssignment] a ON a.[CustomPollerAssignmentID] = s.[CustomPollerAssignmentID]
                                              AND (SELECT [CustomPollerID] FROM dbo.[CustomPollers] WHERE OID = '1.3.6.1.2.1.17.2.15.1.3') = a.[PollerID]
                                         JOIN [Nodes] n on n.[NodeID] = a.[NodeID]
                                         JOIN [NodePortInterfaceMap] p ON p.[PortID] = s.[RowID] AND p.[NodeID] = a.[NodeID] AND p.[VlanId] IS NOT NULL
                                         JOIN [Interfaces] i ON i.NodeID = p.[NodeID] AND i.[InterfaceIndex] = p.[IfIndex]

                                    In all of my SQL JOIN trial and error script kiddie approaches I've noticed bogus multiple entries.
                                    Now I still have these multiple entries per one STP state line in table (in this example it comes from Vlan ID in NodePortInterfaceMap- and the simple BRIDGE MIB doesn't provide per Vlan STP stats - so the output is not really true atm).

                                    This leads me to another topic in STP monitoring: Cisco does per Vlan STP - looks like I have to find another MIB to achieve this.
                                    In most setups all Vlans on a dot1q Trunk should share the same STP status - e.g. a report showing differences here would also be nice.

                                    Finally alerting for this kind of STP reporting is also an open task for me.
                                    This atm is only possible via SNMP Traps and/or Syslog alarming - but this reports only a changes and not the actual states (blocking, forwarding, ...).
                                    So some kind of polling alert including current STP states would be more valuable.

                                    I very appreciate your help here - everyone who participates in this discussion
                                    Thanks! 

                              • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                                Karlo.Zatylny

                                Hi Herwig,

                                Does a DISTINCT on your statement help your results?  i.e. SELECT DISTINCT ...

                                I don't believe that UnDP supports polling via VLAN (public@1) type polling, but with the new table NodeVlans, that is more of a possible feature now.

                                Melton, better reporting is on the roadmap, and this is a great request.  I'll make sure the PM is aware.

                                Thanks

                                • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                                  jordandalley

                                  Curious.. did anyone have a resolution for this in the end? Looking for a simple report that lists interfaces on switches with spanning tree status as blocking. On a HP Procurve, blocking is a numeric value of 2.

                                  • Re: Custom Poller - combine snmp results from different MIBs (Spanning Tree Monitoring)
                                    RichardLetts


                                    The proper way to do this is to use subqueries like this:

                                    'A' and 'B' find the data for the two pollers (in this case it's the Juniper Chassis VCP port states

                                    Note, the A.custompollerassignmentid and A.Rowid could be used to lookup labels, and A.Nodeid could be used to join to the nodes table.


                                    ----


                                    SELECT A.nodeid,

                                           A.rowid,

                                           A.status AS AdminStatus,

                                           B.status AS OperStatus

                                    FROM   (SELECT cpa.nodeid,

                                                   CPA.custompollerassignmentid,

                                                   CPS.[status],

                                                   CPS.[rowid]

                                            FROM   [SolarWindsOrion].[dbo].[custompollerstatus] CPS

                                                   INNER JOIN custompollerassignment CPA

                                                           ON

                                                   CPS.custompollerassignmentid = CPA.custompollerassignmentid

                                                   INNER JOIN custompollers CP

                                                           ON CP.custompollerid = CPA.custompollerid

                                                              AND CP.uniquename = 'jnxVirtualChassisPortAdminStatus'

                                           ) A

                                    INNER JOIN (SELECT cpa.nodeid,

                                                              CPS.[status],

                                                              [rowid]

                                                       FROM   [SolarWindsOrion].[dbo].[custompollerstatus] CPS

                                                              INNER JOIN custompollerassignment CPA

                                                                      ON

                                           CPS.custompollerassignmentid = CPA.custompollerassignmentid

                                           INNER JOIN custompollers CP

                                                   ON CP.custompollerid = CPA.custompollerid

                                                      AND CP.uniquename =

                                                          'jnxVirtualChassisPortOperStatus') B

                                       ON A.nodeid = B.nodeid

                                      AND A.rowid = B.rowid