Page 1 of 1
nagios acknowledgements query
Posted: Thu Dec 24, 2020 7:14 am
by mejokj
Hello,
I need to get the nagios acknowledgements details in between certain dates. I have tried the below query.
But the hosts details is not inside the query. Is there any way to get the hosts details included with the acknowledgements details.
SELECT * FROM nagios_acknowledgements WHERE entry_time BETWEEN '2013-04-01' AND '2014-12-05' order by entry_time desc;
Re: nagios acknowledgements query
Posted: Mon Dec 28, 2020 5:24 pm
by vtrac
Hi mejokj,
I came up with two SQL queries.
You can try both and see which one works for you.
This one lists the "ServerName" then everything else in 'nagios_acknowledgements' tables:
Code: Select all
SELECT a.name1 as ServerName, b.* FROM nagios_objects a, nagios_acknowledgements b WHERE a.object_id=b.object_id AND entry_time BETWEEN '2013-04-01 12:00:00' AND '2020-12-28 23:08:00' order by entry_time DESC;
This lists "display_name", "IP address" and selected columns in "nagios_acknowledgements" tables:
Code: Select all
SELECT c.display_name, c.address, b.acknowledgement_id, b.entry_time, b.entry_time_usec, b.acknowledgement_type, b.object_id, b.state, b.author_name, b.comment_data FROM nagios_objects a, nagios_acknowledgements b, nagios_hosts c WHERE a.object_id=b.object_id AND a.name1=c.display_name AND entry_time BETWEEN '2013-04-01 12:00:00' AND '2020-12-28 23:08:00' order by entry_time DESC;
The second query is longer but the results are better to read.
Best Regards,
Vinh
Re: nagios acknowledgements query
Posted: Tue Dec 29, 2020 8:44 am
by mejokj
Hello,
Yes that is awesome. Now I also need service name. How can I achieve that?
Re: nagios acknowledgements query
Posted: Tue Dec 29, 2020 4:09 pm
by vtrac
Hi mejokj,
The below SQL query will give you the 'Server', 'Service", "IP address' and more.
If one of the service is blank (empty), which means it is the SERVER's issue (not a SERVICE's issue) ..... like host (server) is un-reachable, for example.
Code: Select all
SELECT c.display_name as 'Server', a.name2 as 'Service', c.address, b.acknowledgement_id, b.entry_time, b.entry_time_usec, b.acknowledgement_type, b.object_id, b.state FROM nagios_objects a, nagios_acknowledgements b, nagios_hosts c WHERE a.object_id=b.object_id AND a.name1=c.display_name AND entry_time BETWEEN '2013-04-01 12:00:00' AND '2020-12-29 23:08:00' order by entry_time DESC;
Best Regards,
Vinh
Re: nagios acknowledgements query
Posted: Mon Jan 04, 2021 5:55 am
by mejokj
Hi,
We can see service names now with the last query shared but who acknowledges and comments are missing.
Could you help me with the query that shows who acknowledges and comments as well in the last query shared with us.
Thanks
Re: nagios acknowledgements query
Posted: Mon Jan 04, 2021 4:10 pm
by vtrac
Hi mejokj,
Please try the below SQL command (added author_name and comment_data):
Code: Select all
SELECT c.display_name as 'Server', a.name2 as 'Service', c.address, b.acknowledgement_id, b.entry_time, b.entry_time_usec, b.acknowledgement_type, b.object_id, b.state, b.author_name, b.comment_data FROM nagios_objects a, nagios_acknowledgements b, nagios_hosts c WHERE a.object_id=b.object_id AND a.name1=c.display_name AND entry_time BETWEEN '2013-04-01 12:00:00' AND '2020-12-29 23:08:00' order by entry_time DESC;
Regards,
Vinh
Re: nagios acknowledgements query
Posted: Wed Jan 06, 2021 12:09 pm
by mejokj
Hi vtrac,
Thanks for the query. I think we are getting the data now.
Do we get this report on a daily basis like moring 00:00:00 hr to 23:59:00? Do we have any query for this?
Thanks
Re: nagios acknowledgements query
Posted: Wed Jan 06, 2021 5:27 pm
by vtrac
Hi mejokj,
Unfortunately, you can't create a custom report using the Nagios XI (Report) GUI.
I have confirmed this with my teammates.
So, you would need to do this using a cron job.
Regards,
Vinh
Re: nagios acknowledgements query
Posted: Thu Jan 21, 2021 4:11 pm
by mejokj
Thanks for the support.
Is there any query to get the history from database like below?
SELECT c.display_name as 'Server', a.name2 as 'Service', c.address, b.acknowledgement_id, b.entry_time, b.entry_time_usec, b.acknowledgement_type, b.object_id, b.state, b.author_name, b.comment_data FROM nagios_objects a, nagios_acknowledgements b, nagios_hosts c WHERE a.object_id=b.object_id AND a.name1=c.display_name AND entry_time BETWEEN '2013-04-01 12:00:00' AND '2020-12-29 23:08:00' order by entry_time DESC;
Re: nagios acknowledgements query
Posted: Fri Jan 22, 2021 1:03 pm
by vtrac
Hi mejokj,
Our support team provide technical issues related to Nagios XI and our other licensed products.
I'm sorry, but we don't have any SQL custom scripts for history related.
Best Regards,
Vinh