QUERY MARIA DB TO FILTER CURRENT TIMESTAMP ONLY

Asked

Viewed 29 times

-1

The query:

SELECT DISTINCT
    hosts.name as "Unidade de Negócio",
    CASE
        WHEN services.acknowledged = 1
            THEN acknowledgements.comment_data
        ELSE 0
    END AS "Análise",
    hosts.last_state_change*1000 as "Duração",
    acknowledgements.entry_time
FROM
    hosts
    INNER JOIN services ON services.host_id=hosts.host_id
    LEFT  JOIN acknowledgements ON acknowledgements.host_id = hosts.host_id
WHERE
    hosts.enabled = 1
    AND hosts.name LIKE 'pa-%'
    AND services.description = 'Status FG.'
    AND hosts.output not LIKE '%ok%'
    AND services.state = 2
    AND services.scheduled_downtime_depth =0
    ORDER BY acknowledgements.entry_time DESC

The problem:

He’s repeating the first three columns.

inserir a descrição da imagem aqui

database select

  • You did join with services and in this table there seems to be 3 distinct records for this same host. What was the expected result?

  • @Andersoncarloswoss the expected result is to bring all the names of the hosts with the last updated comment and the duration that it is in the monitoring, but each time we update the comment it makes an Insert to stay in the log and this causes the data to repeat, the only thing that does not repeat is the entry time, but even when I use a group by in the fields and then an order by in the entry time, only the entry time and host is ordered, but the comment that is what I need not, IE, does not work with only group by. I’m using version 5.5 of mariadb.

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

1 answer

0

Use a GROUP BY the first three columns and a MAX to catch the biggest TIMESTAMP:

SELECT DISTINCT h.name as "Unidade de Negócio",
                CASE
                    WHEN s.acknowledged = 1
                        THEN a.comment_data
                    ELSE 0
                END AS "Análise",
                h.last_state_change * 1000 as "Duração",
                MAX(a.entry_time) AS a.entry_time
  FROM hosts h
 INNER JOIN services s ON s.host_id=h.host_id
  LEFT JOIN acknowledgements a ON a.host_id = h.host_id
 WHERE h.enabled = 1
   AND h.name LIKE 'pa-%'
   AND s.description = 'Status FG.'
   AND h.output not LIKE '%ok%'
   AND s.state = 2
   AND s.scheduled_downtime_depth =0
 GROUP BY h.name, s.acknowledged, h.last_state_change
 ORDER BY a.entry_time DESC

Browser other questions tagged database select

You are not signed in. Login or sign up in order to post.