Data extraction issues from Maria DB

[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

[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

[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.

[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;

[keerthi.seetharaman]

Thanks its working fine now..

Please close this ticket

[scottwilkerson]

Thanks its working fine now..

Please close this ticket

great locking thread