SQL query to get alerts, and ack info

[gormank]

Ok, this is going to show my ignorance or dim wittedness, or both... Its actually more of an SQL question than Nagios but I'll throw it out there and hope.
I'm working on query to get alerts and ack info from the DB. The 1st query comes back with a short list as desired. The second has repeated info so my joins are wrong. The ack table and servicestatus table aren't directly related.

select name1 Hostname,
current_state Status,
name2 'Service Name',
output Output from nagios_objects, nagios_servicestatus where
object_id = service_object_id
and current_state > 0
and is_active = 1
and nagios_servicestatus.notifications_enabled = 1
order by name1;

select nagios_objects.name1 Hostname,
nagios_servicestatus.current_state Status,
nagios_objects.name2 'Service Name',
nagios_servicestatus.output Output,
nagios_acknowledgements.author_name 'Acked By',
nagios_acknowledgements.comment_data 'Comments'
from nagios_objects, nagios_servicestatus, nagios_acknowledgements
where nagios_objects.object_id = nagios_servicestatus.service_object_id
and nagios_servicestatus.service_object_id = nagios_acknowledgements.object_id
and nagios_servicestatus.current_state > 0
and nagios_objects.is_active = 1
and nagios_servicestatus.notifications_enabled = 1
order by name1;

If it isn't clear, I'm looking for alert info, and ack info in a fairly compact form. Basically, I'm trying to make a report that shows the alert (which is in place), the acker, and the ack comments, which should have the ticket info. All this nonsense is emailed periodically...
You can laugh and point at my SQL as needed.
Thanks!

[rkennedy]

where nagios_objects.object_id = nagios_servicestatus.service_object_id
and nagios_servicestatus.service_object_id = nagios_acknowledgements.object_id

If I understand this properly, you're looking at taking data from the first query to match where the second one is? Are you running this in conjunction with PHP or another language to define variables?

[gormank]

I'm trying to create a new query from the 1st to get more info from the DB. The 2nd query is a replacement for the 1st. I just posted the one that works for reference. The trouble is the new query isn't right--it returns duplicate rows it shouldn't.
I'm running it from mysql right now, but eventually it will be in a script. There are no variables.

If I take out the line below it returns a few thousand rows.

and nagios_servicestatus.service_object_id = nagios_acknowledgements.object_id

I see what it is now. There are multiple acks for the same alert, which makes the query return more rows... Now I need a way to only get the latest ack.

This brings up the question of what cleans the ack table? I'd think that once an alert clears the ack should go away.

[tmcdonald]

I'm going to have to be the bad guy here and step in saying this is out of scope for the forums. It's not quite training and it's not quite consulting, but it's not quite support either. We need to stay somewhat focused on issues with the software, and custom SQL queries stray pretty far from that.

You might have better luck using the JSON API available in Core:

https://labs.nagios.com/2014/06/19/expl ... -7-part-1/

Or the API introduced in XI, available under the Help menu in XI 5.

[gormank]

Actually the query is fine. The underlying question is how can one alert have multiple acks, and what cleans up the ack table?
I'm at 2.6 so there is no JSON.

[tmcdonald]

If you are running XI 2014R2.6 then the JSON API should be present. It was introduced in Core 4.0.7 which has been in XI since 2014R1.2.

In regards to the ack table, I would need to look at the structure but I know a lot of what is in NDO is historical data as opposed to current. This could be the case with the multiple acks showing up.

[gormank]

Sorry, I misspoke on the JSON API. I was long ago working on getting data out of RRD files and on my installation the rrdtools have XML export, there's no JSON export. Somehow I equated that and the JSON API.

I'll look into JSON to see if it works better than my SQL, but in reality now that I understand what's going on, I added the ack data to my report and it looks good. What I get is a table w/ these columns:

Hostname Status Service Name Output Acked By Comments

Acked by and comments come from the ack table.

You can close this as needed.
Thanks!

[rkennedy]

Sounds good. I'll close this out now, if you ever need assistance in the future then feel free to open a new thread.