Page 1 of 2
extract info from MySQL db
Posted: Fri Mar 22, 2013 6:16 am
by Rhobar
Hello World!
Now my nagios+NDOutils+MySQL is running up!
so, I have other questions for you
please, don't kill me
now I have to create an php application that reads from MySQL db and creates XML doc...
The XML doc is a probedata of checked service from nagios
if I want only the informations about check_local_load service, for example, which select MySQL command should I to use?
Re: extract info from MySQL db
Posted: Fri Mar 22, 2013 7:19 am
by scottwilkerson
Re: extract info from MySQL db
Posted: Fri Mar 22, 2013 10:06 am
by Rhobar
I read it, but it is a bit too complicated for me (my English is not very good
), so I asked for help here
Re: extract info from MySQL db
Posted: Fri Mar 22, 2013 3:14 pm
by abrist
It all depends what you called the service check. This is usually not support that we provide as it is a custom solution for a specific setup. You should spend a bit of time getting acquainted with mysql and the syntax of queries. Start with the following documentation:
http://dev.mysql.com/doc/
Re: extract info from MySQL db
Posted: Tue Mar 26, 2013 7:06 am
by Rhobar
I tried this:
Code: Select all
mysql> select output from nagios_servicestatus;
+--------------------------------------------------------------------+
| output |
+--------------------------------------------------------------------+
| PROCS OK: 106 processes with STATE = RSZDT |
| SSH OK - OpenSSH_5.3 (protocol 2.0) |
| SWAP OK - 100% free (4031 MB out of 4031 MB) |
| PING OK - Packet loss = 0%, RTA = 0.05 ms |
| DISK OK - free space: / 10828 MB (72% inode=88%): |
| USERS OK - 2 users currently logged in |
| HTTP OK: HTTP/1.1 200 OK - 265 bytes in 0.011 second response time |
| OK - load average: 0.24, 0.05, 0.02 |
+--------------------------------------------------------------------+
8 rows in set (0.00 sec)
and it is ok, but which select command I hate to use if I want only the one of these lines?
For example, I need only check_load line (in this case is the last), is it possible to have a select command that has as result only one of these lines?
How becomes the select command in that case?
Re: extract info from MySQL db
Posted: Tue Mar 26, 2013 7:40 am
by scottwilkerson
First you will need to find the line to filter by
run
Code: Select all
select * from nagios_servicestatus;
Get the service_id for the one you want
Then
Code: Select all
select output from nagios_servicestatus where service_id=<ID_YOU_SELECTED>;
Re: extract info from MySQL db
Posted: Tue Mar 26, 2013 7:53 am
by Rhobar
thanks
i've done this:
Code: Select all
mysql> select output from nagios_servicestatus where service_object_id=3;
+-------------------------------------+
| output |
+-------------------------------------+
| OK - load average: 0.25, 0.16, 0.06 |
+-------------------------------------+
1 row in set (0.00 sec)
but another question...
that object_id is always the same or changes sometimes?
Re: extract info from MySQL db
Posted: Tue Mar 26, 2013 9:10 am
by scottwilkerson
It should always remain the same. Your not going to get a ton of basic SQL support here but one thing you could do is something like this, replacing localhost and Ping with what you are looking for
Code: Select all
select s.output from nagios_servicestatus s, nagios_objects o where s.service_object_id=o.object_id AND o.name1='localhost' AND o.name2='Ping';
Re: extract info from MySQL db
Posted: Fri Mar 29, 2013 4:29 am
by Rhobar
hi!
scottwilkerson wrote:Code: Select all
select s.output from nagios_servicestatus s, nagios_objects o where s.service_object_id=o.object_id AND o.name1='localhost' AND o.name2='Ping';
what does "s" stand for?
scottwilkerson wrote:Your not going to get a ton of basic SQL support here
you're right, sorry
Re: extract info from MySQL db
Posted: Sat Mar 30, 2013 12:31 pm
by scottwilkerson
Rhobar wrote:what does "s" stand for?
In the query I had this
So s becomes shorthand for nagios_servicestatus table
and because I also had
o is short for nagios_objects
you could also use
Code: Select all
select nagios_servicestatus.output from nagios_servicestatus, nagios_objects where nagios_servicestatus.service_object_id=nagios_objects.object_id and nagios_objects.name1='localhost' and nagios_objects.name2='Ping';
But that's just messy compared to
Code: Select all
SELECT s.output FROM nagios_servicestatus s, nagios_objects o WHERE s.service_object_id=o.object_id AND o.name1='localhost' AND o.name2='Ping';