Class StampedeStatistics

Overrides: nllog.DoesLogging.__init__

select
(    
    select count(*)  from job as jb where jb.wf_id in (1,2,3) and not 
                (jb.type_desc = 'dax'or jb.type_desc = 'dag')
)
+
(
    select count(*) from
    (
        select jb_inst.job_id from job_instance as jb_inst , job as jb  
        where jb.wf_id in (1,2,3)
        and jb_inst.job_id = jb.job_id
        and (jb.type_desc ='dax' or jb.type_desc ='dag' )
        and jb_inst.subwf_id is  null
        and jb_inst.job_submit_seq  = (
            select max(job_submit_seq) from job_instance where job_id = jb_inst.job_id group by job_id
            )
    )
) as total_jobs

select DISTINCT count(jb.job_id)
    from
    job as jb,
    job_instance as jb_inst,
    jobstate as jb_state
    where  jb.wf_id in(
    1,2,3
    )
    and jb.job_id = jb_inst.job_id
    and not (jb.type_desc ='dax' or jb.type_desc ='dag')
    and jb_inst.job_instance_id = jb_state.job_instance_id
    and jb_state.state ='JOB_SUCCESS'

select count(*) from
(
    select jb_inst.job_instance_id
    from job as jb, job_instance as jb_inst , jobstate as jb_state
    where jb_inst.job_submit_seq  = (
        select max(job_submit_seq) from job_instance where job_id = jb_inst.job_id group by job_id
    )
    and jb.wf_id in (1,2,3)    
    and jb.job_id = jb_inst.job_id
    and jb_inst.job_instance_id = jb_state.job_instance_id
    and (
              (not (jb.type_desc ='dax' or jb.type_desc ='dag'))
        or
              ((jb.type_desc ='dax' or jb.type_desc ='dag') and jb_inst.subwf_id is NULL)
             )
    and jb_state.state in ('JOB_FAILURE')
)

select count(*) from job_instance jb_inst , job as jb
where jb_inst.job_submit_seq  = (
        select max(job_submit_seq) from job_instance where job_id = jb_inst.job_id group by job_id
    )
and jb_inst.job_instance_id in
    (
    select js.job_instance_id from jobstate as js
    where js.job_instance_id = jb_inst.job_instance_id
    and js.state = 'SUBMIT'
    )
and jb_inst.job_instance_id not in
    (
    select js.job_instance_id from jobstate as js
    where js.job_instance_id = jb_inst.job_instance_id
    and js.state in ( 'JOB_SUCCESS', 'JOB_FAILURE')
)
and jb_inst.job_id  = jb.job_id
and jb.wf_id in (
        1,2,3
)
and not (jb.type_desc ='dax' or jb.type_desc ='dag' )

select count(*) from task where wf_id in (
    1,2,3
   )

select count(*)  as total_jobs
from
job_instance as jb_inst ,
job as jb
where
jb_inst.job_id  = jb.job_id
and jb.wf_id in (
    1,2,3
)  
and (
      (not (jb.type_desc ='dax' or jb.type_desc ='dag'))
    or
      ((jb.type_desc ='dax' or jb.type_desc ='dag') and jb_inst.subwf_id is NULL)
   )

select DISTINCT count(jb.job_id)
    from
    job as jb,
    job_instance as jb_inst,
    jobstate as jb_state
    where  jb.wf_id in(
    1,2,3
    )
    and jb.job_id = jb_inst.job_id
    and not (jb.type_desc ='dax' or jb.type_desc ='dag')
    and jb_inst.job_instance_id = jb_state.job_instance_id
    and jb_state.state ='JOB_SUCCESS'

select count(*) as job_failure
from
jobstate as jb_state ,
job_instance jb_inst,
job as jb
where  jb.wf_id in(
    1,2,3
      )
and jb_state.job_instance_id = jb_inst.job_instance_id
and jb.job_id = jb_inst.job_id
and jb_state.state = 'JOB_FAILURE'
and (
      (not (jb.type_desc ='dax' or jb.type_desc ='dag'))
    or
      ((jb.type_desc ='dax' or jb.type_desc ='dag') and jb_inst.subwf_id is NULL)
   )

select count(*) from invocation as invoc where invoc.task_submit_seq >=0 and invoc.wf_id in  (
      1,2,3
 )

select count(*) as succeeded_tasks
from
invocation as invoc
where
invoc.wf_id in  (
    1,2,3
)
and invoc.exitcode = 0
and invoc.task_submit_seq >=0

select count(*) as failed_tasks
from
invocation as invoc
where
invoc.wf_id in  (
    1,2,3
)
and invoc.exitcode <> 0
and invoc.task_submit_seq >=0

