Useful SQL for Applications Manager reporting

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

This article contains commonly requested sql for reporting on objects within Applications Manager

Please consider subscribing to this article as it will be continually updated with additional sql in the future.

 

 

Environment

Applications Manager 9.x.x

Resolution

SQL Table of Contents

----------------------------------------------------------------------------------------------------------------------------------

1. Jobs, Process Flows, their active/inactive state, AND has attached notification, in addition to the Notification Description, notification Trigger, Notification text(body), notification attachment type, and email
   
   
2. Job and Process Flow name, description, Job type, and schedule information (Schedule Name, Schedule Unit, Schedule Frequency, Schedule Start Time, Schedule active/inactive state
   
   
3. Process Flows and their Schedules' Start and End date values
   
   
4. Schedule Requestor values for Jobs and Process Flows
   
   
5. Job, Process Flow name, and Schedule name and eligible Days of the Week (e.g. YYYYYYY)

 

List of SQL
----------------------------------------------------------------------------------------------------------------------------------

Sql for a list of Jobs, Process Flows, their active/inactive state, AND has attached notification, in addition to the Notification Description, notification Trigger, Notification text(body), notification attachment type, and email.

SELECT
    a.so_module              AS "Job or Process Flow",
    a.so_active_flag         AS "Active",
    b.aw_notif_name          AS "Notification",
    b.aw_notif_descr         AS "Notification Description",
    c.aw_notif_trig          AS "Notification Trigger",
    c.aw_notify_text         AS "Notification Test",
    c.aw_notif_attach_flag   AS "Attachment type",
    c.aw_notif_email         AS "Email"
FROM
    so_job_table a
    INNER JOIN aw_notif b 
        ON a.so_notif_seq = b.aw_notif_seq
    INNER JOIN aw_notif_detail c 
        ON b.aw_notif_seq = c.aw_notif_seq;

 

Sql query for Job and Process Flow name, description, Job type, and schedule information (Schedule Name, Schedule Unit, Schedule Frequency, Schedule Start Time, Schedule active/inactive state.

SELECT
    a.so_module           AS "Job Name",
    a.so_job_descr        AS "Job Description",
    a.so_command_type     AS "Job Type",
    b.aw_sch_name         AS "Schedule Name",
    CASE b.aw_sch_units
        WHEN -6 THEN 'Workday'
        WHEN -5 THEN 'Minutes'
        WHEN -4 THEN 'Hourly'
        WHEN -3 THEN 'Daily'
        WHEN -2 THEN 'Weekly'
        WHEN -1 THEN 'Monthly'
        ELSE h.so_holiday_name
    END                   AS "Schedule Units",
    b.aw_sch_interval     AS "Schedule Frequency",
    TO_CHAR(
        TRUNC(SYSDATE) + (b.aw_start_time / (24 * 60 * 60)), 
        'HH24:MI'
    )                     AS "Schedule Start Time",
    b.aw_active           AS "Schedule active/inactive"
FROM
    so_job_table a
    INNER JOIN aw_module_sched b 
        ON a.so_job_seq = b.aw_job_seq
    LEFT JOIN so_holiday_grps h 
        ON b.aw_sch_units = h.so_holiday_grp 
        AND b.aw_sch_units > 0
ORDER BY
    a.so_module ASC;

 

Sql for a list of Process Flows and their Schedules' Start and End date values

SELECT
    a.so_module      AS "Process Flow",
    b.aw_sch_start   AS "Start Date",
    b.aw_sch_end     AS "End Date"
FROM
    so_job_table a
    INNER JOIN aw_module_sched b 
        ON a.so_job_seq = b.aw_job_seq
WHERE
    a.so_command_type = 'CHAIN';

 

Schedule Requestor values for Jobs and Process Flows

SELECT
    a.so_module       AS "Job/Process Flow",
    b.aw_sch_name     AS "Schedule Name",
    c.so_user_name    AS "Requestor",
    b.aw_active       AS "Active/Inactive"
FROM
    so_job_table a
    INNER JOIN aw_module_sched b 
        ON a.so_job_seq = b.aw_job_seq
    INNER JOIN so_user_table c 
        ON b.aw_user_seq = c.so_user_seq
ORDER BY
    c.so_user_name ASC;

 

Job, Process Flow name, and Schedule name and eligible Days of the Week (e.g. YYYYYYY)

SELECT
    a.so_module         AS "Job Name",
    a.so_command_type   AS "Job Type",
    b.aw_sch_name       AS "Schedule Name",
    b.aw_days_of_week   AS "Days of Week"
FROM
    so_job_table a
    INNER JOIN aw_module_sched b 
        ON a.so_job_seq = b.aw_job_seq
ORDER BY 
    a.so_module ASC;