nagios acknowledgements query

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

[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

[mejokj]

Hello,

Yes that is awesome. Now I also need service name. How can I achieve that?

[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

[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

[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

[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

[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

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

[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