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.
Here are the analytic functions that InsightOps offers.
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.
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:
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:
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:
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:
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:
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.
1where(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
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
1groupby(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.
|Number of group keys||Maximum groups|
|groupby(x0, x1, x2)||30,000|
|groupby(x0, x1, x2, x3)||40,000|
|groupby(x0, x1, x2, x3, x4)||50,000|
Note that for a number of groups larger than the maximum listed in the table above, the results are a no deterministic approximation.
1where(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:
|groupby(x, y) limit(5)||groupby(x, y) limit(5, 5)|
|groupby(x, y, z) limit(20, 12)||groupby(x, y, z) limit(20, 12, 12)|
|groupby(x, y)||groupby(x, y) limit(40, 40)|
The following query sets a limit of 100 groups for the first field in the Groupby function, and 20 for the second field.
1groupby(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.
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
1where(status>=300) groupby(status) calculate(count) sort(desc)
This will sort a list of usernames alphabetically:
1groupby(username) calculate(count) sort(asc#key)
The keywords to sort descending are
descending. The keywords to sort ascending are
Note If you use
sort in your query, you must place
limit after the
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.
1where(status=500) calculate(count) timeslice(60)
This will divide the results into 30 second intervals
1where(status=500) calculate(count) timeslice(30s)
This will divide the results into 5 minute intervals
1where(status=500) calculate(count) timeslice(5m)
This will divide the results into hourly intervals
1where(status=500) calculate(count) timeslice(1h)
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.
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
Bytes per Selected Log You can use the following query to see the breakdown of bytes per log per the selected log:
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.
You can also use the keyword
sd as a shortcut e.g