How to update incorrect snmp interface index values

[dgeist]

In working with a add-on that provided per-sub-interface data tracking for screenOS devices, we found that when you added or removed a logical sub-interface, all data sources would remain associated to the original interface index. This is a problem because the SNMP interface indexes for the platform above the newly added interface would be offset by one and your graphs would start displaying the interface data from the RRD file from the adjacent interface. The following is an example of how to list the dynamic interface values, the data source values, and update where there are errors:


- Get the currently cached index data from the running system:

Code: Select all

mysql> select host.id as "host.id", host.description as "host.description", host_snmp_cache.field_value as "host_snmp_cache.field_value", host_snmp_cache.snmp_index as "host_snmp_cache.snmp_index" from host, host_snmp_cache where description like '%examplenetscreen.domain%' and host.id=host_snmp_cache.host_id and host_snmp_cache.field_name='nsIfName' order by host_snmp_cache.field_value;
+---------+--------------------------+-----------------------------+----------------------------+
| host.id | host.description         | host_snmp_cache.field_value | host_snmp_cache.snmp_index |
+---------+--------------------------+-----------------------------+----------------------------+
|     188 | examplenetscreen.domain  | ethernet1                   | 0                          | 
|     188 | examplenetscreen.domain  | ethernet2                   | 1                          | 
|     188 | examplenetscreen.domain  | ethernet3                   | 2                          | 
|     188 | examplenetscreen.domain  | ethernet3.30                | 3                          | 
|     188 | examplenetscreen.domain  | ethernet3.31                | 4                          | 
|     188 | examplenetscreen.domain  | ethernet3.40                | 5                          | 
|     188 | examplenetscreen.domain  | ethernet3.41                | 6                          | 
|     188 | examplenetscreen.domain  | ethernet3.52                | 7                          | 
|     188 | examplenetscreen.domain  | ethernet3.54                | 8                          | 
|     188 | examplenetscreen.domain  | ethernet3.58                | 9                          | 
|     188 | examplenetscreen.domain  | ethernet4                   | 10                         | 
|     188 | examplenetscreen.domain  | ethernet5                   | 11                         | 
|     188 | examplenetscreen.domain  | ethernet5.10                | 16                         | 
|     188 | examplenetscreen.domain  | ethernet5.12                | 17                         | 
|     188 | examplenetscreen.domain  | ethernet5.15                | 18                         | 
|     188 | examplenetscreen.domain  | ethernet5.30                | 19                         | 
|     188 | examplenetscreen.domain  | ethernet5.31                | 20                         | 
|     188 | examplenetscreen.domain  | ethernet5.4                 | 12                         | 
|     188 | examplenetscreen.domain  | ethernet5.5                 | 13                         | 
|     188 | examplenetscreen.domain  | ethernet5.7                 | 14                         | 
|     188 | examplenetscreen.domain  | ethernet5.8                 | 15                         | 
|     188 | examplenetscreen.domain  | ethernet6                   | 21                         | 
|     188 | examplenetscreen.domain  | ethernet7                   | 22                         | 
|     188 | examplenetscreen.domain  | ethernet8                   | 23                         | 
|     188 | examplenetscreen.domain  | vlan1                       | 24                         | 
+---------+--------------------------+-----------------------------+----------------------------+
25 rows in set (0.14 sec)

get the names of the data source index associations:

Code: Select all

mysql> select data_template_data.id as "data_template_data.id", data_input_data.data_template_data_id as "data_input_data.data_template_data_id", data_template_data.name_cache as "data_template_data.name_cache", data_input_data.value as "stored index" from data_template_data, data_input_data where data_template_data.id=data_input_data.data_template_data_id and data_input_data.data_input_field_id='13' and name_cache like '%examplenetscreen.domain%' order by name_cache;
+-----------------------+---------------------------------------+-------------------------------------------------------+--------------+
| data_template_data.id | data_input_data.data_template_data_id | data_template_data.name_cache                         | stored index |
+-----------------------+---------------------------------------+-------------------------------------------------------+--------------+
|                  5722 |                                  5722 | examplenetscreen.domain  - NS Traffic - ethernet1     | 0            | 
|                  5723 |                                  5723 | examplenetscreen.domain  - NS Traffic - ethernet2     | 1            | 
|                  5724 |                                  5724 | examplenetscreen.domain  - NS Traffic - ethernet3.30  | 3            | 
|                  5725 |                                  5725 | examplenetscreen.domain  - NS Traffic - ethernet3.31  | 4            | 
|                  5726 |                                  5726 | examplenetscreen.domain  - NS Traffic - ethernet3.40  | 5            | 
|                  5727 |                                  5727 | examplenetscreen.domain  - NS Traffic - ethernet3.41  | 6            | 
|                  5728 |                                  5728 | examplenetscreen.domain  - NS Traffic - ethernet3.52  | 7            | 
|                  5729 |                                  5729 | examplenetscreen.domain  - NS Traffic - ethernet3.54  | 8            | 
|                  5730 |                                  5730 | examplenetscreen.domain  - NS Traffic - ethernet3.58  | 9            | 
|                  5737 |                                  5737 | examplenetscreen.domain  - NS Traffic - ethernet5.10  | 16           | 
|                  5738 |                                  5738 | examplenetscreen.domain  - NS Traffic - ethernet5.12  | 17           | 
|                  5739 |                                  5739 | examplenetscreen.domain  - NS Traffic - ethernet5.15  | 18           | 
|                  5740 |                                  5740 | examplenetscreen.domain  - NS Traffic - ethernet5.30  | 19           | 
|                  5741 |                                  5741 | examplenetscreen.domain  - NS Traffic - ethernet5.31  | 20           | 
|                  5731 |                                  5731 | examplenetscreen.domain  - NS Traffic - ethernet5.4   | 12           | 
|                  5732 |                                  5732 | examplenetscreen.domain  - NS Traffic - ethernet5.5   | 13           | 
|                  5734 |                                  5734 | examplenetscreen.domain  - NS Traffic - ethernet5.7   | 14           | 
|                  5735 |                                  5735 | examplenetscreen.domain  - NS Traffic - ethernet5.8   | 15           | 
|                  5742 |                                  5742 | examplenetscreen.domain  - NS Traffic - ethernet6     | 21           | 
|                  5743 |                                  5743 | examplenetscreen.domain  - NS Traffic - ethernet7     | 23           | 
|                  5745 |                                  5745 | examplenetscreen.domain  - Traffic - ethernet3        | ethernet3    | 
|                  5746 |                                  5746 | examplenetscreen.domain  - Traffic - ethernet5        | ethernet5    | 
+-----------------------+---------------------------------------+-------------------------------------------------------+--------------+
22 rows in set (0.05 sec)

Hmm, notice the index doesn't match for the "NS Traffic - ethernet7" line?

Let's update that:

Code: Select all

mysql> update data_template_data, data_input_data set value='22' where data_template_data.id=data_input_data.data_template_data_id and data_input_data.data_input_field_id='13' and name_cache like '%examplenetscreen.domain%NS Traffic%ethernet7%';Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

All better. The graphs will start showing the data from the correct interfaces, now.

To generate SQL that will systematically update a whole bunch of rows, paste the data rows of the output of the first query into a text file (/tmp/foo.txt for example) and do something like this:

Code: Select all

awk '{print "update data_template_data, data_input_data set value=\""$9"\" where data_template_data.id=data_input_data.data_template_data_id and data_input_data.data_input_field_id=\"13\" and name_cache like \"%"$4"%NS Traffic%"$7" \";"}' /tmp/foo.txt

Thanks
Dan

Code: Select all