Difference between revisions of "ARCoQueries"

From GridWiki
Jump to: navigation, search
(Undo spam added ing revision 5000 by LindaLopez (talk))
(minor updates)
Line 170: Line 170:
 
     <field dbName="mem_in_MB" reportName="mem_in_MB"/>
 
     <field dbName="mem_in_MB" reportName="mem_in_MB"/>
 
     <sql>SELECT date_trunc('minute', time_start) AS time,  
 
     <sql>SELECT date_trunc('minute', time_start) AS time,  
         hostname,
+
         ho ... \n
        TO_NUMBER(RTRIM(str_value,'M'),999999999999) / 1000000000 AS mem_in_MB
 
        FROM view_host_values
 
        WHERE variable = 'mem_used' and time_start &amp;gt; (current_timestamp - interval '1 hour') 
 
        ORDER BY time
 
</sql>
 
</Query>
 
  
== Latest CPU Utilization ==
+
== Three Qualifications for the New Politician ==
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"?>
+
There are plenty of politicians who genuinely desire to serve their communities and nations with humility and integrity, dedicating their lives to the cultivation of a wisdom that will benefit society at large; sadly, they are a minority.
<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/>
 
        <nowiki><table visible="true" order="0"></nowiki>
 
            <columnWithFormat name="hostname" type="string"/>
 
            <columnWithFormat name="cpu" type="string"/>
 
            <columnWithFormat name="time" type="string"/>
 
        <nowiki></table></nowiki>
 
    </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 ==
