Analytic Functions and Visualizations

With our powerful LEQL functions you are able to produce queries that will easily visualize your data without any preprocessing required.

LEQL Functions

Here are the analytic functions that InsightOps offers.

Count

Log search also supports returning a count of matched search results. Append calculate(COUNT) to your search query or press the calculate to get the number of search results. An example can be seen below.

1
where(status=500) calculate(COUNT)

Sum

You can use the sum function to total the values of your name value pairs. If you had a KVP for sale_value and wanted to know the total sales for a specified time period it would be returned with the following query:

1
where(sale_total>0) calculate(SUM:sale_total).

Average

The average function works the same as sum, but it computes the mean of the values matching the search criteria. For instance, to get the average value of your sales, you might invoke a search like:

1
where(sale_total>0) calculate(AVERAGE:sale_total)

Count Unique

The Count Unique function returns the number of unique values for a given key. It takes one parameter: the name of the key. For example, if you have the KVP userID in your log file and want to find the number of unique users:

1
where(userID) calculate(UNIQUE:userID)

Min

The Min function will return the minimum value of the key for each time period. For example the query below will return the shortest response time for each time period:

1
where(status=200) calculate(MIN:responseTime)

Max

The Max function will return the maximum value of the key for each time period. For example the query below will return the longest response time for each time period:

1
where(status=200) calculate(Max:responseTime)

Groupby

You can perform search functions based on grouping identical datasets. In the example below the logs contain the KVP “status” which represents the HTTP status code for a request from a web server.

1
where(status) groupby(status) calculate(count)

By default LEQL will automatically sort your result set based a descending order if no sort keyword is used.

Groupby is searched over the dataset, so if you have a large dataset of more than 10,000 or so, then the results of a groupby(key)calculate(count) query will be distorted compared to a where(key)calculate(count) query.

Increase Groupby Limit

You can increase the number of groups returned by your groupby query by using the limit keyword. Note, for very large number of groups the results are a no deterministic approximation. Using limit you can specify to return up to 10000 groups in your query. This is done by adding limit(N) at the end of your query where N is a number between 1 and 10000

1
where(status) groupby(status) calculate(count) sort(desc) limit(350)

Sort

You have the ability to sort the results returned by a groupby in ascending or descending order, or alphabetically by the names of the keys that are turned.

This will sort descending based on the count returned by the function

1
where(status>=300) groupby(status) calculate(count) sort(desc)

This will sort a list of usernames alphabetically:

1
groupby(username) calculate(count) sort(asc#key)

The keywords to sort descending are desc and descending. The keywords to sort ascending are asc and ascending.

Note If you use sort and sort in your query, you must place limit after the sort function.

Timeslice

InsightOps will automatically calculate 10 equal time intervals when performing a time based query (i..e. when you're not grouping by a key in your logs).

You can manually set the number of time intervals by using the timeslice function. The valid input for timeslice is a number between 1 and 200 (inclusive), or you can specify a unit of time.

The query below used against a 1 hour search period would return the count of 500 errors per minute.

1
where(status=500) calculate(count) timeslice(60)

This will divide the results into 30 second intervals

1
where(status=500) calculate(count) timeslice(30s)

This will divide the results into 5 minute intervals

1
where(status=500) calculate(count) timeslice(5m)

This will divide the results into hourly intervals

1
where(status=500) calculate(count) timeslice(1h)

Percentile

The Percentile option now allows you to exclude outliers from your search functions. In simple mode you can quickly select either a 95th or 99th percentile search function based on a key value pair which has a numerical number. Users using advanced mode can specify their own percentile vale by using pctl(80):key_value_pair in their calculate function.

1
calculate(pctl(99):RecordNumber)

Bytes

The Bytes option lets you calculate the size of your logs in byte form. This is useful for users who wish to verify the size of the logs that they have sent to their account. A simple query that would calculate the size of the given log would be where(/.*/) calculate(bytes)

Bytes per Selected Log You can use the following query to see the breakdown of bytes per log per the selected log:

1
groupby(#log)calculate(bytes)

Standard Deviation

The Standard Deviation option lets you calculate the standard deviation of a given series values. This is useful when trying to establish what values would be considered within normal variance to a given mean e.g response times.

1
calculate(standarddeviation:service)

You can also use the keyword sd as a shortcut e.g calculate(sd:service).