Developers

SELECT

Syntax

Use SELECT statements to request complex information from the database. The possible elements of a SELECT statement include:

pql_statement ::= SELECT [ROWS] select_column_list
FROM simple_path | integer
[WHERE boolean_expression]
[ORDER BY sort_expression_list]
[OFFSET integer]
[LIMIT integer]
select_column_list
select_column		 ::=
::=		 select_column [, select_column] *
expression [AS alias] | '*' [AS alias]
simple_path ::= [/] identifier *
sort_expression_list
sort_expression		 ::=
::=		 sort_expression [, sort_expression] *
expression [ASC|DESC]



Description

The elements of a SELECT statement are defined as follows:

SELECT Queries the database, and outputs a result of zero or more sub-trees of data. Use the SELECT clause in conjunction with select_column or select_column_list to indicate what kind of data to return.

The select_column_list expression indicates which data to select, and returns zero or more columns of data. For example, if you use *, as in SELECT * FROM /network/device, then all trees and all sub-trees from the /network/device anchor-point are returned in the query results.

The ROWS option generates results as rows of data instead of trees.

The AS alias option controls the appearance of the results. For example, you could use select b/100 AS "Percentage of B" to give the results a more meaningful name. You can apply an alias to every column you specify in the select_column_list. See Example 1: SELECT.
ROWS Flattens the selected trees or sub-trees into rows of data, which might be easier to read.

SELECT statements return a list of trees that contain every unique sub-tree below the node specified in the statement. This reflects how the data is stored in Paglo, but it can be a little hard to read if you have rich trees with lots of data. Even if you limit the results by replacing the * with something more specific, the results may still be very rich.

When you use the ROWS option, Paglo converts the trees into rows of data, more like a traditional SQL SELECT statement, and determines the columns they contain. This sometimes results in a single tree being returned as multiple rows.

Unlike in SQL, it is possible in PQL for each row to contain a different number of columns, depending on the types of trees that were generated by the SELECT statement. For example, SELECT * from / returns a single row containing the tree that is rooted from /, which means everything in the database. If the same data would be more useful in multiple rows, you can rewrite the query to a variation of SELECT ROWS * from /
See Example 2: ROWS.
FROM Specifies the anchor-point node (tree or sub-tree) that the query is executed against. Unlike in SQL, the PQL FROM clause is required. The FROM clause can be a simple_path or an integer.

A simple_path is one way to represent the anchor-point node. A simple_path is always an absolute path, starting with a slash to represent the root node, and using slashes to separate each subsequent node, if there are any. An example of a simple_path that limits the query to the contents of device nodes would be: /network/device.

Another way to represent the anchor-point node in a query is by specifying the ID number of that node. Every node has a unique ID number which is an integer that you can discover by using #id as a select_column_list modifier. For example, to see a list of ID numbers for all devices, run: SELECT device#id from /network. Scan the resulting list for a specific device, and note its ID number. If, for example, the ID number for the specific device is 493, then you can use 493 to request data about only that device in a query such as this: SELECT * from 493.

Remember that such a specific query filters out all devices but one. In other words, don't call Bill if you want all the men to answer. See Example 1.
WHERE Restricts the search to rows that match the boolean_expression specified. A boolean_expression is a test applied to each node found under the anchor-point specified in the FROM clause. See Example 3: WHERE.
ORDER BY Sorts the results of a query in ascending or descending order by using ASC or DESC. Sorting applies to rows only, not to branches within trees, so it is useful to use the ROWS clause to flatten trees to rows so they can be sorted. Because sorting happens after the ROWS clause is applied, paths in the ORDER BY clause must be specified relative to the resulting rows, rather than relative to the original tree. See Example 5: ORDER BY.
LIMIT Restricts the number of results returned from a PQL query. For example, run SELECT ROWS * FROM /network/device LIMIT 2 to limit the results to show the first 2 rows only. If you want to show results 3, 4, and 5 only, use OFFSET and LIMIT together, such as:
SELECT * FROM /network/device OFFSET 2 LIMIT 3

The reserved word LIMIT can also be used as a named argument in PQL functions, such as:
SELECT raw_history(a, '1 day ago', 'now', limit => 5) from / See Example 6: LIMIT.
OFFSET Specifies how many rows or sub-trees to skip at the beginning of the result set. For example, if you have 50 devices, and you want to see data about the first 10 of them, you can use the LIMIT option like this: SELECT * FROM /network/device LIMIT 10. If you want to see data about the next 10 of them — from device 11 to device 20 — use OFFSET and LIMIT together, such as: SELECT * FROM /network/device OFFSET 10 LIMIT 10. See Example 6: OFFSET.

