Number of alarms and pending alarms from the database

[nms]

Hi all,

I am trying to get the amount of alarms for the past hour. it seems i can get this successfully from the nagios_statehistory table by the following query:

Code: Select all

SELECT count(*) FROM nagios.nagios_statehistory where state_time > CURRENT_TIMESTAMP - INTERVAL 1 HOUR;

What i'm not sure if i am also counting the "OK" states, in which i don't want. Can this be verified somehow?

As for the other case, I am requested to gather the amount of pending alarms (i.e alarms that are still active , irrelevant of the time). For this i'm not sure if i can get it from the same table.
How can i achieve this?

Thanks in advance

[dwhitfield]

We generally don't encourage interacting with the database directly. What appears to be missing from http://YOURSERVER/nagiosxi/reports/statehistory.php ? Perhaps a feature request is in order. Are you a customer? If so, post in the customer section of the forums, and the feature request will have a bit more gravitas. Still no guarantee, but it will help.

[nms]

Hi,

Understood, but this is being tried in a testbed before committing ourselves. At present we had just finished the proof of concept of Nagios XI and initiated the purchase for an unlimited version. As of now the process is still being handled, so am still waiting to become a customer.

I will surely post this as a feature requirement once i have an id/customer number which i can use, but i could greatly appreciate if you can let us know the answer of the first question since it seems we are quite near.

Not sure about the possibility of question 2 though. Could you please give us a hint?

[dwhitfield]

As for #2, is there a reason http://YOURSERVER/nagiosxi/includes/com ... iceattr=10 doesn't work for you?

I can put in a feature request now. The sooner it gets in, the sooner it might happen. It sounds like you just want more granularity on the State History Report. Is that correct?

[nms]

Hi,

Yes that is good. Is there a way to retrieve that from the database? I have set a report but it seems the only way is either via direct link or pdf format. The idea is to have a count (let's sat run this once weekly for reporting basis).

As for your question, yes the state history is already a good feature, what maybe lacking is the possibility to add the filter for state in the limit to section, so you can display only the problematic states (warning, critical and unknown while leaving out OK)

May i also ask if the query i posted actually holds the OK states as well?

Code: Select all

SELECT count(*) FROM nagios.nagios_statehistory where state_time > CURRENT_TIMESTAMP - INTERVAL 1 HOUR;

I thank you in advance for your time in reading these posts and for the help you are offering.

[dwhitfield]

Yes, that picks up ok states. For your future testing, all you need to do to see that is SELECT output FROM nagios.nagios_statehistory;. You could dump that to a file and grep it. As mentioned, we do not recommend directly interfacing with the db.

The ability to differentiate different states will be available in XI 5.5.

[nms]

Thanks dwhitfield,

It would be nice to have this feature via the GUI where you can also filter according to state. In the meantime since it is was a requirement from my end here's a how to achieve this:

Pending alarms:

Code: Select all

SELECT (SELECT count(*) FROM nagios.nagios_hoststatus where current_state != 0)+(SELECT count(*) FROM nagios.nagios_servicestatus where current_state != 0) as Pending_Alarms;

Alarms for the past hour:

Code: Select all

SELECT count(*) FROM nagios.nagios_statehistory where state_type=1 and state = 1 and state_time > CURRENT_TIMESTAMP - INTERVAL 1 HOUR;
SELECT count(*) FROM nagios.nagios_statehistory where state_type=1 and state = 2 and state_time > CURRENT_TIMESTAMP - INTERVAL 1 HOUR;
SELECT count(*) as UNKNOWN_Count FROM nagios.nagios_statehistory where state_type=1 and state = 3 and state_time > CURRENT_TIMESTAMP - INTERVAL 1 HOUR;

This can be marked as closed

[tmcdonald]

I'll be closing this thread now, but feel free to open another if you need anything in the future!