Examples: Querying for historical data

This page walks through several permutations of a query that tells the history over time of traffic in and out of a particular interface. The query is developed through a series of examples that are tweaked several times until it gives the desired result. You might work through queries in this same way. And after you save a query that you worked hard on, you can run it against any data, share it with colleagues in your workplace, or publish it to the Paglo community for the benefit of all.

Note that indentation and capitalization is used in these example queries to make them easier to read. But in fact, PQL does not require indentation, is not case sensitive, and supports alternative structures.

Set up the Sandbox

I want a query that tells me the history over time of traffic in and out of a particular interface. I'm going to test this query in the Sandbox, which you can open by clicking the Developers link at the bottom of any page, and then clicking the Sandbox tab. I start with an INSERT statement to enter some fictitious data to work with. This is similar to the sample database, but with traffic_in and traffic_out nodes:

 INSERT INTO / values {
   network => {
     device => {
       interface => {
         mac_address => '00:01:02:03:04:05', 
         name => 'eth0', 
         status => 'down'
       }, 
       traffic_in => 100, 
       traffic_out => 200
     }
   }
 }

Next, I run an UPDATE statement to change the history of the traffic_in and traffic_out nodes:

 UPDATE@'2007-11-05T16:14:52.000000Z' /network/device 
 SET traffic_in = 800, traffic_out = 900 
 WHERE interface/mac_address = '00:01:02:03:04:05'

The Sandbox remains unchanged when the UPDATE statement runs. I need to run a SELECT statement to see the changes.

Query 1: Show the traffic

At first, I simply select traffic_in and traffic_out from the device node:

SELECT traffic_in, traffic_out from /network/device

The result gives me the updated traffic_in and traffic_out data:

row: 
  traffic_in: 800
  traffic_out: 900

This isn't very useful because the results don't indicate which device, which interface, or when.

Query 2: Show the traffic history

Next I add #history to the original query to get the history over time of the traffic_in and traffic_out of the device:

SELECT traffic_in#history, traffic_out#history 
FROM /network/device

This result provides more information than Query 1. It includes the history over time with the timestamp:

row: 
  history(traffic_in): 
    history: 
      when: 2007-10-07T01:38:40.279618Z
      traffic_in: 200
    history: 
      when: 2007-11-05T16:14:52.0Z
      traffic_in: 800
  history(traffic_out): 
    history: 
      when: 2007-10-07T01:38:40.280618Z
      traffic_out: 300
    history: 
      when: 2007-11-05T16:14:52.0Z
      traffic_out: 900

However, it is still not very useful. I need to know what interfaces and devices this traffic is related to.

Query 3: Show rows of traffic history

Now I add the ROWS option to the same query:

SELECT ROWS traffic_in#history, traffic_out#history 
FROM /network/device

The results are now displayed like a table, which is easier to read:

row: 
  history(traffic_in)/history/when: 2007-10-07T01:39:27.748618Z
  history(traffic_in)/history/traffic_in: 200
  history(traffic_out)/history/when: 2007-10-07T01:39:27.750618Z
  history(traffic_out)/history/traffic_out: 300

row: 
  history(traffic_in)/history/when: 2007-10-07T01:39:27.748618Z
  history(traffic_in)/history/traffic_in: 200
  history(traffic_out)/history/when: 2007-11-05T16:14:52.0Z
  history(traffic_out)/history/traffic_out: 900

row: 
  history(traffic_in)/history/when: 2007-11-05T16:14:52.0Z
  history(traffic_in)/history/traffic_in: 800
  history(traffic_out)/history/when: 2007-10-07T01:39:27.750618Z
  history(traffic_out)/history/traffic_out: 300

row: 
  history(traffic_in)/history/when: 2007-11-05T16:14:52.0Z
  history(traffic_in)/history/traffic_in: 800
  history(traffic_out)/history/when: 2007-11-05T16:14:52.0Z
  history(traffic_out)/history/traffic_out: 900

However, the relationships between traffic, interface, and device are still missing.

Query 4: Correlate traffic history by date

Next I apply the history to the same pair of traffic_in and traffic_out nodes by using brackets:

SELECT history(
  traffic_in, 
  traffic_out
) 
FROM /network/device

The results now display the relationship between the timestamps, and each traffic_in and traffic_out pair. This is a little more useful:

row: 
  history(traffic_in, traffic_out): 
    history: 
      when: 2007-10-07T01:41:59.931618Z
      traffic_in: 200
      traffic_out: 300
    history: 
      when: 2007-11-05T16:14:52.0Z
      traffic_in: 800
      traffic_out: 900

Query 5: Show correlated traffic history per interface

Finally, I add interface/mac_address inside the brackets:

SELECT HISTORY(
  traffic_in, 
  traffic_out, 
  interface/mac_address
) 
FROM /network/device

Jackpot! These results tell me the traffic_in and traffic_out for two specific interfaces, and when:

row: 
  history(traffic_in, traffic_out, interface/ip_address): 
    history: 
      when: 2007-10-07T01:44:04.361618Z
      traffic_in: 200
      traffic_out: 300
      mac_address: 00:01:02:03:04:05
    history: 
      when: 2007-11-05T16:14:52.0Z
      traffic_in: 800
      traffic_out: 900

This query finds the data I want and displays it the way I want. If this is something I want to use often, I'll save it. Display it as a chart or table. Add it to my Dashboard to keep it on hand throughout the day. Maybe share it with colleagues if others in my company need this data. And since I worked so hard on it, I'll publish it to the greater Paglo community, in case others find it useful to run these parameters against their own data.