ARCoQueries

From GridWiki
Jump to: navigation, search

This should serve as a place where ARCo users can contribute and share their custom ARCo queries. You can share the complete XML format that is used by ARCo, so other users can easily copy it into the ARCo spool directory (default: /var/spool/arco/queries), or just the SQL string. You should include a short description of the query and the database type for which it was created.

Half Hour Average CPU Utilization for the Past Day

This query shows the graphical representation of the average host CPU utilization for the past day. Database syntax: Postgres

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Query version="1" category="Cluster" type="advanced" clusterName="" name="Host CPU half-hour average">
   <description>Show half-hour CPU utilization average for past day</description>
   <view>
       <description visible="true"/>
       <parameter/>
       <sql/>
       <graphic order="0" visible="true">
           <chart seriesType="row" type="line">
               <xaxis>time</xaxis>
               <seriesFromColumns/>
               <seriesFromRow>
                   <label>hostname</label>
                   <value>value</value>
               </seriesFromRow>
           </chart>
       </graphic>
       <pivot/>
       <table order="1" visible="false">
           <columnWithFormat name="hostname" type="string"/>
           <columnWithFormat format="hh:mm:ss" name="time" type="date"/>
           <columnWithFormat format="#0.00" name="value" type="decimal"/>
       </table>
   </view>
   <field dbName="hostname" reportName="hostname"/>
   <field dbName="value" reportName="value"/>
   <field dbName="time" reportName="time"/>
   <sql>SELECT 
   hostname, 
   AVG(num_value) as value, 
   TIMESTAMP WITH TIME ZONE 'epoch' +  (1800 * trunc(extract(epoch from time_start)/1800)) * INTERVAL '1 second' as time 
   FROM 
   view_host_values 
   WHERE
   variable='cpu' 
   AND 
   time_start &gt; (current_timestamp - interval '1 day') GROUP BY 
   time, 
   hostname 
   ORDER BY 
   time;</sql>
</Query>

Host CPU Utilization for the Past Hour

This query shows the graphical representation of the host CPU utilization for the past hour. Database syntax: Postgres


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Query tableName="" version="1" category="Cluster" type="advanced" clusterName="" name="Host CPU Utilization (past hour)">
<description>Host CPU Utilization for the past hour</description>
   <view>
       <description visible="true"/>
       <parameter visible="false"/>
       <sql visible="false"/>
       <graphic legendVisible="true" order="0" visible="true">
           <chart seriesType="row" type="line">
               <xaxis>time</xaxis>
               <seriesFromColumns>
                   <column>mem</column>
               </seriesFromColumns>
               <seriesFromRow>
                   <label>hostname</label>
                   <value>cpu</value>
               </seriesFromRow>
           </chart>
       </graphic>
       <pivot order="1" visible="false">
           <elem pivotType="col" name="hostname" type="string"/>
           <elem pivotType="row" format="hh:mm:ss" name="time" type="date"/>
           <elem pivotType="data" format="#0.00" name="cpu" type="decimal"/>
       </pivot>
       <table/>
   </view>
   <field dbName="time" reportName="time"/>
   <field dbName="hostname" reportName="hostname"/>
   <field dbName="cpu" reportName="cpu"/>
   <sql>SELECT 
    date_trunc('minute', time_start) AS time, 
    hostname, 
    num_value AS cpu FROM 
   view_host_values WHERE 
   variable = 'cpu' AND 
   time_start &gt; (current_timestamp - interval '1 hour') ORDER BY time
  </sql>
</Query>


Host Load Average for the Past Hour

This query shows the graphical representation of the average host load for the past hour. Database syntax: Postgres

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Query tableName="" version="1" category="Cluster" type="advanced" clusterName="" name="Host Load (past hour)">
   <description>Load Average for the past hour</description>
   <view>
       <description visible="true"/>
       <parameter visible="false"/>
       <sql visible="false"/>
       <graphic legendVisible="true" order="0" visible="true">
           <chart seriesType="row" type="line">
               <xaxis>time</xaxis>
               <seriesFromColumns>
                   <column>load</column>
               </seriesFromColumns>
               <seriesFromRow>
                   <label>hostname</label>
                   <value>load</value>
               </seriesFromRow>
           </chart>
       </graphic>
       <pivot/>
      <table/>
   </view>
   <field dbName="time" reportName="time"/>
   <field dbName="hostname" reportName="hostname"/>
   <field dbName="load" reportName="load"/>
   <sql>SELECT 
   date_trunc('minute' , time_start) AS time, 
   hostname, 
   num_value AS load 
FROM 
   view_host_values
WHERE 
    variable = 'np_load_avg' 
AND 
   time_start &gt; (current_timestamp - interval '1 hour') 
ORDER BY 
   time
</sql>
</Query>

Host Memory Usage for the Past Hour

