Hey, all.
I need to display the result of a query executed on an Oracle table in a Checkmk dashboard. Basically, the query returns some rows with positive integer.
Example:
========
18
23
5
78
120
12
61
========
Which approach do you think is the best to solve this problem:
Thank you for your help!
In your example it is hard to determine if the result is to be expected.
as in (from a local check perspective):
Too many variables here imho to give an advice.
Hi Glowsome, Thanks for the feedback.
I will describe the context better, I didn’t do this before to avoid making the post long.
Imagine a Toll Gate with 10 tollbooths. Each tollbooth has an identification, tollbooth-01, tollbooth-02, tollbooth-03…
Now, imagine that all passages that generate an error will be recorded in the database table.
So throughout the day, the number of errors will increase and at a certain point we will have something similar to this:
Tollbooth Quant. Errors
tollbooth-01----------23
tollbooth-02----------17
tollbooth-03----------98
tollbooth-04----------46
Every 3 minutes, I run a query that counts the number of errors per tollbooth. This way I can monitor the evolution of the number of errors that are being generated.
I need to get the query return and plot it on a dashboard, displaying the number of errors per tollbooth (just like the example described above)
If a tollbooth exceeds the “50” threshold, it will be classified as “Warning”, if it exceeds the “90” threshold, it will be classified as “Critical”.
I’m testing using Localcheck and it looks like this:
However, the way I configured it, it is necessary to run a script that executes the query on the Oracle table and generates a file for each tollbooth. This is necessary so that each tollbooth is identified as a service and helps in building the Dashboard.
I’m using Localchek from the Checkmk server itself (Centos 7), so I save the files in the “/usr/lib/check_mk_agent/local/” folder, then run Service Discovery to visualize the Services and plot them on the Dashboard.
The current configuration already meets the objective, the information can now be monitored, but is this the best method? Can’t we eliminate the need to generate a file for each tollbooth?
Can’t we bring the data to Checkmk via DBlink, eliminating the need to run scripts every “x” minutes? The idea here is to identify the best way, considering using the least amount of hardware resources (server) and network assets.
I tried to explain the context better, I hope I was successful. But if the scenario is still cloudy, please signal.
I really appreciate everyone’s opinion.
(Sorry for the writing errors)
Thanks again!
I will test the configuration suggested in this post by @openmindz. It would eliminate the need to generate individual files for each tollbooth.
Curious as to what the Query you are running gives as output.
Maybe its even possible to directly query the database, and use the output in a variable and process ait further in a/the local check.
Not that i’m familiar with Oracle, but i have seen examples https://www.pontikis.net/blog/store-mysql-result-to-array-from-bash with MySQL/mariaDB where a/the bash script Queries the database and the result is put into a variable, and then processed.
From what i read for Oracle you could call on sqlplus and store the results in a variable ( as said i dont know what the result looks like)
data=$(sqlplus -S ${USER}/${PASSWORD} << EOF
set head off
set feedback off
set pagesize 5000
set linesize 30000
Your_SQL_Query_HERE
exit
EOF)
echo "$data"
Usually with creating a (bash-) Local check, is i examine what i can grab/need and how its presented and then go from there formatting it corectly for my use.
Your case has taken my attention, and as i like to explore stuff i made the following (in not having a Oracle DB server i resorted to mariaDB) Localcheck.
Just a simple setup:
I created a DB cmk-test, with in it a table called toll_booths with 3 columns:
I filled the table with the example data you posted initially ( so 7 records) :
Next i created a localcheck on the database-host with the following code (dont forget to make the script executable):
#!/bin/bash
set -f
IFS=$'\n'
results=( $(mysql --batch -uroot -pMySuperSecretPassword -D "cmk-test" -N -e "select * from toll_booths") )
for line in "${results[@]}";
do
booth_name=$(echo $line | awk '{print $2}')
booth_errors=$(echo $line | awk '{print $3}')
echo "P \"$booth_name\" count=$booth_errors;50;100;;"
done
This resulted in the following results in the CMK host-checks ( after rescanning.accepting):
So from my perspective it is even possible to bypass the ‘write-to-file’ - part, and directly query a database and use the results to generate results in CMK.
Again, do mind this is just a proof of concept, i can not determine if (without further logic) it is wanted to query the database directly, as the script is not handling database-down - issues and thus process it.
But atleast via Localcheck this could be usable (imho)
So if a direct query can be made to your OracleDB, with the correct parameters a similar result can be achieved.
Did you save the script in the default directory for Linux (“/usr/lib/check_mk_agent/local”)?
Yes the script is placed in /usr/lib/check_mk_agent/local
I’ll adjust some formatting details, but this is “fluff”.
With your support, I achieved this result. This dashboard is already in production.
Thank you very much again for your attention, support and time invested.
Best regards!
Hi @marciano ,
Awesome to hear that i was able to aid you in this.
Would you be so kind as to mark this topic as ‘solved’ ?
