Editing a query

You can edit any of the queries you have created for the monitoring server or for an ODBC-compliant database. You might want to modify a query to add a filter that keeps unnecessary data from cluttering the view and slowing the return of data, for example.

1. Complete one of the following steps to open the Query editor:
   • Click Query Editor.
   • In the view whose query you want to edit, click Edit Properties.
   • Click Properties and select the view from the Properties tree.
2. Expand the Cloud Pak System Software Monitoring Agents and attribute group folders to see the queries.
3. Select the Query that you want to edit.
   The query specification is displayed in the right frame.
4. Edit the specification for any changes you want to make.
   • For monitoring server queries:

     Add an attribute to the query

     Click Add Attributes and select the attributes to include. The attributes available are from the group used in the original query.

     Remove an attribute

     Right-click the column heading and click Delete. You can also right-click a row and delete it.

     Include an attribute in the filter but not retrieve the value

     Clear the check box for that column.

     Delete the filter criteria

     Right-click the cell, column, or row and click Clear Contents.

     Insert a row

     Right-click a row and click Insert.

     Cut, copy, or paste

     Right-click the cell or row and click Cut, Copy, or Paste. When you paste, the contents of the clipboard overwrites the cell or row.

     Add filter criteria

     Click in a cell and write the expression.

     Reorder the columns

     Drag a column heading and drop it at the insertion point. Views that use this query are displayed with this column order. Users can drag columns in the workspace view to rearrange them and, if the workspace is saved the new order becomes permanent until another query is applied to the view or the columns reordered and the workspace is saved again.

     Specify a sort order

     Click Advanced and select the Sort By column from the list. Note that if you selected a Group By column, you cannot also specify a sort order.

     If your environment has multiple monitoring servers, the sorting is done independently at each monitoring server and then returned to the portal without resorting the merged values. You can sort the rows returned after they are displayed in the view.

     Specify the first or last n rows to return

     Click Advanced, select First or Last, then use the spin control to adjust the number of rows to retrieve or type the number directly into the text box.

     This is the number of rows to retrieve from each Cloud Pak System Software Monitoring Server in your monitored environment. For example, if your environment has a hub monitoring server with one remote monitoring server and you select the last 5 rows, the view shows 10 rows of data: 5 from the hub and 5 from the remote. The data is sorted for each server individually. In table views, you can click a column heading to sort the results from all servers.

     Apply a group function

     Click Formula in the column heading and select from the list of available group functions. The icon in the column heading changes to the icon for the chosen function:

     Minimum in group

     Maximum in group

     Count of group members

     Sum of group

     Average of group

     Click Advanced and select the Group By column from the list.

   • For ODBC queries, edit the SELECT statement.
5. Optional. If you opened the Query editor from the view properties, you can change the list of managed systems the query will extract data from.
   1. Click the Query Results Source tab.
   2. Select Let user assign explicitly, then remove managed systems from the Assigned list and add them from the Available list.

   Regardless of the managed systems assigned to a query, a gauge chart can show data from only one managed system. If you are using the query in a workspace where multiple systems are applicable, such as at the Enterprise- or Platform level of the Navigator Physical view, the data shown is from the first managed system in the Assigned list.
6. When you are finished, click Apply to save the query and keep the window open or click OK to save the query and close the window.
   If you opened the Query editor from the Properties window, clicking OK also selects the query for the current view.