series_metric_fl()

The series_metric_fl() function selects and retrieves time series of metrics ingested to Azure Data Explorer using the Prometheus monitoring system. This function assumes the data stored in Azure Data Explorer is structured following the Prometheus data model. Specifically, each record contains:

• timestamp
• metric name
• metric value
• a variable set of labels ("key":"value" pairs)

Prometheus defines a time series by its metric name and a distinct set of labels. You can retrieve sets of time series using Prometheus Query Language (PromQL) by specifying the metric name and time series selector (a set of labels).

Note

series_metric_fl() is a UDF (user-defined function). For more information, see usage.

Syntax

T | invoke series_metric_fl(timestamp_col, name_col, labels_col, value_col, metric_name, labels_selector, lookback, offset)

Arguments

• timestamp_col: The name of the column containing the timestamp.
• name_col: The name of the column containing the metric name.
• labels_col: The name of the column containing the labels dictionary.
• value_col: The name of the column containing the metric value.
• metric_name: The metric time series to retrieve.
• labels_selector: Time series selector string, similar to PromQL. It's a string containing a list of "key":"value" pairs, for example '"key1":"val1","key2":"val2"'. This parameter is optional, default to empty string, that means no filtering. Note that regular expressions are not supported.
• lookback: Range vector to retrieve, similar to PromQL. This parameter is optional, default to 10 minutes.
• offset: Offset back from current time to retrieve, similar to PromQL. Data is retrieved from ago(offset)-lookback to ago(offset). This parameter is optional, default is 0, which means that data is retrieved up to now().

Usage

series_metric_fl() is a user-defined tabular function, to be applied using the invoke operator. You can either embed its code in your query, or install it in your database. There are two usage options: ad hoc and persistent usage. See the below tabs for examples.

Ad hoc

For ad hoc usage, embed its code using the let statement. No permission is required.

let series_metric_fl=(metrics_tbl:(*), timestamp_col:string, name_col:string, labels_col:string, value_col:string, metric_name:string, labels_selector:string='', lookback:timespan=timespan(10m), offset:timespan=timespan(0))
{
    let selector_d=iff(labels_selector == '', dynamic(['']), split(labels_selector, ','));
    let etime = ago(offset);
    let stime = etime - lookback;
    metrics_tbl
    | extend timestamp = column_ifexists(timestamp_col, datetime(null)), name = column_ifexists(name_col, ''), labels = column_ifexists(labels_col, dynamic(null)), value = column_ifexists(value_col, 0)
    | extend labels = dynamic_to_json(labels)       //  convert to string and sort by key
    | where name == metric_name and timestamp between(stime..etime)
    | order by timestamp asc
    | summarize timestamp = make_list(timestamp), value=make_list(value) by name, labels
    | where labels has_all (selector_d)
}
;
//
demo_prometheus
| invoke series_metric_fl('TimeStamp', 'Name', 'Labels', 'Val', 'writes', '"disk":"sda1","host":"aks-agentpool-88086459-vmss000001"', offset=now()-datetime(2020-12-08 00:00))
| render timechart with(series=labels)

Persistent

For persistent usage, use .create function. Creating a function requires database user permission.

One-time installation

.create function with (folder = "Packages\\Series", docstring = "Selecting & retrieving metrics like PromQL")
series_metric_fl(metrics_tbl:(*), timestamp_col:string, name_col:string, labels_col:string, value_col:string, metric_name:string, labels_selector:string='', lookback:timespan=timespan(10m), offset:timespan=timespan(0))
{
    let selector_d=iff(labels_selector == '', dynamic(['']), split(labels_selector, ','));
    let etime = ago(offset);
    let stime = etime - lookback;
    metrics_tbl
    | extend timestamp = column_ifexists(timestamp_col, datetime(null)), name = column_ifexists(name_col, ''), labels = column_ifexists(labels_col, dynamic(null)), value = column_ifexists(value_col, 0)
    | extend labels = dynamic_to_json(labels)       //  convert to string and sort by key
    | where name == metric_name and timestamp between(stime..etime)
    | order by timestamp asc
    | summarize timestamp = make_list(timestamp), value=make_list(value) by name, labels
    | where labels has_all (selector_d)
}

Usage

demo_prometheus
| invoke series_metric_fl('TimeStamp', 'Name', 'Labels', 'Val', 'writes', '"disk":"sda1","host":"aks-agentpool-88086459-vmss000001"', offset=now()-datetime(2020-12-08 00:00))
| render timechart with(series=labels)

Example

The following example doesn't specify selector, so all 'writes' metrics are selected. This example assumes that the function is already installed, and uses alternative direct calling syntax, specifying the input table as the first parameter:

series_metric_fl(demo_prometheus, 'TimeStamp', 'Name', 'Labels', 'Val', 'writes', offset=now()-datetime(2020-12-08 00:00))
| render timechart with(series=labels, ysplit=axes)