PQL history() function

The history() function returns a list of previous values over time, if the leaf node had more than one value in the past.

Syntax

The history() function returns a tree and takes a tree parameter:

tree history(tree input)

You can query for historical values by specifying the columns of a SELECT statement as parameters to the history function. There are two formats in which to do this: history() and history#. Use curved history() brackets to get the history of a node, or the pound history# sign modifier on a column name to get its history.

argument#history

Curved-bracket format

Use SELECT history(argument) FROM <path> to extract the history of a node, such as:

 SELECT history(memfree) 
 FROM/network/device/system/meminfo 
 WHERE ../meminfo is not null

Pound-sign modifier

Use SELECT argument#history FROM <path> to extract the history of a node, such as:

 SELECT memfree#history 
 FROM /network/device/system/meminfo 
 WHERE ../meminfo is not null

Keep the following points in mind:

Place named arguments after unnamed arguments, which can only signify columns that the history function is operating on.
Use the => operator to pass values to named arguments with the value in single quotes, like this: argument => 'value' such as:

 SELECT history(
   in_octets,
   out_octets,
   from_time => '1 day ago', 
   agg_function => 'max',
   series_function => 'diff'
 ) 
 FROM /network/device/interface

The format when using from_time and to_time can be normal, such as: 2 hours ago, 32 hours ago, and so on. If to_time is not set, it defaults to now.
The intervals argument can be an integer number of seconds, or 1 minute, 1 hour, 15 minutes, and so on.

Description

Use history() instead of #history to collate data and to apply arguments.

Collating data

Use history() when you want to collate multiple columns under the same time basis. The following example results in a swapfree datum and a memfree datum for every timestamp, which is particularly useful if you want to see the results in chart form:

 SELECT history(swapfree,memfree) 
 FROM /network/device/system/meminfo 
 WHERE ../meminfo is not null

Depending on your database, the results might look something like this:

row
  history(swapfree, memfree)
    history
      when 2008-03-16T23:59:45.076323Z
      swapfree 1538084864
      memfree 32460800
    history
      when 2008-03-17T07:11:45.076323Z
      swapfree null
      memfree null
    history
      when 2008-03-17T14:23:45.076323Z
      swapfree null
      memfree 32460800
    history
      when 2008-03-17T21:35:45.076323Z
      swapfree null
      memfree null 
      ...

If you use #history in the same query, the results contain two uncorrelated series of data, each with its own set of timestamps:

 SELECT swapfree#history,memfree#history 
 FROM /network/device/system/meminfo 
 WHERE ../meminfo is not null

Depending on your database, the results might look something like this:

row
  history(swapfree)
    history
      when 2008-03-17T00:20:48.094740Z
      swapfree 1538084864
    history
      when 2008-03-17T07:32:48.094740Z
      swapfree null
    history
      when 2008-03-17T14:44:48.094740Z
      swapfree null
    history
      when 2008-03-17T21:56:48.094740Z
      swapfree null 
      ...
  history(memfree)
    history
      when 2008-03-17T00:20:48.094740Z
      memfree 411938816
    history
      when 2008-03-17T07:32:48.094740Z
      memfree null
    history
      when 2008-03-17T14:44:48.094740Z
      memfree null 
    history
      when 2008-03-17T21:56:48.094740Z
      memfree null 
      ...

Applying arguments

Use history() instead of #history to apply arguments.

Use history() when you want to apply arguments to the history function. For example, you can apply from_time and to_time to control the time span over which the history data is fetched, and how the values are aggregated.

Arguments

The history function supports the following named arguments:

from_time — Sets the date/time that starts the sequence. If not specified, the time of the oldest history value is used.
to_time — Sets the date/time that ends the sequence. If not specified, now is used.
resolution — Sets the size (in seconds) of a bucket.
intervals — Indicates how many data points are returned, within the maximum.
agg_function — Sets an aggregation function to combine multiple data points into a single data point to meet the requirements set by resolution and intervals.
series_function — Applies adjacent data points.
raw_series_function — Uses raw data to apply adjacent data points.
interpolation_function — Constructs new data points within a set of specified data points.

from_time and to_time

The arguments from_time, to_time, intervals, and resolution interact with and restrict each other in the following ways:

