SQL update for generating reports from job failures

[riwood]

I wanted to share an updated SQL query for failed Discovery jobs.

The original code was shared in the blog post: "Generating reports from Unimus job failures". It is a very useful starting point, but I found the code to be slow and resource intensive when parsing a large database.

Here's my update for the MySQL query for fetching last Discovery jobs within last week if they failed:

Code: Select all

WITH latest_per_device AS (
    SELECT device_id, successful, create_time
    FROM (
        SELECT
            dhj.device_id,
            dhj.successful,
            dhj.create_time,
            ROW_NUMBER() OVER (PARTITION BY dhj.device_id
                               ORDER BY dhj.create_time DESC) AS rn
        FROM device_history_job dhj
        WHERE dhj.job_type = 'DISCOVERY'
          AND dhj.create_time > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY))
    ) t
    WHERE rn = 1
)

SELECT
    z.name AS Zone,
    COUNT(d.id) AS `Number of Devices`
FROM device d
JOIN latest_per_device lpd   ON lpd.device_id = d.id
JOIN zone z                  ON z.id = d.zone_id
WHERE lpd.successful = 0
GROUP BY z.name 
ORDER BY z.name ASC,`Number of Devices` DESC;

This actually speeds up the query by a factor of 5. Note the MSQL version needs to be v8 or higher.