Analytics Query Language

From ITOM Practitioner Info
Jump to: navigation, search

Analytics Query Language (AQL) is a hierarchical language that provides layers of abstraction on analytic queries. The idea here is that the more abstract, the easier it is for you to write AQL in an ad hoc fashion. The primary objective of AQL is to simplify your ad hoc query experience. This applies to the process of building custom dashboards as well as troubleshooting problems using statistical algorithms.

Use AQL when the Phrased Query Language (PQL) syntax is not specific enough to return the data you need. When using AQL, you can be more specific about the data collected. You can also filter, group, and order the collected data in a single query.

The layers of abstraction in AQL are:

  • Built-in analytics that are defined as functions that become intrinsic in AQL
  • A query language to provide SQL-like access to all collections
  • Functions and expressions as abstractions of queries

The following topics include examples that show script usage, command line usage, command line syntax, and file editing. If you copy and paste any examples from this manual, carefully review the results of your paste before running a command or saving a file.

As an example of the layers of abstraction, consider the following query:

When using AQL, you can search for collected metrics, such as cpu_util, which is shown in the following AQL query. Metrics are collected values over time for measurements such as system up time and CPU utilization.

from i in (oa_sysperf_global)
let interval=300
let analytic_interval=between($starttime,$endtime)
where (i.host_name like "myhost")
select moving_avg(i.cpu_util)

This query assumes a collection of system metrics from a predefined Operations Bridge Analytics collection (oa_sysperf_global) and calculates a time series of the moving average of the CPU utilization for the system called "myhost". The time series data is collected every 300 seconds (5 minutes) and the time range is specified by the internal macros $starttime and $endtime.

The above example uses moving_avg, which is a built-in analytic that significantly simplifies this transformation over standard SQL.

This query pattern is very useful for all sorts of metrics. Suppose you have other metrics and functions and want to calculate the time series of a particular metric using a particular function r for a particular host or hosts. In such a case, you can use a query pattern as shown above.

Operations Bridge Analytics AQL supports using query patterns to be abstracted into AQL functions. Using the above example, suppose you want to generalize the query to generate a time series of any metric in oa_sysperf_global using any function for any set of hosts. To generate this time series, define an AQL function as follows:

/* Returns the moving analytic of a specific Operations Agent metric by host. Input parameters are the host filter, metric name, and moving analytic function name. */
define oaSysperfMovingMetric(hostFilter, metric, function) =
from i in (oa_sysperf_global)
let analytic_interval = between($starttime,$endtime)
let interval = $interval
where i.host_name like hostFilter
group by i.host_name
select function(i.metric)

With this function defined, the following AQL expression:

[oaSysperfMovingMetric("myhost", cpu_util, moving_avg)]

is identical to the above AQL query.

In addition, the following expression:

[oaSysperfMovingMetric("myhost", swap_util, moving_max)]

gives you the time series of the moving maximum swap utilization on host "myhost".

The ability to define specialized AQL provides a significant 'ease-of-use' factor in using Operations Bridge Analytics to do ad hoc analytics. Operations Bridge Analytics includes several packages of useful AQL functions that you can see by using the OBA console