Chapter 5 Accounting and Reporting

This chapter covers the following topics:

• Starting the Accounting and Reporting Console

• Creating and Running Simple Queries

• Creating and Running Advanced Queries

• Latebindings for Advanced Queries

Starting the Accounting and Reporting Console

The accounting and reporting console is installed separately from the N1 Grid Engine 6 software. For details on the installation process, see Chapter 8, Installing the Accounting and Reporting Console, in N1 Grid Engine 6 Installation Guide. In addition, you must enable your grid engine system to collect reporting information. For details on how to enable the collection of reporting data, see Report Statistics (ARCo) in N1 Grid Engine 6 Administration Guide.

How to Start the Accounting and Reporting Console

Steps

1. Start a web browser.

2. Connect to the Sun Web Console by accessing the following URL in your browser:

   In the following example, hostname is the host on which the accounting and reporting software has been installed.

   https://hostname:6789

3. Log in with your UNIX account, and then select N1 Grid Engine 6 ARCo.

   

4. After successful login, select the N1 Grid Engine 6 ARCo application. You are redirected to the Overview page, that shows you a list of predefined ARCo queries.

   

   ---

   Note –

   The direct link to the ARCo application is https://hostname:6789/console/login/Login?redirect_url=%22/reporting/arcomodule/Index%22)

   ---

   If you press the tab labeled Result List, you see any stored Query Results. Clicking on Query List brings you back to the Query List overview.

   

Creating and Running Simple Queries

The query defines the data set that you want to retrieve. You can create simple queries, and the system formulates the SQL query string for you. Or if you know SQL and you want to write the query yourself, you can create advanced queries.

How to Create a Simple Query

Steps

1. Go to Query List and press the New Simple button.

   The following screen appears with three tabs showing common information like the query category and description. This information is optional. The Simple Query tab is the place where you define the query and the View tab where you define the configuration how to display the results of the query.

   

   Clicking on the Simple Query tab displays the Query definition page. The page is divided into:

   • a Table/View dropdown menu you use to choose a database table or view to predefine your query

   • the Field List where all the fields that you are interested in are listed as a row

   • a Filter List for defining filter conditions for your query

   • the Row Limit textbox to restrict the number of result entries for your query.

   

   The single steps how to construct a simple query are outlined as follows.

2. Select a table from the table list.

   

3. Define the fields you want to see.

   

   The Field Function describes the functionality used for the field. The following table lists the supported values of Field Function.

   Function	Description
   VALUE	Use the current value of the field
   SUM	Accumulate the values of the field
   COUNT	Count the number of values of the field
   MIN	Get the minimum value of the field
   MAX	Get the maximum value of the field
   AVG	Get the average value of the field

   • The Field Name is a field in the selected table.

   • The User Defined Name allows the results to display a more meaningful name.

   • Sort allows to define the sorting order for every field if needed.

4. (Optional) Define Filters.

   You must specify at least one field before you can define filters.

   

   • AND/OR is needed for any filter except the first. This is the logical connection to the previous filter condition.

   • The Field Name is the name of the field to be filtered. If a field has a user-defined name, it is shown in the selection list. Otherwise a generated name is shown.

   • The Condition field specifies the operators that are used to filter the values from the database. The following table lists the supported operators.

   Filter	Symbol	Description	Number of Requirements
   Equal	=	The value must equal the Requirement	1
   Not Equal	<>, !=	The value must not equal the Requirement	1
   Less Than	<	The value must be less than the Requirement	1
   Less Than or Equal	<=, ≤	The value must be less or equal the Requirement	1
   Greater Than	>	The value must be greater than the Requirement	1
   Greater Than or Equal	>=, ≥	The value must be greater or equal the Requirement	1
   Null		The value must be null	0
   Not Null		The value must not be null	0
   Between		The value must be in a specified interval	2
   In		The value must be equal to an element of a specified list	1 or more
   Like		The value must contain the given Requirement	1

   The Requirement field contains a value that is used for filtering the values returned by the query. The following list contains some examples of things that might go into the Requirement field.

   1 AND 100

   For a between condition.

   d%

   For a like condition.

   %d%

   For a like condition.

   %d%e%

   For a like condition.

   Wert-1', Wert-2', ... , 'Wert-n

   For an in condition.

5. (Optional) Limit the number of data sets.

   If you want to limit the number of data sets, select the Limit Query To First check box. Then enter the number of data sets you want returned.

   

6. Click Save to save the query.

   

   The following figure displays the Save this Query As screen. Use the Query Name field to give your query a name, and then click Ok.

   

   After you save your query, you return to a modified version of the Simple Query screen.

How to Create a View Configuration

Steps