Examples


Example 1: SELECT FROM

This query selects all the information in the database (in other words, the entire tree):

SELECT * FROM /

The SELECT clause describes the data that you want to see in the results. The star * as a select_column modifier behaves similarly to a wildcard. It pulls all the information about the node defined in the FROM clause. The FROM clause describes the anchor-point node by its simple_path. The path starts with a / slash to indicate the root of the tree. There are no limitations or restrictions imposed by a WHERE clause.

When this query is run against the sample database, it displays the entire database in the results:

row 
  * 
    network 
      device 
        system 
          name 5627 
          computed_vendor LANIER 
          computed_model 5627 
          computed_class printer 
          computed_score 115 
        os 
          version LANIER 5627 5.20 
        interface 
          name eth0 
          mac_address 01:01:02:03:04:05 
          in_octets 1995335536 
          out_octets 248342455 
          oper_status 1 
      device 
        system 
          name ESMITH 
          computed_vendor Intel 
          computed_class server 
          computed_score 10 
        os 
          version Linux 
        interface 
          name eth0 
          mac_address 02:A1:A2:A3:A4:A4 
          in_octets 1229592351 
          out_octets 1472928781 
          oper_status 1 
        interface 
          nameeth1 
          mac_address 03:A1:A2:A3:A4:A5 
          in_octets 0 
          out_octets 0 
          oper_status 0 
      device 
        system 
          name AJONES 
          computed_vendor Intel 
          computed_class server 
          computed_score 10 
        os 
          version Linux 
        interface 
          name eth0 
          mac_address 04:A1:A2:A3:A4:A8 
          in_octets 1223098455 
          out_octets 1523093749 
          oper_status 1 
        interface 
          name eth1 
          mac_address 05:A1:A2:A3:A4:A9 
          in_octets 0 
          out_octets 0 
          oper_status 0 
      device 
        system 
          computed_model Ethernet Switch 
          computed_class switch 
          computed_score 25 
        os 
          version Ethernet Switch 
        interface 
          name eth0 
          mac_address 06:13:72:F3:0A:F5 
          in_octets 0 
          out_octets 0 
          oper_status 2 
        interface 
          name eth1 
          mac_address 07:13:72:F3:0A:F6 
          in_octets 0 
          out_octets 0 
          oper_status 2 
      device 
        system 
          name KRYPTON 
          computed_model Windows Workstation 
          computed_class workstation 
          computed_score 125 
        os 
          version Darwin Kernel Version 9.2.2 
        interface 
          name eth0 
          mac_address 08:16:CB:FF:FE:66 
          in_octets 0 
          out_octets 346 
          oper_status 1 
        interface 
          name eth1 
          mac_address 09:16:CB:FF:FE:67 
          in_octets 0 
          out_octets 0 
          oper_status 2 
        interface 
          name eth2 
          mac_address 10:16:CB:FF:FE:68 
          in_octets 0 
          out_octets 0 
          oper_status 2 
        interface 
          name eth3 
          mac_address 11:16:CB:FF:FE:69 
          in_octets 1598233842 
          out_octets 2360815490 
          oper_status 1 
      device 
        system 
          name NISSINGETTY 
          computed_vendor Juniper Networks 
          computed_model Netscreen Router 
          computed_class router 
          computed_score 60 
        os 
          version NetScreen-5GT 5.1.043a 
        interface 
          name eth0 
          mac_address 12:10:D8:99:2B:C2 
          in_octets 1385354750 
          out_octets 1451321493 
          oper_status 1 
        interface 
          name eth1 
          mac_address 13:10:D8:99:2B:C1 
          in_octets 3312952833 
          out_octets 3239791359 
          oper_status 1

However, the results might be more meaningful in a table format. Example 2 describes how to do that.


Example 2: ROWS

To find all the information in the database about all the devices on the network, and return the results in rows of a table, instead of branches of a tree, use ROWS:

SELECT ROWS *
FROM /network/device

This query is identical to Example 1, with one exception: the ROWS keyword generates the query results in a table format.

