A useful report is "utilisation" of particular systems: what proportion of a given time period where they reserved for. We currently have nothing covering that in our supported reporting queries, we should add it. Here is a query I wrote for a user, in Teiid syntax: SELECT fqdn, CAST(SUM(TIMESTAMPDIFF(SQL_TSI_SECOND, CASE WHEN reservation.start_time < {ts '2014-07-01 00:00:00'} THEN {ts '2014-07-01 00:00:00'} ELSE reservation.start_time END, CASE WHEN reservation.finish_time IS NULL OR reservation.finish_time > {ts '2014-07-10 00:00:00'} THEN {ts '2014-07-10 00:00:00'} ELSE reservation.finish_time END)) AS FLOAT) / TIMESTAMPDIFF(SQL_TSI_SECOND, {ts '2014-07-01 00:00:00'}, {ts '2014-07-10 00:00:00'}) FROM Beaker.reservation INNER JOIN Beaker.system ON reservation.system_id = system.id WHERE reservation.start_time >= {ts '2014-07-01 00:00:00'} AND (reservation.finish_time < {ts '2014-07-10 00:00:00'} OR reservation.finish_time IS NULL) -- add clauses here to select particular systems GROUP BY system.fqdn;
gerrit.beaker-project.org/#/c/3213/
The new reporting query is now available here: https://git.beaker-project.org/cgit/beaker/tree/Server/bkr/server/reporting-queries/machine-utilization.sql?h=develop
verified: steps: 1. Get sql from https://git.beaker-project.org/cgit/beaker/tree/Server/bkr/server/reporting-queries/machine-utilization.sql?h=develop 2. Run the sql on database. The sql can be run successfully. 3. Run the sql in description in teiid. The sql can be run successfully too. Change it to verified.
Beaker 0.18.0 has been released.