Writing queries that extract historical values

In GoToAssist's tree-structured database, leaf nodes (that is, nodes that can contain values) can also contain a history of previous values. You can track changes over time by using PQL functions in a query for historical values.

• Leaf nodes with history
• Non-leaf nodes with history
• Sample queries

Leaf nodes with history

Each node in the tree-structured database that contains a value can also maintain a historical record of all previous revisions of that value.

For example, the cpu_utilization leaf node in the above diagram contains its current value, 34%, plus a time-series of previous values at different times. The revision history is like a 2-column table:

One column lists values; the other lists corresponding timestamps. The time series is simply a list of such pairs of values and timestamps. Each timestamp indicates when the system learned the corresponding value. New values are stored only if they are different from the previously stored value.

Non-leaf nodes with history

The tree-structured database can also maintain a historical record of previous values for non-leaf nodes, such as a set of running processes. It's likely that running processes would undergo some change between the times that the computer is queried. Instead of replacing the entire set, it's useful to be able to look back in time and see what the running set was at a previous point in time.

When historical information is kept at the node level, the time-series is a list of pairs of timestamps and pointers. Each pointer indicates a unique sub-tree copy that represents the sub-tree at that point in time. Only some nodes in the hierarchy are labeled for sub-tree copy. In the case of sub-tree copy, the entire sub-tree must be provided by the GoToAssist Crawler for each update.

History functions

Sample queries

In the following 2 examples, we have a network with two devices, each with an interface that has a history of revisions.

• Query for history per device
• Query for history per interface

Query 1: History per device

This query fetches the revision history of MAC addresses of device/interfaces per device:

  SELECT interface/mac_address#history                   
  FROM /network/device

This query generates the revision history of interfaces on the devices in Sample database A:

row
  history(interface/mac_address)
    history
      when2008-03-31T20:15:48.488011Z
      mac_addressnull
    history
      when2008-04-01T03:27:48.488011Z
      mac_addressnull
    history
      when2008-04-01T10:39:48.488011Z
      mac_addressnull
    history
      when2008-04-01T17:51:48.488011Z
      mac_addressnull
    history
      when2008-04-02T01:03:48.488011Z
      mac_addressnull
    history
      when2008-04-02T08:15:48.488011Z
      mac_addressnull
    history
      when2008-04-02T15:27:48.488011Z
      mac_addressnull
    history
      when2008-04-02T22:39:48.488011Z
      mac_addressnull 
   ...      

Query 2: History per interface

This query fetches the revision history of the same MAC addresses of device/interfaces per interface:

  SELECT interface/(mac_address, name#history)
  FROM /network/device

This query uses a path-scoped projection where the path restricts the SELECT clause. It generates the same revision history of the same interfaces as in Query 1, but this time the MAC addresses are correlated by interface:

row
  interface/(mac_address, history(name))
    item
      mac_address 02:A1:A2:A3:A4:A4
        history
          when 2008-03-31T20:15:48.488011Z
          name null
        history
          when 2008-04-01T03:27:48.488011Z
          name null
        history
          when 2008-04-01T10:39:48.488011Z
          name null
        history
          when 2008-04-01T17:51:48.488011Z
          name null
        history
          when 2008-04-02T01:03:48.488011Z
          name null
        history
          when 2008-04-02T08:15:48.488011Z
          name null
        history
          when 2008-04-02T15:27:48.488011Z
          name null
        history
          when 2008-04-02T22:39:48.488011Z
          name null 
   ...  

Related topics

• More about functions
• Continue tutorials
• More about PQL
• Examples and more examples

| Views: 1165 | Last Updated: Thu, May 09 2013 2:29 PM

Is this article helpful?