SQL code to summarize NBU SQL Server jobs
-- Author: ernie.bergan@veritas.com
-- Last Modified 2022/08/16
-- Added Schedule level
-- Previously Modified: 2022/05/20
-- Added date ranging logic and additional hidden fields to help guide drilldowns
-- Previously Modified: 2022/04/27
--
-- Summarizes best status achieved for MSSQL policies per user-specified time period.
-- Default grouping is per day, based on job start time.
-- Jobs that don't resolve to a SQL Server DB name are ignored.
-- Order is client_name, instance name, DB name, period (date)
--
WITH jobs AS (
SELECT
server_id,
job_id,
policy_id,
policy_name,
job_type_name,
schedule_name,
schedule_type_name,
summary_status,
vendor_status_name,
client_id,
client_host_name,
nbu_job_id,
parent_nbu_job_id,
start_date
FROM
apt_v_nbu_job_detail
WHERE
policy_type_name LIKE 'MS-SQL%'
AND start_date BETWEEN ${startDate} AND ${endDate} --start_date between sysdate - 90 and sysdate --
AND client_id in (${hosts})
), paths AS (
SELECT
j.server_id,
j.job_id,
j.policy_id,
j.policy_name,
j.job_type_name,
--j.schedule_name,
j.schedule_type_name,
j.summary_status,
j.vendor_status_name,
j.client_id,
j.client_host_name,
dbi.instance_name,
j.nbu_job_id,
j.parent_nbu_job_id,
j.start_date,
jf.job_id job_file_job_id,
jf.pathname
FROM
apt_v_nbu_job_file jf,
jobs j,
apt_v_nbu_policy_db_instance dbi
WHERE
j.job_id = jf.job_id (+)
AND j.policy_id = dbi.policy_id
)
--select * from paths;
, dbname AS (
SELECT
server_id,
job_id,
policy_id,
policy_name,
job_type_name,
--schedule_name,
schedule_type_name,
summary_status,
vendor_status_name,
client_id,
client_host_name,
instance_name,
nbu_job_id,
parent_nbu_job_id,
start_date,
-- pathname,
CASE
WHEN instr(pathname, 'trx') > 0 THEN
TRIM('/' FROM TRIM('.' FROM substr(pathname, instr(pathname, 'trx') + 3, instr(pathname, '~') - 1 - instr(pathname, 'trx') -
2)))
WHEN instr(pathname, 'inc') > 0 THEN
TRIM('/' FROM TRIM('.' FROM substr(pathname, instr(pathname, 'inc') + 3, instr(pathname, '~') - 1 - instr(pathname, 'inc') -
2)))
WHEN instr(pathname, '.md.') > 0 THEN
TRIM('/' FROM TRIM('.' FROM substr(pathname, instr(pathname, '.md.') + 4, instr(pathname, '~') - 1 - instr(pathname, '.md.') -
3)))
WHEN instr(pathname, '/md/') > 0 THEN
TRIM('/' FROM TRIM('.' FROM substr(pathname, instr(pathname, '/md/') + 4, instr(pathname, '~') - 1 - instr(pathname, '/md/') -
3)))
WHEN instr(pathname, '.db.') > 0 THEN
TRIM('/' FROM TRIM('.' FROM substr(pathname, instr(pathname, '.db.') + 4, instr(pathname, '~') - 1 - instr(pathname, '.db.') -
3)))
WHEN instr(pathname, '/db/') > 0 THEN
TRIM('/' FROM TRIM('.' FROM substr(pathname, instr(pathname, '/db/') + 4, instr(pathname, '~') - 1 - instr(pathname, '/db/') -
3)))
WHEN instr(pathname, 'DATABASE="') > 0
AND instr(pathname, 'DATABASE="%24ALL"') = 0 THEN
TRIM('/' FROM TRIM('.' FROM substr(pathname, instr(pathname, 'DATABASE="') + 10, instr(pathname, ';TYPE=') - 1 - instr(
pathname, 'DATABASE="') - 10)))
END db_name
FROM
paths
)
--select * from dbname;
,pretime AS (
SELECT
server_id,
client_id,
client_host_name,
aptstringconcat(DISTINCT policy_id) policy_ids,
aptstringconcat(DISTINCT job_type_name) job_type_names,
aptstringconcat(DISTINCT schedule_type_name) schedule_type_names,
aptstringconcat(DISTINCT policy_name) policy_names,
instance_name,
-- pathname,
db_name,
trunc(start_date, decode('${freeCombo1}', 'Days', 'DD', 'Weeks', 'IW',
'Months', 'MM', 'Quarters', 'Q', 'Years')) period,
MIN(summary_status) summary_status,
DECODE(MIN(summary_status),0,'green',1,'yellow',2,'red','white') summary,
max(start_Date) max_start
FROM
dbname
WHERE
db_name IS NOT NULL
GROUP BY
server_id,
client_id,
client_host_name,
--policy_name,
instance_name,
db_name,
trunc(start_date, decode('${freeCombo1}', 'Days', 'DD', 'Weeks', 'IW',
'Months', 'MM', 'Quarters', 'Q', 'Years'))
ORDER BY
period,
client_host_name,
db_name
)
--select * from pretime;
,t1 AS (
select distinct period
from pretime order by 1
)
--select * from t1;
,t2 AS (
select period,
LEAD(period) ignore nulls over(order by period)-1 as period_end --/(24*60*60)
from t1
)
select DISTINCT p.*, --t2.period_end ,
GREATEST(nvl(t2.period_end,to_date('01-JAN-1960','DD-MON-YYYY'))+1-(1/(24*60*60)),p.max_start) Trueperiod_end
from t2, pretime p
WHERE p.period = t2.period
ORDER BY client_host_name,instance_name,db_name,p.period
