NagiosXI alarm report

[Anto]

Hi Team

Our customer would like to have a report like one reported in "Acknowledgements" where there are all the ack made by the control room's operators, but having the timestamp of the alarms and the status check output string.

So the report should have the following columns or similar:

"alarm timestamp" "host" "service" "acknowledge timestamp" "acknowledge user" " ack comment" "status check output"

and seems these information are present in different nagios db tables.
Does someone suggest how to do this report ?

Thanks and Regards
Antonello

[benjaminsmith]

Hi Antonello,

Hope you are enjoying your trial of Nagios XI.

Since that data is not available on the page, if you'd like, I can submit a feature request to the development team on your behalf. However, the data is available you can pull this from either the database or using th REST API.

For the database, it's store in the nagios database in the nagios_comments and nagios_servicestatus tables. Here's an example query. Let me know if that works for you or not.

Code: Select all

# log into mysql
mysql -u root -pnagiosxi
use nagios;
# Comment data with status output for services
select nagios_services.display_name, nagios_servicestatus.service_object_id, nagios_comments.author_name, nagios_comments.comment_time,  nagios_comments.comment_data, nagios_servicestatus.output from nagios_comments inner join nagios_servicestatus on nagios_servicestatus.service_object_id = nagios_comments.object_id inner join nagios_services on
nagios_comments.object_id = nagios_services.service_object_id;

For the API, take a look at the documentation at Help > API Docs in the GUI. The GET objects/servicestatus and GET objects/comment API endpoints would have this information.

Regards,
Benjamin

[Anto]

Hi Ben

I'm appreciating nagiosXI a lot. We are using Nagios and NagiosXI to monitor Network devices, Databases (Oracle,DB2) and Servers.

About this post I think is enough if you can provide a query similar to your example that has also a join with nagios_statehistory table to have both ack timestamp and alarm timestamp in the same ouput (and only for hard state change for example).
I don't know the relations between tables so I'll really appreciate if you can help creating this query.

Thanks and regards

Antonello

[benjaminsmith]

Hi Antonello,

I updated the query above to select the nagios database first before running the query. I added the following fields to the query for status time, state of the service, and type of state (0=soft and 1=hard).

nagios_servicestatus.status_update_time
nagios_servicestatus.current_state
nagios_servicestatus.state_type

Let me know if this works or not. It should filter anything that was not in a hard state at the time of the comment or acknowledgment.

Code: Select all

select nagios_services.display_name, nagios_servicestatus.service_object_id, nagios_comments.author_name, nagios_comments.comment_time,  nagios_comments.comment_data, nagios_servicestatus.output,nagios_servicestatus.status_update_time,nagios_servicestatus.current_state, nagios_servicestatus.state_type from nagios_comments inner join nagios_servicestatus on nagios_servicestatus.service_object_id = nagios_comments.object_id inner join nagios_services on
nagios_comments.object_id = nagios_services.service_object_id where nagios_servicestatus.state_type = 1;

Regards,
Benjamin

[Anto]

Hi Ben

The query seems to be ok, I added these columns, nagios_servicestatus.last_state_change and nagios_servicestatus.last_time_ok to have the time of the last state change from OK to CRITICAL.
Can you provide the same query for the acknowledge on Hosts ?

Another question is: with this query seems I collect also the maintenance request, is it possible to filter out the maintenance and consider only the acknowledge?


Thanks and regards
Antonello

[benjaminsmith]

Hi Antonello,

For the hosts, you can just update that query to call the host-related tables, for example, nagios_hoststatus and nagios_hosts.

The following command will show you all of the tables in the Nagios database.

Code: Select all

mysql -u root -pnagiosxi
use nagios;
show tables;

Looking over the nagios_comments table, there is a field for entry_type. I ran some tests and scheduled maintenance is set for entry_type = 2, so setting the query to only entry_type = 1, should remove that data.

If you run into any errors with the above query, please post them to the thread to troubleshoot.

Best Regards,
Benjamin