select ws.wf_id,
sum(case when (ws.state == 'WORKFLOW_TERMINATED') then ws.timestamp end)
-
sum (case when (ws.state == 'WORKFLOW_STARTED') then ws.timestamp end) as duration
from workflowstate ws
group by ws.wf_id

select sum(remote_duration) from invocation as invoc 
   where  invoc.task_submit_seq >=0 and invoc.wf_id in(
      1,2,3
   )

select sum(local_duration) from job_instance as jb_inst , job as jb where
jb_inst.job_id  = jb.job_id
and jb.wf_id in (
    1,2,3
)
and (
      (not (jb.type_desc ='dax' or jb.type_desc ='dag'))
    or
      ((jb.type_desc ='dax' or jb.type_desc ='dag') and jb_inst.subwf_id is NULL)
   )
   

select jb.job_id, jb.exec_job_id as job_name
 from
 job as jb,
 job_instance as jb_inst
 where
 jb_inst.job_id = jb.job_id
 and jb.wf_id = 3
 group by jb.job_id

Seqexec Delay is Seqexec - Kickstart calculated above.

select jb.job_id,
(
 (
 select sum(jb_inst.cluster_duration)
 from
 job_instance as jb_inst
 where
 jb_inst.job_id = jb.job_id
 group by jb_inst.job_id
 )
-
 (
 select sum(remote_duration)
 from
 invocation as invoc,
 job_instance as jb_inst
 where
 jb_inst.job_id = jb.job_id
 and invoc.wf_id =jb.wf_id
 and invoc.task_submit_seq >=0
 and invoc.job_instance_id = jb_inst.job_instance_id
 group by jb_inst.job_id
 )
) as seqexec_delay
from
job as jb
where jb.wf_id in (1,2,3)
and jb.clustered <>0

select job_id, job_name, sum(cQTime) as condorQTime from
(
select jb.exec_job_id as job_name,  jb.job_id as job_id, jb_inst.job_instance_id ,
    (
        (select min(timestamp) from jobstate where job_instance_id = jb_inst.job_instance_id and (state = 'GRID_SUBMIT' or state = 'GLOBUS_SUBMIT' or state = 'EXECUTE'))
        -
        (select timestamp from jobstate where job_instance_id = jb_inst.job_instance_id and state = 'SUBMIT' )
    )   as cQTime
from
job_instance as jb_inst,
job as jb
where jb_inst.job_id =jb.job_id
and jb.wf_id = 2
)   group by job_id

select job_id, job_name, sum(rTime) as resourceTime from
(
select jb.exec_job_id as job_name,  jb.job_id as job_id, jb_inst.job_instance_id ,
    (
             (select timestamp from jobstate where job_instance_id = jb_inst.job_instance_id and state = 'EXECUTE' )  
        -
            (select timestamp from jobstate where job_instance_id = jb_inst.job_instance_id and (state = 'GRID_SUBMIT' or state ='GLOBUS_SUBMIT'))
    )   as rTime
from
job_instance as jb_inst,
job as jb
where jb_inst.job_id =jb.job_id
and jb.wf_id = 2
)   group by job_id

select jb.exec_job_id as job_name,  
(
  (
   select min(timestamp)
   from jobstate
   where job_instance_id in 
     (select job_instance_id from job_instance as jb_inst where jb_inst.job_id = jb.job_id )  
   and state ='SUBMIT'
  )
  -
  (
   select max(timestamp)
   from jobstate
   where job_instance_id in 
      (select job_instance_id from job_instance as jb_inst where jb_inst.job_id in
        (
          select
          parent.job_id as parent_job_id
          from
          job as parent,
          job as child,
          job_edge as edge
          where
          edge.wf_id = 2
          and parent.wf_id = 2
          and child.wf_id = 2
          and child.job_id = jb.job_id
          and edge.parent_exec_job_id like parent.exec_job_id
          and edge.child_exec_job_id like child.exec_job_id
         )
       )
   and (state = 'POST_SCRIPT_TERMINATED' or state ='JOB_TERMINATED')
  )
)   as dagmanDelay
from
job as jb where
jb.wf_id =2

select job_id, job_name, sum(pTime) as postTime from
(
select jb.exec_job_id as job_name,  jb.job_id as job_id, jb_inst.job_instance_id ,
    (
             (select timestamp from jobstate where job_instance_id = jb_inst.job_instance_id and state = 'POST_SCRIPT_TERMINATED')
        -
            (select max(timestamp) from jobstate  where job_instance_id = jb_inst.job_instance_id  and (state ='POST_SCRIPT_STARTED' or state ='JOB_TERMINATED'))
    )   as pTime
from
job_instance as jb_inst,
job as jb
where jb_inst.job_id =jb.job_id
and jb.wf_id = 2
) group by job_id