psql request which could join gxadmin

[lldelisle]

Hi there, At the imaging meeting we were wondering if we could monitor the usage of a list of tools both in terms of CPU hours and in term of number of different users. I came with this request (which I tested for bowtie2 and cufflinks):

tool_list="('bowtie2', 'cufflinks')"
psql -c "COPY
(SELECT
   date_trunc('month', job.create_time  AT TIME ZONE 'UTC')::date as month,
   round(sum((a.metric_value * b.metric_value) / 3600 ), 2) as cpu_hours,
   regexp_replace(regexp_replace(job.tool_id, '/[0-9.a-z+-]+$', '')::TEXT, '.*toolshed.*/repos/[^/]*/[^/]*/', '') as tool_name,
   COUNT (DISTINCT job.user_id) as nb_users
FROM
   job_metric_numeric a,
   job_metric_numeric b,
   job
WHERE
   b.job_id = a.job_id
   AND a.job_id = job.id
   AND a.metric_name = 'runtime_seconds'
   AND b.metric_name = 'galaxy_slots'
   AND job.create_time < NOW() - interval '1 year'
   AND regexp_replace(regexp_replace(job.tool_id, '/[0-9.a-z+-]+$', '')::TEXT, '.*toolshed.*/repos/[^/]*/[^/]*/', '') in $tool_list
GROUP BY
   month, tool_name
ORDER BY
   month DESC)
TO '$PWD/info_tool.csv' WITH (FORMAT CSV, HEADER) ;"

I am not an expert in psql so this request is not optimal. I don't know if this is interesting to go to gxadmin.

[hexylena]

yes, of course this is interesting! please feel free to make a PR adding it to parts/22-query.sh and I'll help with the bash bits

[lldelisle]

Solved by #142