Control-M SQL Queries

You may watch the video here 
https://www.youtube.com/watch?v=qrC-JhJqfRg

These queries can also be automated using Control-M database. Run these queries against enterprise manager database. I have tested it on postgres. You may need to change syntax a bit as per database.

1--> To check number of jobs loaded daily

select net_date, jobs from net_report;

2--> To check number of jobs executed today 

SELECT job_mem_name , sched_table as folder, application, start_time, end_time,
case when ended_status=16 then 'Ended OK' else 'Not Ok' end as Status
from runinfo_history
where start_time > Current_Date

3--> To check number of jobs executed between a time period

SELECT job_mem_name , sched_table as folder, application, start_time, end_time,
case when ended_status=16 then 'Ended OK' else 'Not Ok' end as Status
from runinfo_history
where start_time  between '2016-09-01 00:00:00' and '2016-09-10 23:59:59'

4--> To check number of failed jobs today


SELECT job_mem_name , sched_table as folder, application, start_time, end_time,
case when ended_status=16 then 'Ended OK' else 'Not Ok' end as Status
from runinfo_history
where start_time > Current_Date
and ended_status=32

5--> To check number of failed jobs between a time period

SELECT job_mem_name , sched_table as folder, application, start_time, end_time,
case when ended_status=16 then 'Ended OK' else 'Not Ok' end as Status
from runinfo_history
where start_time  between '2016-09-01 00:00:00' and '2016-09-10 23:59:59'
and ended_status=32


6--> To check number of jobs which completed successfully today

SELECT job_mem_name , sched_table as folder, application, start_time, end_time,
case when ended_status=16 then 'Ended OK' else 'Not Ok' end as Status
from runinfo_history
where start_time > Current_Date
and ended_status=16

7--> To check number of jobs which completed successfully between a time period

SELECT job_mem_name , sched_table as folder, application, start_time, end_time,
case when ended_status=16 then 'Ended OK' else 'Not Ok' end as Status
from runinfo_history
where start_time  between '2016-09-01 00:00:00' and '2016-09-10 23:59:59'
and ended_status=16

8--> To check number of jobs which executed on a particular server in a time period

SELECT job_mem_name , sched_table as folder, application, start_time, end_time,
case when ended_status=16 then 'Ended OK' else 'Not Ok' end as Status , node_id as Server
from runinfo_history
where start_time  between '2016-09-01 00:00:00' and '2016-09-10 23:59:59'
and ended_status=16 and node_id='<Server IP or Hostname>'


9--> To check count of jobs which ended ok and not ok today

select 'OK' Status, sum( case when ended_status=16 then 1 else 0 end) as Count from runinfo_history where ended_status=16
and start_time > current_date
group by ended_status

union all

select 'Not OK' Status, sum( case when ended_status=32 then 1 else 0 end) as Count from runinfo_history where ended_status=32
and start_time> current_date
group by ended_status


10--> To check count of success and failed jobs for a particular application folder

select * from
(
select 'OK' Status, sum( case when ended_status=16 then 1 else 0 end) as Count from runinfo_history where ended_status=16
and start_time Between '2016-09-01 00:00:00' and '2016-09-30 23:59:59' and application like '%a%'
group by ended_status

union all

select 'Not OK' Status, sum( case when ended_status=32 then 1 else 0 end) as Count from runinfo_history where ended_status=32
and start_time Between '2016-09-01 00:00:00' and '2016-09-30 23:59:59' and application like '%a%'
group by ended_status
) Table1


11--> To check count of success and failed jobs , application wise

select * from
(
select application, 'OK' Status, sum( case when ended_status=16 then 1 else 0 end) as Count from runinfo_history where ended_status=16
and start_time Between '2016-09-01 00:00:00' and '2016-09-30 23:59:59'
group by application, ended_status

union all

select application, 'Not OK' Status, sum( case when ended_status=32 then 1 else 0 end) as Count from runinfo_history where ended_status=32
and start_time Between '2016-09-01 00:00:00' and '2016-09-30 23:59:59'
group by application, ended_status
) Table1
order by 1