Developers

Writing queries using the WHERE clause


Query 1: Using the WHERE clause

To restrict or filter the data that you find, add a WHERE clause, such as this:


  SELECT *
  FROM /network/device
  WHERE interface/name = 'eth1'

This query utilizes the following reserved words and symbols:

SELECT

Describes the data that you want to see.

*

Generates everything known about the device, similar to a wildcard that outputs the entire tree below the anchor-point indicated in the FROM clause.

FROM

Describes the anchor-point — the node in the tree that you want to pull data from.

/

Represents the root node when the slash is at the start of the path.

network/device

Represents the path to the node that you're gathering information about.

WHERE

Restricts or filters the data that you want to see. The query gets data from the FROM anchor-point — in this case, the /network/device sub-tree — and the WHERE clause indicates which sub-branches to include in the result.

When run against Sample database A, this query generates the following results. Note that, although the WHERE clause specified 'eth1', the results included both 'eth0' and 'eth1' interfaces:

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
      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
row
  *
    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
row
  *
    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
row
  *
    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

The WHERE clause specified 'eth1', but the results included both 'eth0' and 'eth1'. Why?

The reason is that the WHERE clause does not modify the tree. It indicates which sub-branches must be included in the result, but does not indicate which must not. The query operates on the FROM anchor-point — in this case, the /network/device sub-tree. In this example, the WHERE clause is inclusive — it includes the 'eth1' sub-branch, but does not exclude any sub-branches. Because both Device 2 and Device 3 possess an eth1 interface, and because the query requests information from /network/device — at the device level — both devices match the search criteria.


Query 2: Filtering by the FROM clause

The previous query found everything about any devices that have interfaces named 'eth1', and even including interfaces that are not named 'eth1'. This query is more useful if you move /interface from the WHERE clause to the FROM clause:


  SELECT *
  FROM /network/device/interface      
  WHERE name = 'eth1'

Based on the Sample database A, this new query finds everything about the 'eth1' interfaces only, and filters out any interfaces that do not match the 'eth1' name:

row
  *
    name eth1
    mac_address 03:A1:A2:A3:A4:A5
    in_octets 0
    out_octets 0
    oper_status 0
row
  *
    name eth1
    mac_address 05:A1:A2:A3:A4:A9
    in_octets 0
    out_octets 0
    oper_status 0
row
  *
    name eth1
    mac_address 07:13:72:F3:0A:F6
    in_octets 0
    out_octets 0
    oper_status 2
row
  *
    name eth1
    mac_address 09:16:CB:FF:FE:67
    in_octets 0
    out_octets 0
    oper_status 2
row
  *
    name eth1
    mac_address 13:10:D8:99:2B:C1
    in_octets 3312952833
    out_octets 3239791359
    oper_status 1

Query 3: Using multiple search criteria

What if you want to find out about only the 'eth0' interfaces that are enabled? Add a second search criteria to specify status:

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

When run against Sample database A, this query generates the following results:

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
    mac_address 12:10:D8:99:2B:C2
    in_octets 1385354750
    out_octets 1451321493
    oper_status 1

Query 4: Using brackets for multiple search criteria

A more elegant way to write the same query is to bracket the search criteria so that the name criteria and the oper_status criteria are correlated. This creates what we call a path-scoped predicate:

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

However, when you run this query against Sample database A, it generates the same results as Query 1. The WHERE clause does not filter the results.


Query 5: Using square brackets in the SELECT clause

Another option for keeping the SELECT and WHERE clauses (the projection and predicate operations) separate is to use the path, like this: 

SELECT interface[name = 'eth0' or oper_status = '1']/mac_address   
FROM /network/device
WHERE interface/(name = 'eth0' or oper_status = '1')

The selection of branches of the tree is based on the WHERE clause. If you run this query against the Sample database A, it generates the following results: 

row
  interface[name = eth0 or oper_status = 1]/mac_address 01:01:02:03:04:05
row
  interface[name = eth0 or oper_status = 1]/mac_address 02:A1:A2:A3:A4:A4
row
  interface[name = eth0 or oper_status = 1]/mac_address 04:A1:A2:A3:A4:A8
row
  interface[name = eth0 or oper_status = 1]/mac_address 06:13:72:F3:0A:F5
row
  interface[name = eth0 or oper_status = 1]/mac_address
    mac_address 08:16:CB:FF:FE:66
    mac_address 11:16:CB:FF:FE:69
row
  interface[name = eth0 or oper_status = 1]/mac_address
    mac_address 12:10:D8:99:2B:C2
    mac_address 13:10:D8:99:2B:C1

Query 6: Using square brackets in the WHERE clause

You can use the same syntax in the WHERE clause, like this: 


  SELECT *
  FROM /network/device
  WHERE interface[name = 'eth2']/mac_address = '10:16:CB:FF:FE:68'

In this example, the specific branch of the database tree is selected by the mac_address test. So the query finds only the device that has a MAC address of 10:16:CB:FF:FE:68 assigned to its interface. However, once the query finds this device, it displays all of the non-matching interfaces on that device because the SELECT * means, "Show everything..." and the FROM /network/device means, "...about this device."

If you run this query against Sample database A, it generates the following results: 

row
  *
    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

Query 7: Using square brackets in the FROM clause

You can use the same syntax in the FROM clause, like this: 

  SELECT *
  FROM /network/device/interface[name = 'eth2']

In this example, the specific branch of the database tree is selected by the [name='eth2'] test, and the results are filtered by it as well. So once the query finds this device, it displays only data about the matching interface and filters out any non-matching interfaces on that device, because the SELECT * means, "Show me everything..." and the FROM /network/device/interface means, "...about this interface."

If you run this query against Sample database A, it generates the following results: 

row
  *
    name eth2
    mac_address 10:16:CB:FF:FE:68
    in_octets 0
    out_octets 0
    oper_status 2

How do I find out more?

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