The query was originally written with temp tables (WITH clauses) to break the query down into sections for both speed and readability.
We were trying to minimize the number of temp tables used. Also, we were trying to make the query work in SSRS, so we combined a couple of the temp table queries into one (so the second version has a coulpe of repetitive sub-queries).
The problem we are having is simply getting the query to run quickly in SSRS, as it is doing in OSD. If anyone happens to know a way to also clean up the SQL / make it more efficient in addition to solving the main problem, be our guest!
Thank you so much for your time and efforts!!!
Below are both versions:
------------------ FIRST VERSION --------------------
WITH valid_dates AS (
--Get all weekdays that occurred in the last 100 days
SELECT TRUNC(sysdate) - level AS dates
FROM dual
WHERE TO_CHAR(TRUNC(sysdate) - level, 'D') BETWEEN 2 AND 6
CONNECT BY level <= 100
),
corrected_backup_alerts AS (
--Get only backup alerts and convert their dates from seconds since Jan 1st, 1970 to a date format. Correct applicable date values (alerts that have datetime values between 12:00 AM and 7:00 AM actually occurred the day before)
SELECT (CASE
WHEN (TO_NUMBER(TO_CHAR(TO_DATE('19700101','YYYYMMDD') + date_reception/86400,'HH24')) >= 0 AND
TO_NUMBER(TO_CHAR(TO_DATE('19700101','YYYYMMDD') + date_reception/86400,'HH24')) < 7)
THEN
TRUNC(TO_DATE('19700101','YYYYMMDD') + date_reception/86400 - 1)
ELSE
TRUNC(TO_DATE('19700101','YYYYMMDD') + date_reception/86400)
END) alert_date,
hostname
FROM tec_t_evt_rep
WHERE class = 'XMPAlert' AND
sub_source = 'Backup Exec' AND
((msg LIKE 'Backup Exec - Insert Tape%' OR msg LIKE 'Backup Exec - Job Cancelled%') OR
(long_msg LIKE 'Backup Exec - Insert Tape%' OR long_msg LIKE 'Backup Exec - Job Cancelled%'))
),
valid_alerts AS (
--For hostnames that had a backup alert occur on the most recent weekday (from inner query), get all backup alerts that occurred in the last 100 days (excluding weekends)
SELECT alert_date,
hostname
FROM corrected_backup_alerts c_b_a INNER JOIN valid_dates ON valid_dates.dates = c_b_a.alert_date
WHERE hostname IN (--Choose records where the corrected alert dates were the most recent weekday
SELECT DISTINCT hostname
FROM corrected_backup_alerts
WHERE alert_date = (--Get the most recent weekday
SELECT MAX(dates)
FROM valid_dates)
)
)
--Get a count of all alerts (only for a count of 3 or more) whose alert_date is greater than the latest date where a break in consecutive dates occurred (in other words, get all most recent consecutive alert occurrences)
SELECT hostname, COUNT(DISTINCT alert_date) AS num_failures
FROM valid_alerts v_a_outermost
WHERE alert_date > (--Get the latest alert_date (other than the most recent weekday) where there exists no records such that that record's alert_date is not consecutively higher than that sought alert_date
SELECT MAX(alert_date) AS max_nonconsec_alert
FROM valid_alerts v_a_outer
WHERE NOT EXISTS (SELECT 'x'
FROM valid_alerts v_a_inner
WHERE v_a_inner.hostname = v_a_outer.hostname AND
(((TO_CHAR(v_a_outer.alert_date, 'D') BETWEEN 2 AND 5) AND
(v_a_inner.alert_date = v_a_outer.alert_date + 1)) OR
((TO_CHAR(v_a_outer.alert_date, 'D') = 6) AND
(v_a_inner.alert_date = v_a_outer.alert_date + 3)))) AND
v_a_outermost.hostname = v_a_outer.hostname AND
v_a_outer.alert_date <> (--Get the most recent weekday
SELECT MAX(dates)
FROM valid_dates)
)
GROUP BY hostname
HAVING COUNT(DISTINCT alert_date) >= 3
ORDER BY hostname ASC
------------------------------------------------------
------------------ SECOND VERSION --------------------
WITH valid_alerts AS (
--For hostnames that had a backup alert occur on the most recent weekday (from inner query in the WHERE clause), get all backup alerts that occurred in the last 100 days (excluding weekends)
SELECT alert_date,
hostname
FROM (--Get all backup alerts that have occurred for all weekdays in a 100 day timespan
SELECT (CASE
WHEN (TO_NUMBER(TO_CHAR(TO_DATE('19700101','YYYYMMDD') + date_reception/86400,'HH24')) >= 0 AND
TO_NUMBER(TO_CHAR(TO_DATE('19700101','YYYYMMDD') + date_reception/86400,'HH24')) < 7)
THEN
TRUNC(TO_DATE('19700101','YYYYMMDD') + date_reception/86400 - 1)
ELSE
TRUNC(TO_DATE('19700101','YYYYMMDD') + date_reception/86400)
END) alert_date,
hostname
FROM tec_t_evt_rep
WHERE class = 'XMPAlert' AND
sub_source = 'Backup Exec' AND
((msg LIKE 'Backup Exec - Insert Tape%' OR msg LIKE 'Backup Exec - Job Cancelled%') OR
(long_msg LIKE 'Backup Exec - Insert Tape%' OR long_msg LIKE 'Backup Exec - Job Cancelled%'))
) c_b_a INNER JOIN (--Get all weekdays that occurred in the last 100 days
SELECT TRUNC(sysdate) - level AS dates
FROM dual
WHERE TO_CHAR(TRUNC(sysdate) - level, 'D') BETWEEN 2 AND 6
CONNECT BY level <= 100) valid_dates ON valid_dates.dates = c_b_a.alert_date
WHERE hostname IN (--Get hostnames that had a backup alert occur on the most recent weekday
SELECT DISTINCT hostname
FROM (--Get only backup alerts and convert their dates from seconds since Jan 1st, 1970 to a date format. Correct applicable date values (alerts that have datetime values between 12:00 AM and 7:00 AM actually occurred the day before)
SELECT (CASE
WHEN (TO_NUMBER(TO_CHAR(TO_DATE('19700101','YYYYMMDD') + date_reception/86400,'HH24')) >= 0 AND
TO_NUMBER(TO_CHAR(TO_DATE('19700101','YYYYMMDD') + date_reception/86400,'HH24')) < 7)
THEN
TRUNC(TO_DATE('19700101','YYYYMMDD') + date_reception/86400 - 1)
ELSE
TRUNC(TO_DATE('19700101','YYYYMMDD') + date_reception/86400)
END) alert_date,
hostname
FROM tec_t_evt_rep
WHERE class = 'XMPAlert' AND
sub_source = 'Backup Exec' AND
((msg LIKE 'Backup Exec - Insert Tape%' OR msg LIKE 'Backup Exec - Job Cancelled%') OR
(long_msg LIKE 'Backup Exec - Insert Tape%' OR long_msg LIKE 'Backup Exec - Job Cancelled%'))
)
WHERE alert_date = (--Get the most recent weekday
SELECT MAX(TRUNC(sysdate) - level)
FROM dual
WHERE TO_CHAR(TRUNC(sysdate) - level, 'D') BETWEEN 2 AND 6
CONNECT BY level <= 3)
)
)
--Get a count of all alerts (only for a count of 3 or more) whose alert_date is greater than the latest date where a break in consecutive dates occurred (in other words, get all most recent consecutive alert occurrences)
SELECT hostname, COUNT(DISTINCT alert_date) AS num_failures
FROM valid_alerts v_a_outermost
WHERE alert_date > (--Get the latest alert_date (other than the most recent weekday) where there exists no records such that that record's alert_date is not consecutively higher than that sought alert_date
SELECT MAX(alert_date) AS max_nonconsec_alert
FROM valid_alerts v_a_outer
WHERE NOT EXISTS (SELECT 'x'
FROM valid_alerts v_a_inner
WHERE v_a_inner.hostname = v_a_outer.hostname AND
(((TO_CHAR(v_a_outer.alert_date, 'D') BETWEEN 2 AND 5) AND
(v_a_inner.alert_date = v_a_outer.alert_date + 1)) OR
((TO_CHAR(v_a_outer.alert_date, 'D') = 6) AND
(v_a_inner.alert_date = v_a_outer.alert_date + 3)))) AND
v_a_outermost.hostname = v_a_outer.hostname AND
v_a_outer.alert_date <> (--Get the most recent weekday
SELECT MAX(TRUNC(sysdate) - level)
FROM dual
WHERE TO_CHAR(TRUNC(sysdate) - level, 'D') BETWEEN 2 AND 6
CONNECT BY level <= 3)
)
GROUP BY hostname
HAVING COUNT(DISTINCT alert_date) >= 3
ORDER BY hostname ASC
------------------------------------------------------
In case you are wondering what may be the number of records we are dealing with, the last time I ran a query against the database there were less than 2400 backup failures (although there were many more failures that are not considered to be "backup failures").
