I need help with the following SQL query to generate a report for backup jobs that had failed more than 2 days so I can feed the results to our automation ticketing system. (the query used to work before but now it does not)...If anyone can point out what could be wrong....
WITH get_var AS (
SELECT TRUNC(sysdate) - TRUNC(sysdate-2) nbr_of_days FROM apt_v_dual
)
, get_last_backup_date AS (
SELECT client_id, TRUNC(sysdate)-TRUNC(MAX(finish_date)) last_backup FROM apt_v_job WHERE summary_status < 2 AND finish_date > sysdate-45 GROUP BY client_id
)
--select * from get_last_backup_date;
,get_counts AS (
SELECT
to_char(start_date,'MM/DD/YYYY') the_date,
client_id,
server_id,
rtrim(lower(client_name),'.') client_name,
MAX(vendor_status) max_vendor_status,
NVL(SUM(DECODE(summary_status,0,1,0)),0) success_count,
NVL(SUM(DECODE(summary_status,1,1,0)),0) partial_count,
NVL(SUM(DECODE(summary_status,2,1,0)),0) failed_count
FROM apt_v_job
WHERE
start_date BETWEEN sysdate-2 AND sysdate
AND client_id <> server_id
AND job_type IN ('101','102')
AND vendor_status NOT IN ('90','129','150','99','230','239','240','245','196','999999')
AND rtrim(lower(client_name), '.') NOT IN (select distinct lower(media_server_name) from apt_v_nbu_media_server)
AND client_name NOT like 'nbum%'
GROUP BY
to_char(start_date,'MM/DD/YYYY'),
client_id,
server_id,
rtrim(lower(client_name), '.')
)
-- select * from get_counts;
, get_sums AS (
SELECT
c.client_id,
c.server_id,
s1.display_name client,
s1.os_platform,
s2.display_name master,
MAX(lb.last_backup) last_backup,
MAX(c.max_vendor_status) max_vendor_status,
SUM(c.success_count) success_count,
SUM(c.partial_count) partial_count,
SUM(c.failed_count) failed_count
FROM get_counts c
INNER JOIN apt_v_server s1 ON s1.server_id = c.client_id
INNER JOIN apt_v_server s2 ON s2.server_id = c.server_id
LEFT JOIN get_last_backup_date lb ON lb.client_id = c.client_id
WHERE s1.os_platform IS NOT NULL
GROUP BY
c.client_id,
c.server_id,
s2.display_name,
s1.os_platform,
s1.display_name
)
--select * from get_sums;
SELECT
nbr_of_days,
max_vendor_status,
master,
os_platform,
trim(both ' ' from client) client,
NVL(last_backup,45) last_backup
FROM get_sums, get_var
-- WHERE failed_count >= nbr_of_days
-- AND
-- (last_backup >= nbr_of_days OR last_backup IS NULL)
ORDER BY 1,2,3,4
