# default.conf
# sample config file for nl_dbquery
# This one basically makes use of the database and the queries for the pegasus database
# A similar config file can be generated for any other database also

[DEFAULT]
# put defaults here

# These are the query sections
[how_many_jobs]
   desc = "How many jobs ran on a given day"
   query = "select count(id) from event where <timerange> and name = 'pegasus.invocation';"
       
[cumulative]
   desc = "What was the cumulative runtime of these jobs"
   query = "select sum(value) from attr join event on e_id = id where <timerange> and event.name = 'pegasus.invocation'  and attr.name = 'duration';"

[jobs_on_hosts]
   desc = "How many jobs ran on given hosts" 
   query = "select count(id), value from event join attr on e_id = id where event.name = 'pegasus.invocation' and attr.name = 'host' group by value;"

[jobs_on_day]
   desc = "How many jobs of a given type ran on a given day"	
   query = "select attr.value, count(id) from attr join event on e_id = id where <timerange> and event.name = 'pegasus.invocation' and attr.name = 'type' group by attr.value;" 

[jobs_failed]
   desc = "How many jobs failed"
   query = "select count(id) from attr join event on e_id = id where <timerange> and event.name = 'pegasus.invocation' and attr.name = 'status' and attr.value != '0';"

[jobs_succeeded]
   desc = "How many jobs succeeded"
   query = "select count(id) from attr join event on e_id = id where <timerange> and event.name = 'pegasus.invocation' and attr.name = 'status' and attr.value = '0';"    	

[workflow_comp_time]
    desc = "Total computational time for each workflow"
    query = "select wf.value as workflow, sum(dur.value) as computational_time from attr as dur join ident as wf on wf.e_id = dur.e_id where wf.name = 'workflow' and dur.name = 'duration' group by wf.value;"

[job_status]
desc = "How many jobs had a given status"
query = """select count(id)  from
              attr join event on e_id = id
           where
               <timerange> and event.name = 'pegasus.invocation'
           and attr.name = 'status' and attr.value = '<status>'"""
#query = "select count(id) from attr join event on e_id = id where <timerange> and event.name = 'pegasus.invocation' and attr.name = 'status' and attr.value = '<status>'"

# these are the new queries which are added for cybershake (these begin with cs_).I have added some basic description of these which I would suggest the original
# author should change to something more meaningful

# QUERIES PER WORKFLOW WHERE WORKFLOW ID IS A DAX LABEL

[cs_total_jobs]
    desc = "CyberShake: Total number of jobs"
    query = "select count(attr.e_id) from attr join ident on attr.e_id = ident.e_id where  attr.name = 'status' and ident.name='workflow' and ident.value LIKE 'CyberShake_WNGC%';"
    
[cs_jobs_succeeded]
   desc = "Cybershake: Total number of succeeded jobs"
   query = "select count(attr.e_id) from attr join ident on attr.e_id = ident.e_id where  attr.name = 'status' and attr.value = '0' and ident.name='workflow' and ident.value LIKE 'CyberShake_WNGC%';"
 
[cs_job_breakdown]  
   desc = "Cybershake: breakdown of jobs"
   query = "select attr.value, count(attr.e_id) from attr join ident on attr.e_id = ident.e_id  where  ident.name='workflow' and ident.value LIKE 'CyberShake_WNGC%'  and attr.name='type' group by attr.value;"
 
[cs_total_runtime]   
   desc = "Cybershake: total runtime of the jobs"
   query = "select sum(attr.value) from attr join ident on attr.e_id=ident.e_id where attr.name='duration' and ident.name='workflow' and ident.value LIKE 'CyberShake_WNGC%';"

# QUERIES PER WORKFLOW PER JOB TYPE
# here jt in the query name stands for job type

[cs_runtime_breakdown_jt]
   desc = "Cybershake: Runtime Breakdown by job type per workflow "
   query = "select TRANSFORMATION, count(TRANSFORMATION) as number ,round(sum(attr.value),2) as sum_seconds,  round(sum(attr.value)/(3600),2) as sum_hours, round(avg(attr.value),2) as avg_seconds from attr join (select attr.e_id as event_id, attr.value as TRANSFORMATION from  attr join ident on  attr.e_id=ident.e_id  where attr.name='type' and  ident.name='workflow' and ident.value LIKE 'CyberShake_USC%') ident  on attr.e_id=event_id WHERE attr.name='duration' group by  TRANSFORMATION;" 

[cs_numof_failure_jt]
   desc = "Cybershake: No. of Failures by Job Type"   
   query = "select TRANSFORMATION, count(TRANSFORMATION) as failures from attr join (select attr.e_id as event_id, attr.value as TRANSFORMATION from attr join ident on attr.e_id=ident.e_id  where attr.name='type' and ident.name='workflow' and ident.value LIKE 'CyberShake_USC%') ident on attr.e_id=event_id WHERE attr.name = 'status' and attr.value != '0' group by TRANSFORMATION;"

# QUERIES PER UNIT TIME PER WORKFLOW
# here pd: per day, ph: per hour, pt: per host and pw: per workflow

[cs_jobs_pd_pw]
   desc = "Cybershake: Jobs Per Day Per Workflow"
   query = "select count(id) as 'count', day(from_unixtime(time)) as day from event join attr on attr.e_id = event.id join ident on attr.e_id=ident.e_id where event.name = 'pegasus.invocation' and attr.name = 'host' and ident.name='workflow' and ident.value LIKE 'CyberShake_CCP%' group by day;"

[cs_jobs_ph_pd_pw]   
   desc = "Cybershake: Jobs Per Hour Per Day Per Workflow"
   query = "select count(id) as 'count', hour(from_unixtime(time)) as hour, day(from_unixtime(time)) as day from event join attr on attr.e_id = event.id join ident on attr.e_id=ident.e_id where event.name = 'pegasus.invocation' and attr.name = 'host' and ident.name='workflow' and ident.value LIKE 'CyberShake_CCP%' group by hour, day;"
   
[cs_jobs_pt_ph_pw]
   desc = "Cybershake: Jobs Per Host Per Hour Per Workflow"
   query = "select count(id) as 'count', hour(from_unixtime(time)) as 'hour', attr.value  as value from event join attr on attr.e_id = event.id join ident on attr.e_id=ident.e_id where event.name = 'pegasus.invocation' and attr.name = 'host' and ident.name='workflow' and ident.value LIKE 'CyberShake_<SITE>%' group by value, hour;"

# Add new queries here following the format for the queries above
# If a query needs to be executed within a particular timerange then just 
# enter <timerange> for that query. The actual start and end dates can be entered as a command line
# parameters in the nl_dbquery script file
