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

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.

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:

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'

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

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 

"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.

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.

No, it also delivers empty output.

When I remove the "where" part of the query I get following output, but this might not help:

Looks like in the meantime mmelton brought up a promising, at least easy to implement kind of reporting STP.
[Edited]: the simple report approach makes wrong links between interfaces and StpStates- doesn't work.

In the meantime I want to thank you for the time you've invested so far! 

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

seems like a custom poller adds the actual status more than one time into the current table?
how can I reduce this output to only the real one actual status?

thnx