Notice that some devices are represented by more than one row. That is each row represents a different interface, and some devices have more than one interface:

row
  system/name 5627
  system/computed_vendor LANIER
  system/computed_model 5627
  system/computed_class printer
  system/computed_score 115
  os/version LANIER 5627 5.20
  interface/name eth0
  interface/mac_address 01:01:02:03:04:05
  interface/in_octets 1995335536
  interface/out_octets 248342455
  interface/oper_status 1
row
  system/name ESMITH
  system/computed_vendor Intel
  system/computed_class server
  system/computed_score 10
  os/version Linux
  interface/name eth0
  interface/mac_address 02:A1:A2:A3:A4:A4
  interface/in_octets 1229592351
  interface/out_octets 1472928781
  interface/oper_status 1
row
  system/name ESMITH
  system/computed_vendor Intel
  system/computed_class server
  system/computed_score 10
  os/version Linux
  interface/name eth1
  interface/mac_address 03:A1:A2:A3:A4:A5
  interface/in_octets 0
  interface/out_octets 0
  interface/oper_status 0
row
  system/name AJONES
  system/computed_vendor Intel
  system/computed_class server
  system/computed_score 10
  os/version Linux
  interface/name eth0
  interface/mac_address 04:A1:A2:A3:A4:A8
  interface/in_octets 1223098455
  interface/out_octets 1523093749
  interface/oper_status 1
row
  system/name AJONES
  system/computed_vendor Intel
  system/computed_class server
  system/computed_score 10
  os/version Linux
  interface/name eth1
  interface/mac_address 05:A1:A2:A3:A4:A9
  interface/in_octets 0
  interface/out_octets 0
  interface/oper_status 0
row
  system/computed_model Ethernet Switch
  system/computed_class switch
  system/computed_score 25
  os/version Ethernet Switch
  interface/name eth0
  interface/mac_address 06:13:72:F3:0A:F5
  interface/in_octets 0
  interface/out_octets 0
  interface/oper_status 2
row
  system/computed_model Ethernet Switch
  system/computed_class switch
  system/computed_score 25
  os/version Ethernet Switch
  interface/name eth1
  interface/mac_address 07:13:72:F3:0A:F6
  interface/in_octets 0
  interface/out_octets 0
  interface/oper_status 2
row
  system/name KRYPTON
  system/computed_model Windows Workstation
  system/computed_class workstation
  system/computed_score 125
  os/version Darwin Kernel Version 9.2.2
  interface/name eth0
  interface/mac_address 08:16:CB:FF:FE:66
  interface/in_octets 0
  interface/out_octets 346
  interface/oper_status 1
row
  system/name KRYPTON
  system/computed_model Windows Workstation
  system/computed_class workstation
  system/computed_score 125
  os/version Darwin Kernel Version 9.2.2
  interface/name eth1
  interface/mac_address 09:16:CB:FF:FE:67
  interface/in_octets 0
  interface/out_octets 0
  interface/oper_status 2
row
  system/name KRYPTON
  system/computed_model Windows Workstation
  system/computed_class workstation
  system/computed_score 125
  os/version Darwin Kernel Version 9.2.2
  interface/name eth2
  interface/mac_address 10:16:CB:FF:FE:68
  interface/in_octets 0
  interface/out_octets 0
  interface/oper_status 2
row
  system/name KRYPTON
  system/computed_model Windows Workstation
  system/computed_class workstation
  system/computed_score 125
  os/version Darwin Kernel Version 9.2.2
  interface/name eth3
  interface/mac_address 11:16:CB:FF:FE:69
  interface/in_octets 1598233842
  interface/out_octets 2360815490
  interface/oper_status 1
row
  system/name NISSINGETTY
  system/computed_vendor Juniper Networks
  system/computed_model Netscreen Router
  system/computed_class router
  system/computed_score 60
  os/version NetScreen-5GT 5.1.043a
  interface/name eth0
  interface/mac_address 12:10:D8:99:2B:C2
  interface/in_octets 1385354750
  interface/out_octets 1451321493
  interface/oper_status 1
row
  system/name NISSINGETTY
  system/computed_vendor Juniper Networks
  system/computed_model Netscreen Router
  system/computed_class router
  system/computed_score 60
  os/version NetScreen-5GT 5.1.043a
  interface/name eth1
  interface/mac_address 13:10:D8:99:2B:C1
  interface/in_octets 3312952833
  interface/out_octets 3239791359
  interface/oper_status 1

