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 dataset with more than 10,000 unique groups, then the results of a groupby(key)calculate(count) query will be distorted compared to a where(key)calculate(count) query.

Groupby more than one field

The Groupby function allows you to group by multiple fields in your log data. Run a single query to get an overall view of your log data as well as drill down into that data. To use this feature, add up to 5 fields in a groupby query.

 
1
groupby(destination_user, result, service, source_asset_address) calculate(count)

After running a query, the results will be visualized in a stacked bar chart, showing 2 groups. If you added more than 2 fields to the query, click on a bar to drill down further into the next 2 groups, filtered by the bar you clicked on.

Results will also be displayed in a table format, allowing you to drill down on groups by clicking the arrows to display subsequent fields.

Increase Groupby Limit

You can increase the number of groups returned by your Groupby query with the limit keyword by adding limit(n) at the end of your query, where n represents the number of groups. Refer to the following table for the maximum groups per number of group keys. Any value inputted that is greater than the maximum number of groups will default to the maximum.

Note that for a number of groups larger than the maximum listed in the table above, the results are a no deterministic approximation.

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

If you are grouping by multiple fields, you can pass in additional values to limit the number of rows returned for each individual group. When grouping by multiple fields, the limit applies across groups according to the following table:

The following query sets a limit of 100 groups for the first field in the Groupby function, and 20 for the second field.

 
1
groupby(source_asset_address, service) calculate(count) sort(desc) limit(100, 20)

By default, LEQL limits each group to 40 results if you do not use a limit keyword in your query.

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).