Examples: Querying for Historical Data

On this page, I'll walk through several permutations of a query that tells the history over time of traffic in and out of a particular interface. I'll develop the query through a series of examples starting with the simplest, and tweaking it until it gives the desired result. You might work through your own queries in the same way. And after you save a query that you have perfected, you can run it against any data, share it with colleagues in your workplace, or publish it to the GoToAssist community for the benefit of all.

Note: Although indentation and capitalization are used in these examples, it is not required. PQL does not require indentation, is not case sensitive, and supports alternative structures.

You can run the following examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:

Set up the Sandbox
Query 1: Show the traffic
Query 2: Show the traffic history
Query 3: Show rows of traffic history
Query 4: Correlate traffic history by date
Query 5: Show correlated traffic history per interface

Set up the Sandbox

I want to find out the history over time of traffic in and out of a particular interface. I'm going to practice 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 will 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: 100
  traffic_out: 200

This isn't all that useful because it doesn't tell me which device, which interface, or when. So...

Query 2: Show the traffic history

Next I add #history 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

Better. This tells me the history over time with a 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

But it's still not very useful. I want to know what interfaces and devices this traffic is related to. So...

Query 3: Show rows of traffic history

I add the ROWS option:

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

But something is still missing. I want to see the relationships between traffic, interface, and device.

Query 4: Correlate traffic history by date

I'll 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

OK, this is a little more useful. It displays 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 put interface/mac_address inside the brackets:

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

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

row: 
  history(traffic_in, traffic_out, interface/mac_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 information I want and displays it in a way that's useful. 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 at my fingertips. 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 GoToAssist community, in case others find it useful to run these parameters against their own data.