This query shows the graphical representation of the host memory usage for the past hour. Database syntax: Postgres

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Query tableName="" version="1" category="Cluster" type="advanced" clusterName="" name="Host Memory Usage (past hour)">
   <description>Memory usage for the past hour</description>
   <view>
       <description visible="true"/>
       <parameter visible="false"/>
       <sql visible="false"/>
       <graphic legendVisible="true" order="0" visible="true">
           <chart seriesType="row" type="line">
               <xaxis>time</xaxis>
               <seriesFromColumns>
                   <column>mem</column>
               </seriesFromColumns>
               <seriesFromRow>
                   <label>hostname</label>
                   <value>mem_in_MB</value>
               </seriesFromRow>
           </chart>
       </graphic>
       <pivot order="1" visible="false">
           <elem pivotType="row" format="hh:mm:ss" name="time" type="date"/>
           <elem pivotType="col" name="hostname" type="string"/>
           <elem pivotType="data" format="#0" name="mem" type="decimal"/>
       </pivot>
       <table order="2" visible="false">
           <columnWithFormat format="hh:mm:ss" name="time" type="date"/>
           <columnWithFormat format="###,###.##" name="mem" type="decimal"/>
       </table>
   </view>
   <field dbName="time" reportName="time"/>
   <field dbName="hostname" reportName="hostname"/>
   <field dbName="mem_in_MB" reportName="mem_in_MB"/>
   <sql>SELECT date_trunc('minute', time_start) AS time, 
        hostname, 
        TO_NUMBER(RTRIM(str_value,'M'),999999999999) / 1000000000 AS mem_in_MB
        FROM view_host_values 
        WHERE variable = 'mem_used' and time_start &gt; (current_timestamp - interval '1 hour')   
        ORDER BY time
</sql>
</Query>

Latest CPU Utilization

This query shows the graphical representation and the table for the most current CPU Utilization on all exec hosts. Database syntax: Postgres

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Query tableName="" category="Status" clusterName="" name="Latest CPU Utilization" version="1" type="advanced">
   <description>Shows the most current CPU utilization on all exec hosts.</description>
   <view>
       <description visible="true"/>
       <parameter visible="true"/>
       <sql visible="false"/>
       <graphic legendVisible="true" visible="true" order="1">
           <chart seriesType="row" type="bar">
               <xaxis>hostname</xaxis>
               <seriesFromColumns/>
               <seriesFromRow>
                   <label>time</label>
                   <value>cpu</value>
               </seriesFromRow>
           </chart>
       </graphic>
       <pivot/>
       <table visible="true" order="0">
           <columnWithFormat name="hostname" type="string"/>
           <columnWithFormat name="cpu" type="string"/>
           <columnWithFormat name="time" type="string"/>
       </table>
   </view>
   <field reportName="time" dbName="time"/>
   <field reportName="hostname" dbName="hostname"/>
   <field reportName="cpu" dbName="cpu"/>
   <sql>SELECT 
           hv_time_end as time, 
           h_hostname as hostname, 
           hv_dvalue as cpu, 
        FROM 
           sge_host_values, 
           sge_host
        WHERE 
           hv_time_end IN (
              SELECT 
                 MAX(hv_time_end) 
              FROM 
                 sge_host_values 
              WHERE 
                 hv_variable = 'cpu' 
              GROUP BY 
                 hv_parent)
        AND 
           hv_variable = 'cpu'
        AND 
           hv_parent = h_id
</sql>
</Query>

Latest Load

This query shows the graphical representation and the table for the most current load value on all exec hosts. The load value is averaged by the number of processors (np_load_avg). Database syntax: Postgres

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Query tableName="" category="Status" clusterName="" name="Latest Load" version="1" type="advanced">
   <description>Shows the most current load value on all exec hosts. The load value is averaged by the number of processors (np_load_avg).</description>
   <view>
       <description visible="true"/>
       <parameter visible="true"/>
       <sql visible="false"/>
       <graphic legendVisible="true" visible="true" order="1">
           <chart seriesType="row" type="bar">
               <xaxis>hostname</xaxis>
               <seriesFromColumns/>
               <seriesFromRow>
                   <label>time</label>
                   <value>load</value>
               </seriesFromRow>
           </chart>
       </graphic>
       <pivot/>
       <table visible="true" order="0">
           <columnWithFormat name="hostname" type="string"/>
           <columnWithFormat name="load" type="string"/>
           <columnWithFormat name="time" type="string"/>
       </table>
   </view>
   <field reportName="time" dbName="time"/>
   <field reportName="hostname" dbName="hostname"/>
   <field reportName="load" dbName="load"/>
   <sql>SELECT 
           hv_time_end as time, 
           h_hostname as hostname, 
           hv_dvalue as load, 
        FROM 
           sge_host_values, sge_host
        WHERE  
           hv_time_end IN (
              SELECT 
                 MAX(hv_time_end) 
              FROM 
                 sge_host_values 
              WHERE 
                 hv_variable = 'np_load_avg' 
              GROUP BY hv_parent)
        AND hv_variable = 'np_load_avg'
        AND hv_parent = h_id
</sql>
</Query>

Latest Memory Used

