Understanding queries

Queries are made to the Cloud Pak System Software Monitoring Server to retrieve sampled attribute values from managed systems for display in workspace chart views, the table view and relational table-based topology views. There are benefits and limitations that you must be aware of when creating custom queries to the monitoring server or SQL queries to a JDBC- or ODBC-compliant data source.

Queries to the monitoring server
For queries written to the monitoring server, the Queries editor enables you to add a pre-filter, composed in the same way as a filter for a table or chart view in the Properties editor. Additionally, you can add a column function to average, count, total, or find the minimum or maximum value of a column grouping. The example below shows theCloud Pak System Software Monitoring Server query Open Events Count - Managed System.
FunctionLocal Timestamp FunctionStatus FunctionOrigin Node FunctionSituation Name CountType
1 Collect check box Collect Collect Collect
2   == Open == $NODE$    
3 sub string == 14,000        
4 sub string == 14,999        

For most predefined queries, the specification shows == $NODE$ in the column that identifies the system (such as Server Name or Origin Node). The $NODE$ is a symbol that is replaced by the node name of the agent or agents associated with the Navigator item where the query is applied. This filter criteria is required to limit the retrieved data to only those agents associated with the Navigator item rather than all agents of that type.

You can clear the box below the column heading for any attribute you want to write a filter against, but which you do not want to retrieve. The example above has a filter written on the Status attribute to show only true situations (== Opened), although the attribute itself will not display in the view.

The query in the above example also shows the >4 COUNT column function in the Type column. Although not shown in the query editor, the Situation Name was specified as the GROUP BY column (click Advanced). Cloud Pak System Software Monitoring Portal will keep count of the number of times an event is opened and show the total in the Type column.

Here is an example of the queries for Managed System attributes. If you apply the Current Managed Systems Status query to it, all attributes are retrieved; if you apply the Windows Systems Online or Windows Systems Offline query, only 4 attributes are retrieved. With either query you can use the Filters tab in the Properties editor to display fewer attributes.

Managed System Managing System Status Origin Node Name Product Version
Query Current Managed Systems Status Collect == $AGENT$ Collect Collect Collect Collect Collect
Query Windows Systems Online   Collect  =='*ONLINE'    Collect Collect  =='NT' Collect
QueryWindows Systems Offline    Collect  =='*OFFLINE'    Collect Collect  =='NT' Collect

To determine which queries to use, consider that every attribute you retrieve adds to the processing time and network traffic, as does every query. If possible, use the same query for more than one view in a workspace and create queries that eliminate unwanted attributes or values.

Your Cloud Pak System Software Monitoring Server predefined workspaces use queries to the monitoring server to retrieve attributes from agents. You can also create your own queries with only the attributes you want to see in the view. The attributes can be from one group only; you cannot mix attributes from different groups in the same query.

Queries to JDBC and ODBC data sources
The Query editor has a text editor where you can compose free-form SQL queries to any JDBC- or ODBC-compliant databases located on the system where the Cloud Pak System Software Monitoring Portal server is installed. This gives you the ability to integrate systems management data from monitoring agents with data from other sources, such as a third-party database, in one workspace.
Pre-filters compared with post-filters
Your monitoring product has queries for its attribute groups that send a request to the Cloud Pak System Software Monitoring Server for data from managed systems. With the Query editor, you can create your own queries to the monitoring server and add a pre-filter that is composed in the same way as a post-filter for a table, chart, or relational table based topology view. Additionally, you can add a column function to average, count, total, or find the minimum or maximum value of a column and group the results accordingly.
Advanced options
The Query editor has advanced options for sorting the view results, the attribute to group the rows by when a column function is used, and the number of rows to display from the beginning or end of the list of returned values (such as the top ten processes).