+
[[http://goodvillenews.com/Three-Qualifications-for-the-New-Politician-227DZ2.html Three Qualifications for the New Politician]]
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"?>
+
[[http://goodvillenews.com/wk.html GoodvilleNews.com - good, positive news, inspirational stories, articles]]
<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/>
 
        <nowiki><table visible="true" order="0"></nowiki>
 
            <columnWithFormat name="hostname" type="string"/>
 
            <columnWithFormat name="load" type="string"/>
 
            <columnWithFormat name="time" type="string"/>
 
        <nowiki></table></nowiki>
 
    </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 ==
+
== 12 Lessons To Learn From Highly Successful People ==
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"?>
+
The only work that will ultimately bring any good to any of us is the work of contributing to the healing of the world. Marianne WilliamsonSuccess can mean different things to different people, and I noticed that many of us have some kind of resistance to the word success, even though deep down inside, that is exactly what we all want, to live a successful and meaningful life.
<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/>
 
        <nowiki><table visible="true" order="0"></nowiki>
 
            <columnWithFormat name="hostname" type="string"/>
 
            <columnWithFormat name="memused" type="string"/>
 
            <columnWithFormat name="time" type="string"/>
 
        <nowiki></table></nowiki>
 
    </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 ==
+
[[http://goodvillenews.com/12-Lessons-To-Learn-From-Highly-Successful-People-sn25rw.html 12 Lessons To Learn From Highly Successful People]]
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"?>
+
[[http://goodvillenews.com/wk.html GoodvilleNews.com - good, positive news, inspirational stories, articles]]
<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/>
 
        <nowiki><table/></nowiki>
 
    </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 &amp;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 ==
+
== 9-yr-old Helps Disabled Brother Finish Triathlons ==
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
+
Over the weekend, brothers Cayden and Connor Long joined hundreds of other children as they competed in the first annual New England Kids Triathlon in Cambridge. The boys did not win the event -- they didnt even come close. But that didnt stop them from winning hearts across the Internet. The Long brothers are not your typical triathletes. Six-year-old Cayden has cerebral palsy and can neither walk nor talk. But thanks to the dedication of his older brother, Connor, the young boy has participated in several triathlons. A touching video
        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
+
[[http://goodvillenews.com/9-yr-old-Helps-Disabled-Brother-Finish-Triathlons-mozmuF.html 9-yr-old Helps Disabled Brother Finish Triathlons]]
 
+
 
  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
+
[[http://goodvillenews.com/wk.html GoodvilleNews.com - good, positive news, inspirational stories, articles]]
  <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>
+
== Mr. Happy Man ==
  <view>
+
 
      <description visible="true"/>
+
For six hours each day, Bermudas Johnny Barnes stands at a busy traffic intersection telling all who pass that he loves them. His delight and sincerity are infectious, and the people of the island love him back. His service is a simple reminder of the power of happiness and loving-kindness to change any day for the better
      <parameter/>
+
 
      <sql visible="true"/>
+
[[http://goodvillenews.com/Mr-Happy-Man-VDrbVr.html Mr. Happy Man]]
  </view>
+
 
    <sql>SELECT
+
[[http://goodvillenews.com/wk.html GoodvilleNews.com - good, positive news, inspirational stories, articles]]
            date_format(time, '%Y-%m-%d %H:00:00') AS hour,
+
 
            SUM(success) as success,  
+
== 6 Steps Towards Living a Life Free of Fear and Full of Hope ==
            SUM(failed) as failed,
+
 
            SUM(success + failed) AS total
+
There are two basic motivating forces: fear and love. When we are afraid, we pull back from life. When we are in love, we open to all that life has to offer with passion, excitement, and acceptance. We need to learn to love ourselves first, in all our glory and our imperfections. If we cannot love ourselves, we cannot fully open our ability to love others or our potential to create. Evolution and all hopes for a better world rest in the fearlessness and open-hearted vision of people who embrace life. ~ John Lennon
        FROM
+
 
            base_for_jobs_count
+
[[http://goodvillenews.com/6-Steps-Towards-Living-a-Life-Free-of-Fear-and-Full-of-Hope-dnAp.html 6 Steps Towards Living a Life Free of Fear and Full of Hope]]
        WHERE
+
 
            time &gt; (current_timestamp - interval 24 hour)
+
[[http://goodvillenews.com/wk.html GoodvilleNews.com - good, positive news, inspirational stories, articles]]
        AND
 
            LATEBINDING { owner ; = ; 'user_name' }
 
        GROUP BY
 
            hour
 
  </sql>
 
  </Query>
 

Revision as of 09:44, 17 August 2012

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, 
        ho ... \n

Three Qualifications for the New Politician

There are plenty of politicians who genuinely desire to serve their communities and nations with humility and integrity, dedicating their lives to the cultivation of a wisdom that will benefit society at large; sadly, they are a minority.

[Three Qualifications for the New Politician]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]

12 Lessons To Learn From Highly Successful People

The only work that will ultimately bring any good to any of us is the work of contributing to the healing of the world. Marianne WilliamsonSuccess can mean different things to different people, and I noticed that many of us have some kind of resistance to the word success, even though deep down inside, that is exactly what we all want, to live a successful and meaningful life.

[12 Lessons To Learn From Highly Successful People]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]

9-yr-old Helps Disabled Brother Finish Triathlons

Over the weekend, brothers Cayden and Connor Long joined hundreds of other children as they competed in the first annual New England Kids Triathlon in Cambridge. The boys did not win the event -- they didnt even come close. But that didnt stop them from winning hearts across the Internet. The Long brothers are not your typical triathletes. Six-year-old Cayden has cerebral palsy and can neither walk nor talk. But thanks to the dedication of his older brother, Connor, the young boy has participated in several triathlons. A touching video

[9-yr-old Helps Disabled Brother Finish Triathlons]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]

Mr. Happy Man

For six hours each day, Bermudas Johnny Barnes stands at a busy traffic intersection telling all who pass that he loves them. His delight and sincerity are infectious, and the people of the island love him back. His service is a simple reminder of the power of happiness and loving-kindness to change any day for the better

[Mr. Happy Man]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]

6 Steps Towards Living a Life Free of Fear and Full of Hope

There are two basic motivating forces: fear and love. When we are afraid, we pull back from life. When we are in love, we open to all that life has to offer with passion, excitement, and acceptance. We need to learn to love ourselves first, in all our glory and our imperfections. If we cannot love ourselves, we cannot fully open our ability to love others or our potential to create. Evolution and all hopes for a better world rest in the fearlessness and open-hearted vision of people who embrace life. ~ John Lennon

[6 Steps Towards Living a Life Free of Fear and Full of Hope]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]