ARCoQueries
From GridWiki
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.
Contents |
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 > (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 > (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 > (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 > (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 > (current_timestamp - interval '1 day')
AND
LATEBINDING { jr_variable ; = ; 'license_name' }
GROUP BY
time,
jr_variable
ORDER BY
time;
</sql>
</Query>