Currently, no more than 100 intervals (data points) are returned.
Setting from_time and to_time defines the starting point and the ending point of your query or graph.
from_time sets the date/time that starts the sequence. If not specified, the query defaults to the time of the oldest history value.
to_time sets the date/time that ends the sequence. If not specified, the query defaults to now.

All five Examples use from_time and/or to_time.

resolution and intervals

The arguments from_time, to_time, intervals, and resolution interact with and restrict each other in the following ways:

Currently, no more than 100 intervals (data points) are returned. A maximum of 100 intervals is used regardless of the resolution or intervals parameters.
Both intervals and resolution define ranges of time, which is can be defined by to_time and/or from_time arguments.
If you use either intervals or resolution without indicating a to_time, it defaults to now.
The intervals argument divides the time range into arbitrary segments. For example, it divides 2 hours into 24 equal segments.
The resolution argument divides the time range into segments of time, such as minutes or weeks. For example, it divides 2 hours into 5-minute segments.
Note: Under many circumstances, there is no need to set either intervals or resolution. Because the number of intervals (such as buckets or data points) is capped at a maximum size, that will almost always be the determining factor.

All five Examples use from_time, to_time, intervals, and resolution.

agg_function

The named argument agg_function operates on all of the values in a single interval. If multiple history values fall within a single intervals time span, agg_function indicates how those values should be aggregated into a single interval (data point).

For example, if you have a history datum every 5 minutes for the memfree node, and you ask for 24 hours of history data, the result is 288 intervals of data at 5-minute intervals. However, since the maximum is 100 intervals, the history function must aggregate 288 points into 100 points. The agg_function indicates how to do this aggregation.

The following values are currently defined for the agg_function:

sum — Returns the sum of all of the actual data intervals contained in the returned interval
count — Returns the count of all of the actual data intervals contained in the returned interval
min — Returns the minimum value out of all of the actual data contained in the returned interval
max — Returns the maximum value out of all of the actual data contained in the returned interval
mean — Returns the mean value out of all of the actual data contained in the returned interval

Example 2, Example 3, and Example 5 use agg_function to find the history of network traffic and server bandwidth.

series_function

The series_function operates on multiple intervals in the result set, unlike the named argument agg_function which operates on all of the values in a single interval.

The following values are currently defined for the series_function:

diff — Computes the difference between the previous value and the first value in an interval. This is useful for charts where the history values are a counter (such as in_octets or out_octets gathered on an interface via SNMP). In such cases, what is important is how many octets passed into an interface over a given unit of time.
The diff function handles simple wrapping of a counter across 32-bit and 64-bit boundaries in a method similar to that of the mrtg SNMP statistics graphing system.
per_second — Computes the rate of change in seconds.
per_minute — Computes the rate of change in minutes (exactly 60 seconds).
per_hour — Computes the rate of change in hours (exactly 60 minutes).
per_day — Computes the rate of change in days (exactly 24 hours).
per_week — Computes the rate of change in weeks (exactly 7 days).
per_month — Computes the rate of change in months (exactly 4 weeks).

Example 2 and Example 3 use series_function to find history of network traffic and rate of traffic over time.

raw_series_function

The raw_series_function operates on raw values of data, such as octets, instead of intervals like the series_function. Two values, diff and wrap_diff, are defined for the raw_series_function.

The following values are currently defined for the raw_series_function:

diff — Computes the difference between the previous value and the first value in an interval. Use diff when you have raw data that can go up or down, or that can have negative numbers, such as to plot a graph of changes in temperature or total numbers of users.
wrap_diff — Computes the difference between the previous value and the first value of raw data. The wrap_diff does for raw_series_function, what the diff does for series_function.

Use wrap_diff when you have raw data that will always go up and never down. For example, this happens when the data is collected by a counter where each datum bumps the number up, such as in_octets or out_octets gathered on an interface via SNMP. In such cases, what is important is how many octets passed into an interface over a given unit of time.

The wrap_diff function handles wrapping of a counter across 32-bit and 64-bit boundaries in a method similar to that of the mrtg SNMP statistics graphing system.

Example 3 and Example 5 use raw_series_function to find traffic over time and server bandwidth.

interpolation_function

The interpolation_function constructs new data points within a set of known and specified data points.

