Why So Hard To Do A Report for MSSQL Backups?

Hi Dennis,

Please send an email to mailto:ernie.bergan@cohesity.com and I will send you a pair of reports to evaluate.  For some reason I can't attach them here.

When you get the templates:  Scoring for a requested time period summarizes the best outcome achieved (e.g. if the day had both successes and warnings, the summary will be a success). 

To get the drilldown working on your portal, you’ll probably need to do the following:

Run the drilldown report directly.  It will return no rows.  While on the drilldown report, press Ctrl+ALT+T  .   Note the number in the highlighted example:

Customize the NBU SQL Server DB Summary_v5 template.  Select the formatting tab, click on the Summary row, and then the Advanced button at the bottom of the popup.

Edit the Drilldown, setting the templateID to the template ID of the drilldown.

Ok, here's the result of the joint effort of ZenOfAnalytics and me:

And the Query code that does include my modifications to get the result I need to send out to my MS-SQL DBA Team:

-- Author:  ernie.bergan@veritas.com
-- Last Modified 2022/09/16

-- Configured to be a stand alone template rather than a drilldown target.

--
-- 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,
        finish_date
    FROM
        apt_v_nbu_job_detail
    WHERE
        client_host_name NOT LIKE 'nbunpamas01'
        AND policy_type=15 --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})
        AND job_type NOT IN (107)

), paths AS (
    SELECT
        j.server_id,
        j.job_id,
        j.policy_id,
        j.policy_name,
        j.job_type_name,
        j.schedule_type_name,
        j.summary_status,
        j.vendor_status_name,
        j.client_id,
        j.client_host_name,
        NVL(dbi.instance_name,'none') instance_name,
        j.nbu_job_id,
        j.parent_nbu_job_id,
        j.start_date,
        j.finish_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 ( /*+NO_MERGE(paths)*/
    SELECT 
        server_id,
        client_id,
        client_host_name,
        job_id,
        policy_id,
        policy_name,
        job_type_name,
        --schedule_name,
        schedule_type_name,
        instance_name,
        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,
        -- instance_name,
        nbu_job_id,
        parent_nbu_job_id,
        start_date,
        finish_date,
        summary_status,
        vendor_status_name
    FROM
        paths
)
select 
dbname.* 
FROM dbname
where db_name is not null
ORDER BY client_host_name,start_date,db_name

-- Author:  ernie.bergan@veritas.com
-- Last Modified 2022/09/16

-- Configured to be a stand alone template rather than a drilldown target.

--
-- 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,
        finish_date
    FROM
        apt_v_nbu_job_detail
    WHERE
        client_host_name NOT LIKE 'nbmasterservername'
        AND policy_type=15 --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})
        AND job_type NOT IN (107)

), paths AS (
    SELECT
        j.server_id,
        j.job_id,
        j.policy_id,
        j.policy_name,
        j.job_type_name,
        j.schedule_type_name,
        j.summary_status,
        j.vendor_status_name,
        j.client_id,
        j.client_host_name,
        NVL(dbi.instance_name,'none') instance_name,
        j.nbu_job_id,
        j.parent_nbu_job_id,
        j.start_date,
        j.finish_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 ( /*+NO_MERGE(paths)*/
    SELECT 
        server_id,
        client_id,
        client_host_name,
        job_id,
        policy_id,
        policy_name,
        job_type_name,
        --schedule_name,
        schedule_type_name,
        instance_name,
        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,
        -- instance_name,
        nbu_job_id,
        parent_nbu_job_id,
        start_date,
        finish_date,
        summary_status,
        vendor_status_name
    FROM
        paths
)
select 
dbname.* 
FROM dbname
where db_name is not null
ORDER BY client_host_name,start_date,db_name
----end code query here------

And the result looks like this:

The templates I want to send have been recently update..

ZenOfAnalytics​ are these templates accessible by public?!I I suggest you try to have it available here for other people, who are looking for such templates, as most of us are not aware about analyzing /writing cods which is required with NBA.

ZenOfAnalytics​ any chance to give us some sort of training for the NITA :)

ZenOfAnalytics​ 
is there a way to show the scheduled activity which are finished and show the missed scheduled as well,for example, currently in the template each schedule is showing in a separate row, but let say if I want to get a report for each day or a week, of all SQL backups that happen, which were successful or not successful or did not happen at all (kind of like check mark , or specific color for reach schedule name),
the reason I am asking, we have logs backup happening every 1 hour, it will make it very difficult to spot if any schedule did not happen at all.