Page 1 of 1
Data extraction issues from Maria DB
Posted: Tue Feb 19, 2019 6:16 am
by keerthi.seetharaman
Hello Team,
We are trying to extract the data of hosts and services checks configured on Nagios XI via the below command from Maria DB. We are only finding the output as alias name rather than host name.
MariaDB [nagios]> SELECT h.display_name, s.display_name from nagios_hosts h, nagios_services s where h.host_object_id = s.host_object_id INTO OUTFILE '/home/skeerthi/host.csv' FIELDS TERMINATED BY '|';
Can you please suggest what needs to be done.
Regards,
Keerthi Prashanth S
Re: Data extraction issues from Maria DB
Posted: Tue Feb 19, 2019 7:58 am
by scottwilkerson
The query is a little more complicated than that.
For services it looks like this
Code: Select all
SELECT o.name1 hostname, o.name2 servicename FROM nagios_objects o LEFT JOIN nagios_services s ON o.object_id = s.service_object_id WHERE is_active=1 and objecttype_id=2
for hosts
Code: Select all
SELECT o.name1 hostname FROM nagios_objects o LEFT JOIN nagios_hosts s ON o.object_id = s.host_object_id WHERE is_active=1 and objecttype_id=1
Re: Data extraction issues from Maria DB
Posted: Wed Feb 27, 2019 2:24 am
by keerthi.seetharaman
Thanks for your quick reply.
But is it possible to merge both the host and service commands into a single command from where i can get the output in a single file.
For example:-
Server abc :- linux -prod - CPU
linux -prod - memory
etc.
Re: Data extraction issues from Maria DB
Posted: Wed Feb 27, 2019 8:04 am
by scottwilkerson
you could do something like this
Code: Select all
SELECT o.name1 hostname, o.name2 servicename
FROM nagios_objects o
LEFT JOIN nagios_services s ON o.object_id = s.service_object_id
WHERE is_active=1 and objecttype_id IN (1,2)
ORDER BY hostname, servicename;
Re: Data extraction issues from Maria DB
Posted: Wed Feb 27, 2019 9:25 am
by keerthi.seetharaman
Thanks its working fine now..
Please close this ticket
Re: Data extraction issues from Maria DB
Posted: Wed Feb 27, 2019 9:54 am
by scottwilkerson
keerthi.seetharaman wrote:Thanks its working fine now..
Please close this ticket
great locking thread