Meshing multiple tables in the same snmp_query

[mprigge]

Okay. I think I have a good handle on the fact that what I am attempting to to do is impossible with an snmp_query, but I'm not quite ready to give up. Here's the deal:

I have a storage device (I'll stay generic for the moment, but I'd be happy to share specifics) that can logically contain more than one shelf within the scope of the SNMP agent being queried. Each shelf has a random identifier that is used in its SNMP MIB. Each shelf also has a variable number of fans, temperature sensors, power supplies, and controller cards (variable because redundancy is an option). So. The SNMP table that contains information about each shelf within the logical array might look like this:

.x.2.1.1.1.5.1.1000000001 = Shelf #1's IP
.x.2.1.1.1.7.1.1000000001 = Shelf #1's Name
.x.2.1.1.1.5.1.1000000002 = Shelf #2's IP
.x.2.1.1.1.7.1.1000000002 = Shelf #2's Name
(lots of other columns in the table, but you get the drift)

1000000001 is the shelf identifier here and is much more random-looking than my example here.

Now, the temperature sensor data is kept in another table like this:

.x.2.1.6.1.2.1.1000000001.1 = Name of Temperature Sensor #1 on Shelf #1
.x.2.1.6.1.3.1.1000000001.1 = Reading of Temperature Sensor #1 on Shelf #1
...
.x.2.1.6.1.2.1.1000000002.1 = Name of Temperature Sensor #1 on Shelf #2
.x.2.1.6.1.3.1.1000000002.1 = Reading of Temperature Sensor #1 on Shelf #2

You can see the OID is hierarchically overlaying the sensor over the shelf ID so you can tell which shelf the sensor is in (as opposed to making that information a row in the table). The same is done for other shelf-specific things like power supplies and fans.

I need to be able to construct a temperature sensor query that will actually show the human-readable name of the shelves when I do a query on them. I can use a OID REGEXP to capture the shelf ID (since the shelf ID is embedded in the OID of the temp sensor) and display that, but I have no way of telling the user which Shelf ID is which (by name - users would never see the ID unless they looked for it in a the output of snmpwalk).

So, I need to do the snmp_query equivalent of a SQL table join (to virtually add the shelf name into the temperature sensor table using the shelf ID as a common key). Is anything close to this possible or am I writing a script_query? I don't mind writing the script, but I'd love to make this natively Cacti so that it's easy to redistribute. Meaning: If this is something anyone has ever run into before, I'll happily modify data_query.php to make it possible (not hard - would just mean an extension of the XML spec to accommodate it - similar to the additions of REGEXP support). I just wouldn't want to write it if it had no use to anyone else (and therefore 0% chance of being drawn into the next release).

Thanks for your help. If you've gotten this far down the post, you deserve a cookie whether you write back or not.

[polletj]

This taken from one of my table joins:
Change the OID's and names.

<interface>
<name>Get Radius Acc Counters</name>
<description>Queries a NT For Radius Acc Servers</description>
<oid_index>.........1.42.1.11.1</oid_index>

<fields>
<Index>
<name>Index</name>
<method>walk</method>
<source>value</source>
<direction>input</direction>
<oid>.........1.42.1.9.1.1</oid>
</Index>
<Server>
<name>Server</name>
<method>walk</method>
<source>value</source>
<direction>input</direction>
<oid>.........1.42.1.9.1.2</oid>
</Server>
<Requests>
<name>Requests</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.2</oid>
</Requests>
<Retransmissions>
<name>Retransmissions</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.3</oid>
</Retransmissions>
<Responses>
<name>Responses</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.4</oid>
</Responses>
<MalformedResponses>
<name>MalformedResponses</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.5</oid>
</MalformedResponses>
<BadAuthenticators>
<name>BadAuthenticators</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.6</oid>
</BadAuthenticators>
<PendingRequests>
<name>PendingRequests</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.7</oid>
</PendingRequests>
<Timeouts>
<name>Timeouts</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.8</oid>
</Timeouts>
<UnknownTypes>
<name>UnknownTypes</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.9</oid>
</UnknownTypes>
<PacketsDropped>
<name>PacketsDropped</name>
<method>walk</method>
<source>value</source>
<direction>output</direction>
<oid>.........1.42.1.11.1.10</oid>
</PacketsDropped>
</fields>
</interface>

Cheers

[mprigge]

Thanks polletj! In your case, do the number of rows in 1.42.1.11.1 and 1.42.1.9.1 match? And do they share the same index? I've gotten that sort of thing working before, too.

To relate my situation to yours, it would be as if you had one row in the 1.42.1.9.1 table which you wanted to include in the results of many rows in the 1.42.1.11.1 table. That's what I can't find a way to do except within a custom script.

[polletj]

Ok now I see the diff.

Guess that indeed a custuom script will be needed where you compose
the oid's .

In my case there is a one one mapping.

Cheers
Johan