Example 3: WHERE

To scan your network and find out which devices have the 'eth0' interface enabled.

SELECT *
FROM /network/device/interface
WHERE name = 'eth0'    
AND oper_status = '1'

As in Example 1, the SELECT * clause finds all sub-nodes of /network/device/interface that match the criteria. The WHERE clause limits the search with a boolean_expression to find only those devices that have an interface named 'eth0'. The AND interface/status = '1' clause attempts to further restrict the query to devices with enabled interfaces by using a second boolean_expression.

When this query is run against the sample database, it returns everything in the database about the interfaces that match the two criteria:

row
  *
    name eth0
    mac_address 01:01:02:03:04:05
    in_octets 1995335536
    out_octets 248342455
    oper_status 1
row
  *
    name eth0
    mac_address 02:A1:A2:A3:A4:A4
    in_octets 1229592351
    out_octets 1472928781
    oper_status 1
row
  *
    name eth0
    mac_address 04:A1:A2:A3:A4:A8
    in_octets 1223098455
    out_octets 1523093749
    oper_status 1
row
  *
    name eth0
    mac_address 08:16:CB:FF:FE:66
    in_octets 0
    out_octets 346
    oper_status 1
row
  *
    name eth0
    . . .

Example 4: WHERE

Another way to write a query that scans your network to find devices that match both criteria: an interface named 'eth0' where 'eth0' is enabled:

SELECT *
FROM /network/device
WHERE interface/(name = 'eth0'    
AND oper_status = '1')

This query is similar to Example 3 but with an important difference: In the WHERE clause, the two boolean_expressions are grouped inside a pair of () brackets:

The results show the difference:

row
  *
    system
      name 5627
      computed_vendor LANIER
      computed_model 5627
      computed_class printer
      computed_score 115
    os
      version LANIER 5627 5.20
    interface
      name eth0
      mac_address 01:01:02:03:04:05
      in_octets 1995335536
      out_octets 248342455
      oper_status 1
row
  *
    system
      name ESMITH
      computed_vendor Intel
      computed_class server
      computed_score 10
    os
      version Linux
    interface
      name eth0
      mac_address 02:A1:A2:A3:A4:A4
      in_octets 1229592351
      out_octets 1472928781
      oper_status 1
    interface
      name eth1
      mac_address 03:A1:A2:A3:A4:A5
      in_octets 0
      out_octets 0
      oper_status 0
row
  *
    system
      name AJONES
    . . .

Example 5: ORDER BY

To find all the information in the database about all the devices on the network:

SELECT mac_address
FROM /network/device/interface
ORDER BY ip_address ASC

This query requests a list of IP addresses from the node defined in the FROM clause. The ORDER BY clause directs Paglo to sort the resulting list and the ASC keyword directs Paglo to display the sorted data in ascending order.

When this query is run against the sample database, it produces a sorted list of all known interfaces, in ascending order by ip_address. The interfaces that do not have a known IP address are listed as null.

row
  mac_address 01:01:02:03:04:05
row
  mac_address 02:A1:A2:A3:A4:A4
row
  mac_address 03:A1:A2:A3:A4:A5
row
  mac_address 04:A1:A2:A3:A4:A8
row
  mac_address 05:A1:A2:A3:A4:A9
row
  mac_address 06:13:72:F3:0A:F5
row
  mac_address 07:13:72:F3:0A:F6
row
  mac_address 08:16:CB:FF:FE:66
row
  mac_address 09:16:CB:FF:FE:67
row
  mac_address 10:16:CB:FF:FE:68
row
  mac_address 11:16:CB:FF:FE:69
row
  mac_address 12:10:D8:99:2B:C2
row
  mac_address 13:10:D8:99:2B:C1

Example 6: LIMIT and OFFSET

This example queries the database to find all the data about all devices on the network, but to limit the results to one row:

SELECT *
FROM /network/device LIMIT 1

When this query is run against the sample database, the results provide one row of data about the first device under the /network node:

row
  *
    system
      name 5627
      computed_vendor LANIER
      computed_model 5627
      computed_class printer
      computed_score 115
    os
      version LANIER 5627 5.20
    interface
      name eth0
      mac_address 01:01:02:03:04:05
      in_octets 1995335536
      out_octets 248342455
      oper_status 1