1. To change the view configuration for a query choose the View tab.

   To create a view for a saved query:

   • choose the query from the Query List on the Overview page

   • press the Edit button

   • click on the View tab.

   The queries current view configuration displays.

2. Declare how you want to view the results of your query.

   You can add three different sections to the view configuration, decide if additional information about the query is shown, and in which order it is shown.

   

   On top there are jumplinks to jump to the corresponding section if it is available. The possible sections are Database Table, Pivot Table and Graphic. The View Configuration section is always visible and allows to switch on the display of the query description that has been entered on the common tab, the filter conditions from the filter list and the resulting SQL statement of the query definition or the content of the SQL tab for advanced queries.

   Pressing Add Database, Add Pivot, Add Graphic adds the corresponding section.

   For some queries, only a subset of the possible view selections are meaningful. For example, if you have only two columns to select from, pivot makes no sense.

   For the Database Table add and choose the columns that you need to display under Name and adjust their Type and Format. The order in which the columns are added will be the order in which the columns are presented. The selections that you make for this report do not affect the filters applied to the data.

   

   For the Pivot Table, add the pivot column, row, and data entries then choose the column Name, Type, and Format. To shift an entry to a different pivot type select it under Pivot Type.

   

   For the Graphic section, you can attach the query data to different chart diagram types. The following chart types are available from the Diagram Type dropdown:

   • Bar Chart,

   • Bar Chart (3D)

   • Bar Chart Stacked

   • Bar Chart Stacked(3d)

   • Pie Chart, Pie Chart 3D

   • Line Chart

   • Line Chart Stacked Line

   There three different diagram types available:

   • Bar

   • Pie

   • Line

   Bar and Pie types can be display with a 3D effect. Bar and Line diagrams can be drawn as stacked diagrams (values on the y-axis summarized).

   

3. Click Save or Save As to save your View configuration to the query.

4. Click Run to run your query.

How to Define Series for Diagrams

There are two ways to define the data series for a diagram.

Steps

1. Series from columns: All column values are added to a series. The name of the series is the column header

2. Series from rows: All column values define the series. The names of the series is defined by the values of the label column. The values of the series are defined by the value column.

---

Example 5–1 The query “Accounting per Department” results in a table with the columns: time, department, and cpu.

---

---

Example 5–2 Displaying the Results- Selecting Configuration

To display the result in a pie chart select the following configuration:

---

---

Example 5–3 Displaying the Results - Pie Charts

The result will be a multiple pie charts

---

---

Example 5–4 Cpu, Io, and Mem Usage Over All Departments

A query summarizes cpu, io, and mem usage over all departments:

---

---

Example 5–5 Displaying the Results - Selecting Configuration

To display the results in a bar char select the following configuration

---

---

Example 5–6 Displaying the Results - Bar Chart

The results will be a bar chart with three bars for each department:

---

How to Run a Simple Query

Step

You can run a simple query that you just created. You can also run a simple query that you previously saved.

• To run a query that you just created, click Run on the Simple Query screen.

  

• To run a query that you previously saved, select the query from the Query List screen , and then click Run.

  

How to Edit a Simple Query

Steps

1. Select a query from the list on the Query List screen

2. Click Edit.

   The selected Simple Query screen displays.

3. Make changes to the Simple Query screen by navigating through the tabs and entering your changes as you would for creating a simple query .

4. Save or run your changed query.

Creating and Running Advanced Queries

You must have previous experience writing SQL queries to use this feature of the accounting and reporting console.

How to Create an Advanced Query

Steps

1. Click New Advanced Query on the Query List screen.

2. Enter your SQL query in the text box.

   This is the only difference compared to the actions available for the Simple Query.

   

3. Save or run your query.

   • To save your query, click Save.

   • To run your query, click Run.

How to Run an Advanced Query

Step

You can run an advances query that you just created. You can also run an advanced query that you previously saved.



• To run a query that you just created, click Run on the Advanced Query screen.

• To run a query that you previously saved, select the query from the Query List screen, and then click Run.

How to Edit an Advanced Query

Steps

1. Select a query from the list on the Query List screen

2. Click Edit.

   A completed version of the Advanced Query screen displays.

3. Make changes to the SQL query.

4. Save or run your changed query.

   • To save your changed query, click Save.

   • To run your changed query, click Run.

Latebindings for Advanced Queries

The syntax for the latebindings in advanced queries is:

   LATEBINDING{ <column>;<operator>;<default value> }

   <column>    name if the latebinding
   <operator>  a SQL operator (e.g. = < > in .. )
   <value>     default value (e.g. 'localhost' )

---

Example 5–7 Latebindings Examples

select hostname from sge_host where LATEBINDING{hostname, like, 'a%'}
select hostname from sge_host where LATEBINDING{hostname, in, ('localhost', 'foo.bar')}

---