PQL Retrieving Live Data

Use the 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:

FROM

specify the source of the data such as Prognosis records or PQL online views and tables.

NOTE: Tables are DEPRECATED for Prognosis 10

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 Prognosis terms related to online data collection:

DATABASEspecify a Prognosis database from which data should be retrieved.
OPTIONspecify options that are peculiar to Prognosis 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 Prognosis collectors - it is the same as Top n in IRGUI.

NODE

specify the node(s) from which Prognosis data is to be collected.

ASSOCIATEspecify the associate(s) for which the collector should provide data.
PARAMspecify parameter information that will be passed to the collector.

EVERY

specify the interval at which Prognosis 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 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.

SELECT ProcessName, ProcessCreationTime, BusyPercent, ProcessName FROM NtProcess

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:

ProcessName

ProcessCreationTime

BusyPercent

ProcessName

Idle

2012-07-27T11:44:44.170000+10:00

99.18

Idle

System

2012-07-27T11:44:44.170000+10:00

0.00

System

irpromgr

2012-08-01T13:54:21.560000+10:00

0.00

irpromgr

irnetrtr

2012-08-01T13:54:21.780000+10:00

0.00

irnetrtr

irdllcol1

2012-08-01T13:54:21.990000+10:00

0.00

irdllcol1

iravcol

2012-08-01T13:54:21.990000+10:00

0.00

iravcol

iradicol

2012-08-01T13:54:21.990000+10:00

0.00

iradicol

irgfsrv

2012-08-01T13:54:21.990000+10:00

0.00

irgfsrv

ircmdsrv

2012-08-01T13:54:22.000000+10:00

0.00

ircmdsrv

irdspsrv

2012-08-01T13:54:22.000000+10:00

0.00

irdspsrv

irpqlsrv

2012-08-01T13:54:22.020000+10:00

0.00

irpqlsrv

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 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:

META.NODE

Name of the datasource. This can be an appliance (fake) node, or a real machine where Prognosis is installed.

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:

  • multiple sources (e.g., multiple nodes and/or associates),
  • or the current node,
  • or a virtual appliance monitored by the current node,

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:

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

\DESKTOP

Idle

2012-08-01T16:03:20.000000+10:00

\DESKTOP

System

2012-08-01T16:03:20.000000+10:00

\DESKTOP

irpromgr

2012-08-01T16:03:20.000000+10:00

\DESKTOP

irnetrtr

2012-08-01T16:03:20.000000+10:00

\DESKTOP

irdllcol1

2012-08-01T16:03:20.000000+10:00

\DESKTOP

iravcol

2012-08-01T16:03:20.000000+10:00

\DESKTOP

iradicol

2012-08-01T16:03:20.000000+10:00

\DESKTOP

irgfsrv

2012-08-01T16:03:20.000000+10:00

\DESKTOP

ircmdsrv

2012-08-01T16:03:20.000000+10:00

\DESKTOP

irdspsrv

2012-08-01T16:03:20.000000+10:00

\DESKTOP

irpqlsrv

2012-08-01T16:03:20.000000+10:00

\DESKTOP

irpqlcli

2012-08-01T16:03:20.000000+10:00

Provide feedback on this article