This next query uses the * star to pull all the information about the network node defined in the FROM clause. The OFFSET keyword directs Paglo to skip the first and produce data about the second device. The LIMIT keyword directs Paglo to display only one row of data. There are no limitations or restrictions imposed by a WHERE clause.

SELECT *
FROM /network/device OFFSET 1 LIMIT 1

The OFFSET keyword directs Paglo to skip the first device and produce data about the second device. The LIMIT keyword directs Paglo to display only one row of data.

When this query is run against the sample database, it produces the following results:

row
  *
    system
      name ESMITH
      computed_vendor Intel
      computed_class server
      computed_score 10
    os
      version Linux
    interface
      name eth0
      mac_address 02:A1:A2:A3:A4:A4
      in_octets 1229592351
      out_octets 1472928781
      oper_status 1
    interface
      name eth1
      mac_address 03:A1:A2:A3:A4:A5
      in_octets 0
      out_octets 0
      oper_status 0

Example 7: As alias

This example queries the database to find the MAC address of each device in the network, and to return the results without the path, which is easier to read:

SELECT device/interface/mac_address as mac_address
FROM /network

When this query is run against the sample database, it returns a list of trees with one MAC address node:

row
  mac_address
    mac_address 01:01:02:03:04:05
    mac_address 02:A1:A2:A3:A4:A4
    mac_address 03:A1:A2:A3:A4:A5
    mac_address 04:A1:A2:A3:A4:A8
    mac_address 05:A1:A2:A3:A4:A9
    mac_address 06:13:72:F3:0A:F5
    mac_address 07:13:72:F3:0A:F6
    mac_address 08:16:CB:FF:FE:66
    mac_address 09:16:CB:FF:FE:67
    mac_address 10:16:CB:FF:FE:68
    mac_address 11:16:CB:FF:FE:69
    mac_address 12:10:D8:99:2B:C2
    mac_address 13:10:D8:99:2B:C1

Compare the results if you use the same query without the AS mac_address option:

SELECT device/interface/mac_address
FROM /network

Without the AS mac_address option, the results contain the same data, but appear somewhat buried in the longer path name:

row
  device/interface/mac_address
    mac_address 01:01:02:03:04:05
    mac_address 02:A1:A2:A3:A4:A4
    mac_address 03:A1:A2:A3:A4:A5
    mac_address 04:A1:A2:A3:A4:A8
    mac_address 05:A1:A2:A3:A4:A9
    mac_address 06:13:72:F3:0A:F5
    mac_address 07:13:72:F3:0A:F6
    mac_address 08:16:CB:FF:FE:66
    mac_address 09:16:CB:FF:FE:67
    mac_address 10:16:CB:FF:FE:68
    mac_address 11:16:CB:FF:FE:69
    mac_address 12:10:D8:99:2B:C2
    mac_address 13:10:D8:99:2B:C1

Example 8: Top 10 Software Products

This example queries the database to find the top 10 software products installed after January first, 2008, and uses GROUP BY, ORDER BY, DESC, OFFSET, and LIMIT to direct the way the result is displayed:

SELECT name, count(*) 
FROM /network/device/wmi/win32_product 
WHERE installdate > '20080101' 
GROUP BY name ORDER BY 2 DESC OFFSET 1 LIMIT 10

The query finds all software installed since the given date, groups each type together and counts it. It then displays the number of each type of software in descending order by the total count of each, and limits the results to show only the top ten. Depending on the database, this query will produce results that look similar to this:

row
  name Adobe Reader 8.1.2
  count(*) 7
row
  name Windows Media Encoder 9 Series
  count(*) 3
row
  name Microsoft Office Standard 2007
  count(*) 3
row
  name Microsoft .NET Framework 2.0 Service Pack 1
  count(*) 3
row
  name Yahoo! Music Jukebox
  count(*) 2
row
  name Windows Resource Kit Tools
  count(*) 2
row
  name Spelling Dictionaries Support For Adobe Reader 8
  count(*) 2
row
  name Microsoft Office Small Business Edition 2003
  count(*) 2
row
  name Macromedia Flash 8 Video Encoder
  count(*) 2
row
  name Macromedia Flash 8
  count(*) 2 
  . . .

For more examples, see Examples and More examples.



How do I find out more?

Camera
News
Check out the
headlines
Blog_icon_blue
Blog! Blog! Blog!
Straight talk from the
open enterprise