InfluxDB/Grafana/Bosun + Cacti = CereusTransport - Updated!

[theslim1]

I also saw that many datapoints were not being pushed into db, wasn't satisfied with the speed of script and with a lack of Bosun support. So I've started to change the script. Well, it became a rewrite.

Here are my changes:

0.45 (changes by simonov@gmail.com)
* Added Bosun export support (http://bosun.org/). Also OpenTSDB. May have broken InfluxDB support, haven't tested it, but should be working.

* Changed settings back to DB URI instead of db, port and so on in separate settings. Made configuration simpler.

* Fixed population of data array by key - version 0.4 was loosing too much data (for example, if there was more than one traffic_out, only one was saved)

* Converted plugin_CereusTransporter_data to a Memory table. Nice improvement in speed. Make sure to make mysql memory table parameters bigger
(tmp_table_size and max_heap_table_size). Table will be recreated automatically by the upgrade script (hopefully).
Data is considered temporary and gets deleted on every sending pass.

* Added much more additional data to metrics: host and hostname, data source type (gauge, counter), index type and value if used, units and so on.
Unit name is taken from Vertical Label of Graph Template for datasource. If there are multiple Graph Templates, only one value will be taken.
Vertical Label text should be from this list, or bosun will skip it: https://godoc.org/bosun.org/metadata#Unit
Metrics info for Bosun is sent to different api outlet.

* Pre-populated all needed info from DB. No SQL queries are made for each metric. HUGE improvement in speed. Now it takes about 90 seconds on average
to send 168k data points into Bosun.

* Additional stats in poller logs. Example:
CereusTransporter: Poller[0] STATS: Time:98.82 LookupTableSize:32685 LookupUnitsTableSize:22363 MetricsCount:168862 AverageHostTime:0.12
SlowestHostnames:[host1: 3.544s, host2: 2.784s, host3: 2.216s, host4: 2.159s]

Changes are not much tested, but they are working in my environment. No guarantees.

InfluxDB support does appear to be broken with your release,

Code: Select all

1/19/2016 02:16:02 PM - CereusTransporter: Poller[0] ERROR: https is not a valid scheme
01/19/2016 02:16:02 PM - CereusTransporter: Poller[0] DEBUG: Adding the following data to influxdb: [1453241762] []
01/19/2016 02:16:02 PM - CereusTransporter: Poller[0] DEBUG: influxdb Units lookup table size: [61]
01/19/2016 02:16:02 PM - CereusTransporter: Poller[0] DEBUG: influxdb Lookup table size: [0]
01/19/2016 02:16:02 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:" SELECT ds.id ,host.hostname ,host.description ,data.name_cache ,(CASE WHEN rrd.data_source_type_id=1 THEN 'gauge' WHEN rrd.data_source_type_id=2 THEN 'counter' WHEN rrd.data_source_type_id=3 THEN 'counter' WHEN rrd.data_source_type_id=4 THEN 'counter' END) AS rate ,data_template.name AS metric ,host_template.name AS host_type ,(CASE WHEN EXISTS (SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME='host' AND COLUMN_NAME='polling_time') THEN host.polling_time END) polling_time FROM data_template_data data INNER JOIN data_local ds ON ds.id=data.local_data_id INNER JOIN host ON host.id=ds.host_id INNER JOIN host_template ON host_template.id=(CASE host.host_template_id WHEN 0 THEN (SELECT id FROM host_template ORDER BY id LIMIT 1) ELSE host.host_template_id END) INNER JOIN data_template ON data_template.id=data.data_template_id INNER JOIN data_template_rrd rrd ON rrd.local_data_id=data.local_data_id WHERE data.local_data_template_data_id <> 0 AND host.disabled <> 'on' GROUP BY ds.id"
01/19/2016 02:16:02 PM - CereusTransporter: Poller[0] INFO: Adding data to influxdb 

[shd]

For the data, I think there may be cases where the tables gets deleted (poller_top) while it's still working on the data ( overrunning poller processes ) , so maybe changing the deletion of old data should be done after the processing of it ( e.g delete from `plugin_CereusTransporter_data` where timestamp < oldest_processed_data_item_timestamp ).

My version takes all data to export and then immediately cleans it out from plugin table:

Code: Select all

        $polling_data = db_fetch_assoc( "SELECT `timestamp`, `local_data_id`, `key`, `value` FROM plugin_CereusTransporter_data ORDER BY `timestamp`,`local_data_id`,`key`" );
        // Clean
        db_execute( "DELETE FROM `plugin_CereusTransporter_data`" ); 

So all datapoints are in script memory. Even if one script overruns another, data is still pushed into upstream db. If the pushing script dies, then only its datapoints are lost. And we don't have a case of growing plugin table.

[shd]

InfluxDB support does appear to be broken with your release,

Code: Select all

01/19/2016 02:16:02 PM - CereusTransporter: Poller[0] DEBUG: influxdb Lookup table size: [0]
01/19/2016 02:16:02 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:" SELECT ds.id ,host.hostname ,host.description ,data.name_cache ,(CASE WHEN rrd.data_source_type_id=1 THEN 'gauge' WHEN rrd.data_source_type_id=2 THEN 'counter' WHEN rrd.data_source_type_id=3 THEN 'counter' WHEN rrd.data_source_type_id=4 THEN 'counter' END) AS rate ,data_template.name AS metric ,host_template.name AS host_type ,(CASE WHEN EXISTS (SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME='host' AND COLUMN_NAME='polling_time') THEN host.polling_time END) polling_time FROM data_template_data data INNER JOIN data_local ds ON ds.id=data.local_data_id INNER JOIN host ON host.id=ds.host_id INNER JOIN host_template ON host_template.id=(CASE host.host_template_id WHEN 0 THEN (SELECT id FROM host_template ORDER BY id LIMIT 1) ELSE host.host_template_id END) INNER JOIN data_template ON data_template.id=data.data_template_id INNER JOIN data_template_rrd rrd ON rrd.local_data_id=data.local_data_id WHERE data.local_data_template_data_id <> 0 AND host.disabled <> 'on' GROUP BY ds.id"

It's not influx, it's data cache prepolling. Try executing this SELECT manually, what error do you receive?

[phalek]

Added the source to bitbucket now: https://bitbucket.org/thurban/cereustransport

Fixed the InfluxDB connection ( DSN description wrong, starting with influxdb:// instead of https:// )

Fixed SQL statement ( CASE not working, failing on host.polling_time. Added a new setting to choose "modified spine" or not to the MISC settings page )

Update: Added the CereusTransport_CleanTag function to metric_text and units. Spaces contained in there break the InfluxDB upload. This may break the bosun support though.

Uploaded 0.47 to http://blog.network-outsourcing.de/prod ... ansporter/

[shd]

Update: Added the CereusTransport_CleanTag function to metric_text and units. Spaces contained in there break the InfluxDB upload. This may break the bosun support though.
Uploaded 0.47 to http://blog.network-outsourcing.de/prod ... ansporter/

It will not break bosun, but will make that text less readable.
Move CleanTag to line 95, in point preparation block for inluxdb, that way you will process tags for influx only.

[phalek]

Ok,

Updated to 0.48.

Code: Select all

* Added CereusTransporter_cleanTag function for metric_text and units for InfluxDB writes.
* Removed general integer cast for $point[value] and added to bosun updates only

Plugin:
http://blog.network-outsourcing.de/prod ... ansporter/
Source:
https://bitbucket.org/thurban/cereustransport

[phalek]

Btw: theslim1, the measurements changed a bit now. previously, the load average was load_1min, now it's called "ucd.net.load.average.1.minute"

Traffic is under the interface.traffic measurement, type is either traffic_in or traffic_out.


A side-note:
I'm not really a Grafana Expert mabe even less then a beginner on it, so I've got no clue how to build a nice dashboard with it. If someone has it up for Cacti, sharing it would be really great

shd. Bosun is for alerting/notification, right ? Is there some sort of screenshot you could share to show what you can do with the cacti data ?

[theslim1]

The latest version of this plugin is now working great to dump data into InfluxDB, and pull out with Grafana. I will be writing up a full guide on generating templates and dashboards with the basis of a existing Cacti install to work with very soon. Here's what my dashboard looks like now;

http://i.imgur.com/g1RPb7t.png

This is a very exciting project because there are no solid SNMP pollers created yet besides some small hobby scripts on Github that allow you to easily query SNMP enabled hosts and push that data into InfluxDB POST requests.

[phalek]

I'd love to get hold of that guide. As said, I'm pretty new on InfluxDB ( or any of these "BigData" things ).

Anyone would like a Grafana integration ( user create, assign dashboards ... stuff like this ) for Cacti ? Maybe in a separate plugin. I can see they have a HTTP API for doing this.

[brandstaetter]

Hi,
I would really like to use CereusTransporter for pushing my cacti results to influxDB!
But its not working for me with cacti version 0.8.8f.

in cacti.log I get:

01/26/2016 08:46:28 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1140', SQL:" SELECT ds.id ,MAX(CASE WHEN type_code='index_type' THEN input.value END) index_type ,MAX(CASE WHEN type_code='index_value' THEN input.value END) index_value FROM data_template_data data INNER JOIN data_local ds ON ds.id=data.local_data_id INNER JOIN data_input_fields field ON field.data_input_id=data.data_input_id AND field.input_output='in' AND field.type_code IN ('index_type', 'index_value') INNER JOIN data_input_data input ON input.data_template_data_id=data.id AND input.data_input_field_id=field.id WHERE data.local_data_template_data_id <> 0"

When I run the query manually, the error message is:
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Do you have any idea, what is wrong there?

greetings, Christian.

[phalek]

It's working with this version:
mysql Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (i386) using readline 5.1

Code: Select all

Database changed
mysql> SELECT ds.id ,MAX(CASE WHEN type_code='index_type' THEN input.value END) index_type ,MAX(CASE WHEN type_code='index_value' THEN input.value END) index_value FROM data_template_data data INNER JOIN data_local ds ON ds.id=data.local_data_id INNER JOIN data_input_fields field ON field.data_input_id=data.data_input_id AND field.input_output='in' AND field.type_code IN ('index_type', 'index_value') INNER JOIN data_input_data input ON input.data_template_data_id=data.id AND input.data_input_field_id=field.id WHERE data.local_data_template_data_id <> 0;
+----+-------------------+-------------+
| id | index_type        | index_value |
+----+-------------------+-------------+
| 31 | voltageProbeIndex | XXXXX001    |
+----+-------------------+-------------+
1 row in set (0.31 sec)

What database version do you have ?

[brandstaetter]

Hi Phalek,
I have here a quite old Debian GNU 5.0 Database server with kernel 2.6.26-1-amd64, mysql-server 5.0.51a-24+lenny5 and libreadline 5.2-3.1.
The cacti 0.8.8f itself is working without problems with this datbase version.

Code: Select all

mysql> SELECT ds.id ,MAX(CASE WHEN type_code='index_type' THEN input.value END) index_type ,MAX(CASE WHEN type_code='index_value' THEN input.value END) index_value FROM data_template_data data INNER JOIN data_local ds ON ds.id=data.local_data_id INNER JOIN data_input_fields field ON field.data_input_id=data.data_input_id AND field.input_output='in' AND field.type_code IN ('index_type', 'index_value') INNER JOIN data_input_data input ON input.data_template_data_id=data.id AND input.data_input_field_id=field.id WHERE data.local_data_template_data_id <> 0;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql>

greetings, Christian

[phalek]

What is this command returning ?

Code: Select all

select @@sql_mode;

[brandstaetter]

Code: Select all

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql>

Christian.

[phalek]

Try this file.