This query shows the graphical representation and the table for the most current memory used value on all exec hosts. Database syntax: Postgres

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Query tableName="" category="Status" clusterName="" name="Latest Memory Used" version="1" type="advanced">
   <description>Shows the most current memory used value on all exec hosts.</description>
   <view>
       <description visible="true"/>
       <parameter visible="true"/>
       <sql visible="false"/>
       <graphic legendVisible="true" visible="true" order="1">
           <chart seriesType="row" type="bar">
               <xaxis>hostname</xaxis>
               <seriesFromColumns/>
               <seriesFromRow>
                   <label>time</label>
                   <value>memused</value>
               </seriesFromRow>
           </chart>
       </graphic>
       <pivot/>
       <table visible="true" order="0">
           <columnWithFormat name="hostname" type="string"/>
           <columnWithFormat name="memused" type="string"/>
           <columnWithFormat name="time" type="string"/>
       </table>
   </view>
   <field reportName="time" dbName="time"/>
   <field reportName="hostname" dbName="hostname"/>
   <field reportName="memused" dbName="memused"/>
   <sql>SELECT 
           hv_time_end as time, 
           h_hostname as hostname, 
           hv_dvalue as memused, 
        FROM 
           sge_host_values, sge_host
        WHERE  
           hv_time_end in (
              SELECT 
                 MAX(hv_time_end) 
              FROM
                 sge_host_values 
              WHERE 
                 hv_variable = 'mem_used' 
              GROUP BY hv_parent)
        AND hv_variable = 'mem_used'
        AND hv_parent = h_id
</sql>
</Query>

License Demand

This query shows the graphical representation of the number of license requests per hour, for the past day. This query uses LATEBINDING, which allows you to specify the license name at runtime. Database syntax: Postgres

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Query category="Cluster" clusterName="" name="License Demand" version="1" type="advanced">
   <description>Shows number of license requests per hour, for the past day.</description>
   <view>
       <description visible="true"/>
       <parameter visible="true"/>
       <sql/>
       <graphic visible="true" order="0">
           <chart seriesType="row" type="bar">
               <xaxis>time</xaxis>
               <seriesFromColumns/>
               <seriesFromRow>
                   <label>variable</label>
                   <value>SUM</value>
               </seriesFromRow>
           </chart>
       </graphic>
       <pivot/>
       <table/>
   </view>
   <field reportName="time" dbName="time"/>
   <field reportName="variable" dbName="variable"/>
   <field reportName="SUM" dbName="SUM"/>
   <filter condition="=" startOffset="285" parameter="'license_name'" active="true" lateBinding="true" endOffset="329" name="jr_variable"/>
   <sql>SELECT
           date_trunc('hour', j_submission_time) AS time, 
           jr_variable AS variable, 
           SUM(jr_value::integer) AS SUM
        FROM 
           sge_job 
        LEFT JOIN
           sge_job_request  
        ON 
           j_id = jr_parent 
        WHERE 
           j_submission_time &gt; (current_timestamp - interval '1 day')
        AND 
           LATEBINDING { jr_variable ; = ; 'license_name' } 
        GROUP BY 
           time, 
           jr_variable 
        ORDER BY 
           time;
</sql>
</Query>

Number of completed, failed, total jobs for past 24 Hours

This query shows the tabular representation of how many jobs a particular user has run over last 24 hours, how many have completed successfully and how many failed. The results are grouped by each hour. Database syntax: MySQL. First, create a base view for further querying. The view should look like this:

 CREATE OR REPLACE VIEW base_for_jobs_count AS
       SELECT
              date_format(ju_end_time, '%Y-%m-%d %H:00:00') AS time,
              ju_failed AS failed_code,
              CASE
                WHEN ju_failed != 0 then count(ju_id)
                ELSE 0
              END AS failed,
              CASE
                WHEN ju_failed = 0 then count(ju_id)
                ELSE 0
              END AS success, ju_ar_parent AS ar_parent,
              j_owner AS owner
       FROM sge_job, sge_job_usage
       WHERE j_id = ju_parent
       AND ju_exit_status != -1
       AND j_pe_taskid = 'NONE'
       GROUP BY time, owner, ju_failed, ar_parent

And here is the xml file for ARCo reporting tool. This query uses LATEBINDING, which allows you to specify the user name at runtime

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <Query category="Job" clusterName="" name="Number of completed, failed, total jobs for past 24 Hours" version="1" type="advanced">
  <description>Shows how many jobs a particular user has run over last 24 hours, how many have completed successfully and how many failed.</description>
  <view>
     <description visible="true"/>
     <parameter/>
     <sql visible="true"/>
  </view>
   <sql>SELECT 
           date_format(time, '%Y-%m-%d %H:00:00') AS hour,
           SUM(success) as success, 
           SUM(failed) as failed,
           SUM(success + failed) AS total
        FROM 
           base_for_jobs_count
        WHERE 
           time > (current_timestamp - interval 24 hour) 
        AND 
           LATEBINDING { owner ; = ; 'user_name' } 
        GROUP BY 
           hour
 </sql>
 </Query>