PQL zip() function
zip() — Combines 2 sets of historical data from unrelated
parent nodes.Combines separate
datasets into one query so that the results of both can be compared side-by-side.
Syntax
The zip() function returns a tree,
and takes 2 parameters which are both trees:
tree zip(tree input, tree input)
For example, in a SELECT statement, use a variation of the following:
SELECT zip(
history(
argument
)
)
FROM path
Return value
The zip() function returns a tree.
Description
The zip() function can take 2 parameters.
The zip() function combines separate
datasets into one query, which enables you to see results of both in a single table
or graph. Use zip() when you want to compare outputs of a
history() function (such as min, max,
mean and so on) rather than actual nodes.
If you have two sets of data that you want to compare side-by-side
in a single graph, and that are not of the same parent node, you
cannot put them into one history() statement. You must
use a combination of both history() and zip()
to collapse the two subtrees of results into a single tree. The
zip() function examines the top-level children of multiple
subtrees, and merges each entry that it finds into a single tree.
Note: The zip() function is a complex function for
advanced users.
Examples
You can try the following examples against your own data in the GoToManage search field, or against fictitious data in the Query Sandbox:
- Example 1: Find free memory
- Example 2: Show minimum and maximum in 2 charts
- Example 3: Show minimum and maximum in 1 chart
- Example 4: Show multiple sets of data
Example 1: Find free memory
Query: How much and when free memory?
SELECT zip(
history(
memfree,
agg_function => 'max'
),
history(
memfree,
agg_function => 'min'
)
)
FROM /network/device/system/meminfo
WHERE ../meminfo is not null
This query finds all machines with free disk space under 1 GB, and formats
the freespace results in megabytes instead of bytes. For example, the
format() function converts
the free space for the Luxemburg machine from 244367360 Bytes to 233.05 MB.
Results: Depending on the database, this query returns results similar to this:
row
zip(history(memfree, agg_function => max),
history(memfree, agg_function => min))
z
when_1 2008-01-15T21:45:10.509485Z
memfree_1 28057600
when_2 2008-01-15T21:45:10.509485Z
memfree_2 28057600
z
when_1 2008-01-16T21:45:10.509485Z
memfree_1 29130752
when_2 2008-01-16T21:45:10.509485Z
memfree_2 29130752
z
when_1 2008-01-17T21:45:10.509485Z
memfree_1 906920
when_2 2008-01-17T21:45:10.509485Z
memfree_2 851628
. . .
Example 2: Show minimum and maximum in 2 charts
Query: What devices have free disk space that is less than 1 GB?
Query: : This example shows the minimum and maximum of free
memory in two separate graphs by using two separate history()
statements:
SELECT history( memfree, agg_function=>'max' ) FROM /network/device/system/meminfo WHERE ../meminfo is not null
SELECT history( memfree, agg_function=>'min' ) FROM /network/device/system/meminfo WHERE ../meminfo is not null
This query finds all machines with free disk space under 1 GB, and formats
the freespace results in megabytes instead of bytes. For example, the
format() function converts
the free space for the Luxemburg machine from 244367360 Bytes to 233.05 MB.
Results: Depending on the database, this query returns results similar to this:
Example 3: Show minimum and maximum in 1 chart
Query: What devices have free disk space that is less than 1 GB?
Query: To show the minimum and maximum of free memory in the
same graph, apply zip() to history(), like this:
SELECT zip(
history(
memfree,
agg_function=>'max'
),
history(
memfree,
agg_function=>'min'
)
)
FROM /network/device/system/meminfo
WHERE ../meminfo is not null
This query finds all machines with free disk space under 1 GB, and formats
the freespace results in megabytes instead of bytes. For example, the
format() function converts
the free space for the Luxemburg machine from 244367360 Bytes to 233.05 MB.
Results: Depending on the database, this query returns results similar to this:
Example 4: Show multiple sets of data
Query: What devices have free disk space that is less than 1 GB?
Query: To show more than two sets of data on the same graph —
such as minimum, maximum, and mean — apply another zip() to
the query, like this:
SELECT zip(
history(
memfree,
agg_function=>'mean',
zip(
history(
memfree,
agg_function=>'max'
),
history(
memfree,
agg_function=>'min'
)
)
)
)
FROM /network/device/system/meminfo
WHERE ../meminfo is not null
This query finds all machines with free disk space under 1 GB, and formats
the freespace results in megabytes instead of bytes. For example, the
format() function converts
the free space for the Luxemburg machine from 244367360 Bytes to 233.05 MB.
Results: Depending on the database, this query returns results similar to this:
When to use zip()
Use zip() when you want to compare two or more outputs
from a history() function — such as min,
max, mean and so on — rather than actual
nodes. When you want to compare two or more actual nodes that are not
outputs of a PQL statement, you can use the history()
function.
Note: When zip() merges the entries from top-level
children of multiple subtrees, it does not necessarily correlate them
the way you would expect. This is because when zip() merges
two subtrees, it merely puts the first two values together, then the second
two, and so on. If it runs out of children for one subtree, it skips it and
continues on.
For example, let's take the following two subtrees:
TreeMin={history={t=0,v=1},history={t=1,v=2},history={t=2,v=3}}
TreeMax={history={t=0,v=9},history={t=1,v=8},history={t=2,v=7}}
If you zip TreeMin and TreeMax, you will get this:
zip(...)={z={t_1=0,v_1=1,t_2=0,t_2=9},
z={t_1=1,v_1=2,t_2=1,v_2=8},
z={t_1=2,v_1=3,t_2=2,v_2=7}}
Notice that not only does it not always correlate, but it also pulls in subtrees as well as leaf nodes.
