Cisco Workload Automation (CWA) Metadata Queries

-- Data warehouse group dependencies - VQA
SELECT
    jdp.jobdep_id,
    jm1.jobmst_id,
    jm1.jobmst_name "JOB_NAME/GROUP_NAME",
    jm2.jobmst_name JOB_DEPENDS_ON_JOB,
    '"' + jm2.jobmst_name + '"->"' + jm1.jobmst_name + '";' FOR_GRAPHVIZ,
    jm1.jobmst_prntname JOB_FULL_PATH,
    REVERSE(SUBSTRING(REVERSE(jm1.jobmst_prntname), 0, PATINDEX('%\%',REVERSE(jm1.jobmst_prntname)))) JOB_GROUP_NAME,
    SUBSTRING (jd.jobdtl_cmd,
               PATINDEX ('%@%',
                         jd.jobdtl_cmd) + 1,
               200) AS PROJECT_FOLDER,
    SUBSTRING (jd.jobdtl_cmd,
               0,
               PATINDEX ('%@%',
                         jd.jobdtl_cmd)) AS WORKFLOW
FROM
    dbo.jobmst jm1
    LEFT JOIN
    dbo.jobdep jdp
    ON jm1.jobmst_id = jdp.jobmst_id
    LEFT JOIN
    dbo.jobmst jm2
    ON jm2.jobmst_id = jdp.jobdep_jobmst
    left join
    dbo.jobdtl jd
    ON jd.jobdtl_id = jm1.jobmst_id
WHERE jm1.jobmst_active = 'Y' AND
    (jm1.jobmst_prntname LIKE '%VQA - Warehouse US Group%' )
    -- and jd.servicemst_id is not null
-- Data warehouse group dependencies
SELECT
    jdp.jobdep_id,
    jm1.jobmst_id,
    jm1.jobmst_name "JOB_NAME/GROUP_NAME",
    jm2.jobmst_name JOB_DEPENDS_ON_JOB,
    '"' + jm2.jobmst_name + '"->"' + jm1.jobmst_name + '";' FOR_GRAPHVIZ,
    jm1.jobmst_prntname JOB_FULL_PATH,
    REVERSE(SUBSTRING(REVERSE(jm1.jobmst_prntname), 0, PATINDEX('%\%',REVERSE(jm1.jobmst_prntname)))) JOB_GROUP_NAME,
    SUBSTRING (jd.jobdtl_cmd,
               PATINDEX ('%@%',
                         jd.jobdtl_cmd) + 1,
               200) AS PROJECT_FOLDER,
    SUBSTRING (jd.jobdtl_cmd,
               0,
               PATINDEX ('%@%',
                         jd.jobdtl_cmd)) AS WORKFLOW
FROM
    dbo.jobmst jm1
    LEFT JOIN
    dbo.jobdep jdp
    ON jm1.jobmst_id = jdp.jobmst_id
    LEFT JOIN
    dbo.jobmst jm2
    ON jm2.jobmst_id = jdp.jobdep_jobmst
    left join
    dbo.jobdtl jd
    ON jd.jobdtl_id = jm1.jobmst_id
WHERE jm1.jobmst_active = 'Y' AND
    (jm1.jobmst_prntname LIKE '%Test BCV Data Warehouse Group A%' )
    -- and jd.servicemst_id is not null


-- ABS Job dependencies

SELECT
    jdp.jobdep_id,
    jm1.jobmst_name Job_name,
    jm2.jobmst_name depends_on,
    '"' + jm2.jobmst_name + '"->"' + jm1.jobmst_name + '";',
    jm2.jobmst_prntname
FROM
    dbo.jobdep jdp
    LEFT JOIN
    dbo.jobmst jm1
    ON jm1.jobmst_id = jdp.jobmst_id
    LEFT JOIN
    dbo.jobmst jm2
    ON jm2.jobmst_id = jdp.jobdep_jobmst
WHERE jm1.jobmst_active = 'Y' and
jm1.jobmst_prntname LIKE '%ABS%'