Interpolation functions are used to fill in gaps in the history() output that occur when the Crawler submits "No" at a given point in a time series. This can occur when there was an empty value to report, or when the value did not change. Interpolation functions are used when you want to do math on values from a subselect out of a history statement. Otherwise, the utility is limited to removing the points from graphs with less than 30 datapoints.

Only one value is currently defined for the interpolation_function:

constant — Works backwards in time, carrying the next history value back over any null gaps it finds. It also pulls the most recent data point forwards in time, to ensure your dataset appears complete.
Note: The interpolation_function=>'constant' is silently added to all stacked area charts. There is no way to disable this behavior at this time.

Example 4 uses interpolation_function to count the number over the last month.

Examples

Example 1: History of server memory usage

Replace the name Paris with that of one of your servers to get the history of memory usage:

 SELECT history(
   totalphysicalmemory, 
   totalvirtualmemory, 
   totalpagefilespace, 
   availablevirtualmemory, 
   from_time => '1 day ago', 
   resolution => '15 minutes'
 )
 FROM /network/device/wmi/win32_logicalmemoryconfiguration 
 WHERE ../win32_computersystem/name = 'PARIS'

Example 2: History of network traffic

Replace the IP address 10.10.10.10 with that of one of your servers to get the history of network traffic:

 SELECT history(
   in_octets,
   out_octets,
   from_time => '1 hour ago', 
   agg_function => 'max',
   series_function => 'diff'
 ) 
 FROM /network/device/interface 
 WHERE inet/ip_address='10.10.10.10'

Example 3: Rate of traffic over time

This example differs from Example 1 in the use of raw_series_diff to handle the fact that the raw data is collected by a counter that wraps when it reaches a maximum cap, similar to how a sentence wraps when it reaches the margin of the page. The counter wraps the data to keep it within workable limits. But since the wrap is arbitrary, it can show up as a spike on a graph. The wrap_diff handles this wrapping action.

Replace the IP address 10.10.10.10 with that of one of your own servers to get the rate of in and out traffic per second over the last week:

SELECT history(
  in_octets,
  out_octets, 
  from_time => '1 week ago', 
  raw_series_function => 'wrap_diff', 
  agg_function => 'sum', 
  series_function => 'per_second'
) 
FROM /network/device/interface 
WHERE inet/ip_address='10.10.10.10'

Example 4: Count over time

Add interpolation_function => 'constant' to your history call as in the following example, substituting your company name for company:

 SELECT when, (100 * company_count) / invites_issued 
 FROM (
   select history(
     invites_issued, 
     company_count, 
     from_time => '1 month ago',
     to_time => 'now', 
     interpolation_function=>'constant'
   ) as a FROM /apps/com/company/monitors
 )/a/history

Example 5: History of server bandwidth

This example implements a derived tree FROM clause — a SELECT clause nested inside another SELECT statement — to find the bandwidth by counting in-octets and out-octets. It displays the rate per second against the T1 maximum. This is particularly useful if you want to operate on the output of the history function.

Replace the name paris with that of one of your servers to get the bandwidth over time of that server:

SELECT when, 
  out_octets * 8 as "Out bps", 
  in_octets * 8 as "In bps",
  1572864 as "T1" from (
    SELECT history(
      out_octets, 
      in_octets, 
      resolution => '5 minutes', 
      from_time => '26 hours ago', 
      raw_series_function => 'wrap_diff', 
      agg_function => 'sum', 
      series_function => 'per_second'
    ) 
  as a FROM /network/device/interface 
  WHERE ../system/dns_name='paris' 
  AND if_index='3')/a/history

SELECT history( user_count, company_count, from_time => '1 week ago', raw_series_function => 'diff', agg_function => 'sum', series_function => 'per_hour', to_time => 'now') FROM /apps/com/company/monitors

Depending on the data in your Search Index, the results of this query would look similar to this:

row
   when2008-05-26T15:51:55.873035Z
   Out bps346280
   In bps45520
   T11572864
row
   when2008-05-26T16:07:31.872911Z
   Out bps280056
   In bps49536
   T11572864
row
   when2008-05-26T16:23:07.872787Z
   Out bps334224
   In bps133864
   T11572864
   . . .

When graphed, the same data is easier to comprehend. The T1 represents a stable cap and the in_octets and out_octets fluctuate against it. Notice the artificial spike at 02 because of a power failure which the query treated as a wrap. A solution to this is currently being developed.

Example 6: History of server bandwidth