Graphing SQL query results

[mase2hot]

Hi,

I've looked over the documentation but its very focused on SNMP. I have a MySQL query that I want the results graphing. Is there a guide anywhere on an example that I can use?

Thanks

[silvertip257]

You'll need to create a script which can run the SQL query and return the results in a format Cacti desires.

I've done this before for MySQL question and connection counts in the past.
You'll use a Data Input Method which references the script.
Then reference the DIM in a new Data Template for each piece of data you're retrieving.
On Graph Templates, instead of choosing SNMP Data, you'll choose the name of that Data Input Method.


http://docs.cacti.net/manual:087:3a_adv ... with_cacti

[mase2hot]

Yes I sort of gathered that but I dont know how to get to that point. Where I should put my script an how it needs to be formatted...

[silvertip257]

Yes I sort of gathered that but I dont know how to get to that point. Where I should put my script an how it needs to be formatted...

You didn't say if you were using Cacti on Windows or Linux...

With Cacti (EPEL package) installed on CentOS Linux, the path is:
/var/www/cacti/scripts/

And in cases where I want to denote custom scripts from the default distributed ones, I'll add a custom directory (so /var/www/cacti/scripts/custom/) and put the scripts in there instead.

For a GNU/Linux install of Cacti, there will be a cronjob in /etc/init.d/cacti which contains the Cacti path. Or your Apache config file for Cacti.
Or find it from the web interface: Configuration > Settings > Paths > Logging > Cacti Log File Path (remove the logging parts of that path and you have the root path).

Then reference the script in a Data Input Method like so:
Input Type: Script/Command
Input String: <path_cacti>/scripts/yourscripthere <param1> <param2> <...etc>
Input Fields: are the parameters (param1,2,etc) you pass to the script (if necessary)
Output Fields: are the data points you create which can be referenced on your Graph Templates

[mase2hot]

Sorry, its on Debian. Ok so lets say my script was (select timedelay from testtable where call_id is null order by seize_time desc limit 1).

Would my script contain a connection string to my database or various databases if I use <param> as hostname?

Also output field in this case would be "timedelay" ?


Thanks

[silvertip257]

Sorry, its on Debian. Ok so lets say my script was (select timedelay from testtable where call_id is null order by seize_time desc limit 1).

Would my script contain a connection string to my database or various databases if I use <param> as hostname?

Also output field in this case would be "timedelay" ?


Thanks

It all depends on how you write your script.
When I wrote mine, I passed username, password, and a name for my pre-written/static SQL query.

The name for the output field is arbitrary (as in does not have to match the field in your query), but makes sense to match the field in the query.
And since you only have one field, you can return just a number and not a name:value pair.

Ok, sometimes an example is needed where an explanation would only confuse.
Here's an example that should work, but I've not tested it.

Certainly adjust the BIN_ values to reflect the actual paths on your Debian box.
And any username, pass, port, etc to reflect your actual settings.

Code: Select all

#!/bin/bash
BIN_MYSQL='/usr/bin/mysql'
BIN_AWK='/bin/awk';

host="192.168.1.1"
user="bob"
pass="password123"
port=3306
query="select timedelay from testtable where call_id is null order by seize_time desc limit 1"

# execute the query and parse it with awk
res_td=$($BIN_MYSQL -h $host -P $port -u $user -p "$pass" -e "$query" | $BIN_AWK '/timedelay/{print $2}'

# print the result to stdout
echo $res_td

If you have more than one field, the output format to stdout changes.
For more on that, see the Cacti Doc link I provided a few responses ago.

[mase2hot]

Ok thanks, yes an actual example is a massive help. I will see if I can get this going now.

[mase2hot]

I presume running this script standalone should give me a result? It currently doesn't, it runs now without any errors but returns a blank space.

/timedelay/{print $2}

does this timedelay need to be its own file somewhere? I currently have 2 files, this script and the RRD.

Thanks

[silvertip257]

I presume running this script standalone should give me a result? It currently doesn't, it runs now without any errors but returns a blank space.

/timedelay/{print $2}

does this timedelay need to be its own file somewhere? I currently have 2 files, this script and the RRD.

Thanks

No, no extra files.
That is part of an awk expression.

But instead of testing what I sent you, I modified it from what I had used in the past (and that didn't apply to your instance).

At least two options, I'll present the simpler of the two.

Code: Select all

#!/bin/bash
BIN_MYSQL='/usr/bin/mysql'
BIN_AWK='/bin/awk';

host="192.168.1.1"
user="bob"
pass="password123"
port=3306
query="select timedelay from testtable where call_id is null order by seize_time desc limit 1"

# execute the query and parse it with awk
res_td=$($BIN_MYSQL -h $host -P $port -u $user -p "$pass" -e "$query" | $BIN_AWK '/[0-9]/{print $1}')

# print the result to stdout
echo $res_td

[mase2hot]

Thanks for you help, I have it working now

For you example I should be able to create many others, thanks again!

[silvertip257]

Thanks for you help, I have it working now

For you example I should be able to create many others, thanks again!

Awesome.
You're welcome.

You could take that one step further and modify your script so you can pass the host, port, username, password, and query to the script.
Easy enough to do with some reading on Cacti and Bash.