PQL Retrieving Live Data
Use the 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:
FROM | specify the source of the data such as Records or PQL online views and tables. NOTE: Tables are DEPRECATED as of version 10.0 |
|---|---|
WHERE | specify a filter to indicate which rows to retrieve. |
GROUP BY | group rows sharing a property so that an aggregate function can be applied to each group of rows. |
ORDER BY | specify the order in which to return the rows |
| TOP | specify a limit to the number of rows returned by PQL. |
UNION | 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. |
LIMIT | specify a limit to the number of rows returned by collectors - it is the same as Top n in IRGUI. |
NODE | 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. |
EVERY | specify the interval at which data is to be collected. |
KEEP | specify the number of data intervals to be kept by PQL. |
Syntax Diagram
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 Select statement:
SELECT ProcessName FROM NtProcess
Results:
| ProcessName |
|---|
Idle |
taskhost |
dwm |
explorer |
irpromgr |
irnetrtr |
irpqlsrv |
irdllcol |
iravcol |
iradicol |
irfilcol |
irgfsrv |
ircmdsrv |
irdspsrv |
irteasrv |
irnetmon |
irwincol |
irperfutl |
irpqlcli |
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.
SELECT ProcessName, ProcessCreationTime, BusyPercent, ProcessName FROM NtProcess
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:
ProcessName | ProcessCreationTime | BusyPercent | ProcessName |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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:
SELECT * FROM NtProcess
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:
META.NODE | Name of the datasource. This can be an appliance (fake) node, or a real Prognosis Server. |
|---|---|
META.INTERVAL | Start time of interval when the data was collected. |
META.ASSOCIATE | Value of the associate for the record being collected. |
META.SERVERTIME | 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:
Select Meta.Interval, * FROM NtProcess
The * 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 Select query.
Example:
Select Meta.Node, ProcessName, Meta.Interval FROM NtProcess
Results:
Meta.Node | ProcessName | Meta.Interval |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|