Select statement to retrieve one or more columns of data from a Prognosis record.
The Select Statement
The Select statement is used to retrieve data from Prognosis online 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 Prognosis records or PQL online views and tables.
NOTE: Tables are DEPRECATED for Prognosis 10
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 Prognosis terms related to online data collection:
|DATABASE||specify a Prognosis database from which data should be retrieved.|
|OPTION||specify options that are peculiar to Prognosis 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 Prognosis collectors - it is the same as Top n in IRGUI.
specify the node(s) from which Prognosis 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 Prognosis 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 Prognosis NtProcess record. The desired field name is specified right after the
Select keyword, and the
From keyword specifies the name of the Prognosis record from which to retrieve the data. Prognosis record names, and both long/short names of fields can be used in the select query. The output from this statement is shown below:
Retrieving Multiple Columns
To retrieve multiple fields from a Prognosis 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 Prognosis 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 Prognosis 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 Prognosis Meta Fields
Metadata fields are special fields that are available from records collected by Prognosis. These special fields are only available for rows of data that are directly related to a Prognosis record.
The following meta fields are available:
Name of the datasource. This can be an appliance (fake) node, or a real machine where Prognosis is installed.
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 from Prognosis, rounded to the nearest wall-clock aligned interval boundary.
Prognosis 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