Additional queries

// Api name get_invocation_by_time()
// Call set_time_filter() - specify the split time , options - month/week/day/hour ; Default : day
// set_transformation_filter(include='pegasus::dirmanager') - specify the comma separated list of transformation to include  Default : all (In all case no need to add the filter condition 'and transformation in')
// set_transformation_filter(exclude=['dagman::post' , 'dagman::pre' ,'condor::dagman'])- specify the comma separated list of transformation to exclude  Default : None (In all case no need to add the filter condition 'and transformation not in')
// per month // 1 month (30.44 days)
select
(start_time/ 2629743) as date_format,
count(invocation_id) as count,
sum(remote_duration) as total_runtime
from
invocation as i,
workflow as wf
where
wf.root_wf_id = 1
and i.wf_id = wf.wf_id
and transformation in('pegasus::dirmanager')
and transformation not in ('dagman::post' , 'dagman::pre' ,'condor::dagman' )
group by date_format
order by date_format

// per week // Only changes is in the date format selection
(start_time/604800) as date_format,

// per day
(start_time/86400) as date_format,

// per hour
(start_time/3600) as date_format,

// Api name get_jobs_run_by_time(date_filter='day' )
// Call set_time_filter() - specify the split time , options - month/week/day/hour ; Default : day
// set_transformation_filter(include='pegasus::dirmanager') - specify the comma separated list of transformation to include  Default : all (In all case no need to add the filter condition 'and transformation in')
// set_transformation_filter(exclude=['dagman::post' , 'dagman::pre' ,'condor::dagman'])- specify the comma separated list of transformation to exclude  Default : None (In all case no need to add the filter condition 'and  transformation not in')

// per month // 1 month (30.44 days)

select
(js.timestamp/ 2629743) as date_format,count(ji.job_instance_id) as count,
sum(ji.local_duration) as total_runtime
from
workflow wi,
job j,
job_instance  ji,
jobstate js
where
wi.root_wf_id = 1
and wi.wf_id=j.wf_id
and j.job_id=ji.job_id
and js.job_instance_id = ji.job_instance_id
and js.state = 'EXECUTE'
group by date_format
order by date_format

// per week // Only changes is in the date format selection
(js.timestamp/604800) as date_format,

// per day
(js.timestamp/86400) as date_format,

// per hour
(js.timestamp/3600) as date_format,

// suggested api name get_invocation_by_time_per_host()
// Call set_time_filter() - specify the split time , options - month/week/day/hour ; Default : day
// set_transformation_filter(include='pegasus::dirmanager') - specify the comma separated list of transformation to include  Default : all (In all case no need to add the filter condition 'and transformation in')
// set_transformation_filter(exclude=['dagman::post' , 'dagman::pre' ,'condor::dagman'])- specify the comma separated list of transformation to exclude  Default : None (In all case no need to add the filter condition 'and  transformation not in')
//set_host_filter() - specify the comma separated list of hosts Default: 'all'(In all case no need to add the filter 'h.hostname in')

// per month // 1 month (30.44 days)
select
(start_time/ 2629743) as date_format,h.hostname as host_name,
count(invocation_id) as count,
sum(remote_duration) as total_runtime
from
invocation as i,
workflow as wf,
host as h,
job_instance as ji
where
wf.root_wf_id = 1
and i.wf_id = wf.wf_id
and ji.job_instance_id = i.job_instance_id
and ji.host_id = h.host_id
and h.hostname in ('butterfly.isi.edu')
and transformation not in ('dagman::post' , 'dagman::pre' ,'condor::dagman' )
group by date_format,host_name
order by date_format

// per week // Only changes is in the date format selection
(start_time/604800) as date_format,

// per day
(start_time/86400) as date_format,

// per hour
(start_time/3600) as date_format,

// Api name get_jobs_run_by_time_per_host()
// Call set_time_filter() - specify the split time , options - month/week/day/hour ; Default : day
// set_transformation_filter(include='pegasus::dirmanager') - specify the comma separated list of transformation to include  Default : all (In all case no need to add the filter condition 'and transformation in')
// set_transformation_filter(exclude=['dagman::post' , 'dagman::pre' ,'condor::dagman'])- specify the comma separated list of transformation to exclude  Default : None (In all case no need to add the filter condition 'and  transformation not in')
//set_host_filter() - specify the comma separated list of hosts Default: 'all'(In all case no need to add the filter 'h.hostname in')

// per month // 1 month (30.44 days)
select
(js.timestamp/ 2629743) as date_format,
h.hostname as host_name,
count(ji.job_instance_id) as count,
sum(ji.local_duration) as total_runtime
from
workflow wi,
job j,
job_instance  ji,
jobstate js,
host h
where
wi.root_wf_id = 1
and wi.wf_id=j.wf_id
and j.job_id=ji.job_id
and js.job_instance_id = ji.job_instance_id
and j.type_desc in ('compute')
and j.type_desc not in ('dax','dag')
and js.state = 'EXECUTE'
and h.host_id = ji.host_id
and h.hostname in ('butterfly.isi.edu')
group by date_format , host_name
order by date_format