Select statement to retrieve one or more columns of data from a Record.
The Select Statement
The Select statement is used to retrieve data from live collectors or Database Collections, PQL online and table views. It supports the following constructs which are also found in ANSI SQL:
specify the source of the data such as Records or PQL online views and tables.
NOTE: Tables are DEPRECATED as of version 10.0
specify a filter to indicate which rows to retrieve.
group rows sharing a property so that an aggregate function can be applied to each group of rows.
specify the order in which to return the rows
specify a limit to the number of rows returned by PQL.
|combine the results of multiple select queries to create one large dataset.|
PQL introduces a number of non-standard keywords to allow the specification of terms related to online data collection:
|DATABASE||specify a Database Collection from which data should be retrieved.|
|OPTION||specify options that are specific to PQL query requests. For example, you can request collectors for historical records to provide only differences.|
specify a limit to the number of rows returned by collectors - it is the same as Top n in IRGUI.
specify the node(s) from which data is to be collected.
|ASSOCIATE||specify the associate(s) for which the collector should provide data.|
|PARAM||specify parameter information that will be passed to the collector.|
specify the interval at which data is to be collected.
specify the number of data intervals to be kept by PQL.
The diagram below shows all the Select statement clauses and the order in which they should appear.
Retrieving Individual columns
The syntax for a column is as follows
We start with a very simple
The query above uses the
Select statement to retrieve a single field called ProcessName from NtProcess record. The desired field name is specified right after the
Select keyword, and the
From keyword specifies the name of the Record from which to retrieve the data. Long and short names can be used for both Record names, and field names in the select query. The output from this statement is shown below:
Retrieving Multiple Columns
To retrieve multiple fields from a Record, the same
Select statement is used. The only difference is that multiple column names must be specified after the
Select keyword, and each column is separated by a comma.
Just as in the prior example, this statement uses the
Select statement to retrieve data from NtProcess record. In this example, four column names are specified, each separated by a comma. The output from this statement is shown below:
Retrieving All Columns
In addition to being able to specify one or more desired columns (as seen in the previous examples),
Select statements can also request all columns without having to list them individually. This is done using the asterisk
* wildcard character in lieu of actual field names:
When a wildcard is specified, all the fields in the record are returned. The field order will typically be the same as the order they are defined in the Record Definition. Usually, you are better off not using the
* wildcard, unless you really do need every field in the record. Even though the use of wildcards may save you the time and effort needed to list the desired fields explicitly, retrieving unnecessary fields usually slows down the performance of your retrieval and your application, not to mention the consumption of resources such as CPU and memory usage.
There is one big advantage of using
* wildcards. Because you do not explicitly specify field names, it is possible to retrieve columns whose names are unknown.
Retrieving Meta Fields
Metadata fields are special fields that are available from records collected. These special fields are only available for rows of data that are directly related to a Record.
The following meta fields are available:
Name of the datasource. This can be an appliance (fake) node, or a real Prognosis Server.
Start time of interval when the data was collected.
Value of the associate for the record being collected.
For queries that request data from:
the value is the same as META.INTERVAL. Otherwise, the value is the time when PQL Server received the data, rounded to the nearest wall-clock aligned interval boundary.
Meta fields are never automatically included in select queries, even when the wildcard (*) is used; they need to be included in the Select queries explicitly. For example, if you want all fields from the NtProcess record, including the meta.interval meta field you can use the following query:
* wildcard can be used in any order in the list of fields after the Select keyword, but only one
* wildcard can be used in a