0

For my monitoring tool (Centreon), I need to create a script that will send me every day a report with my hosts / services that have been acknowledged for X days.

For this I need to get information in several tables which I can do with the following query

SELECT DISTINCT     services.host_id,
                        hosts.name,
                        services.service_id,
                        services.description,
                        comments.author,
                        comments.DATA,from_unixtime(comments.entry_time), 
                        comments.comment_id
FROM  services,
        hosts,
        comments 
WHERE services.host_id=hosts.host_id 
        AND hosts.host_id=comments.host_id 
        AND services.acknowledged='1' 
        AND services.enabled='1' 
        AND comments.DATA NOT LIKE '%downtime%'

MySQL query Result

What I'll need is to have as result only the highest comment_id per service_id

Is there a way to do this directly in a query or do I have to import my entire result into my python script and process the data afterwards?

Thanks in advance for your help.

Christophe Pelichet
  • 11
  • 1
  • *only the highest comment_id per service_id* What about another columns? Any? From the same row? something else? And specify **precise** MySQL version. – Akina Feb 09 '21 at 12:14
  • This is something of a FAQ, and hence easily solved with a quick search. Nevertheless, if you're still struggling, please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query). And welcome to SO. – Strawberry Feb 09 '21 at 12:15

1 Answers1

0

Sort Inside of Group By Results

You can "GROUP By" the results by service_id and use "MAX()" (Aggregate Function, you can also use Min() ) to have the highest comment in the grouped result. Try the following query:


SELECT services.host_id,
                hosts.name,
                services.service_id,
                services.description,
                comments.author,
                comments.DATA,
                from_unixtime(comments.entry_time), 
                max(comments.comment_id)
FROM services,
     hosts,
     comments 
WHERE services.host_id=hosts.host_id 
  AND hosts.host_id=comments.host_id 
  AND services.acknowledged='1' 
  AND services.enabled='1' 
  AND comments.DATA NOT LIKE '%downtime%'
GROUP By services.service_id
;
Soroosh Khodami
  • 1,185
  • 9
  • 17