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 GoToManage 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 GoToManage search field, or against fictitious data in the Query Sandbox:
- Set up the Sandbox
- Example 1: Show the traffic
- Example 2: Show the traffic history
- Example 3: Show rows of traffic history
- Example 4: Correlate traffic history by date
- Example 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 GoToManage community, in case others find it useful to run these parameters against their own data.
