Using Logentries Query Language (LEQL) to analyze Sensor Data

This is one of a series of IoT blogs that show how easy it can be to integrate a range of real sensor devices with Logentries. The focus of this blog is to show how the Logentries Query Language (LEQL) can be used to search and analyze that data. A separate blog will also show the usefulness of the alerting functionality in Logentries, where labels can be set that will display with matching events/data, alerts can be sent to a range of destinations (including webhooks) and anomalies or inactivity can be detected.

The volume and variety of sensor data, usually unstructured or semi-structured makes it a challenge to collect and store the data, but also to analyze such an amount of data. Viewing the collected sensor data at a point in time is important, but the value is increased if it can be put in context over time periods from a single source or across multiple sources, especially if easy to use tools and languages are made available to allow data scientists and domain experts to work with the data from sensors.

This blog will show how to use LEQL from the Search bar in the UI, but the same functionality will be available over our REST Query API, including language features such as groupby designed for heavy hitters, e.g. the sensors sending the highest readings, to be selected from a large data set.

The data we will use in this blog came from the TI SensorTag, but could be from any sensor device. We showed how to send data from the TI SensorTag to Logentries in a previous blog.

The SensorTag data is sent as a message like the following:

2016-04-20T12:47:02.712Z info topic=sensorTag/temperature payload.object=18.4 payload.ambient=22.8 _msgid=f3db5df4.0c24a

Note that keys are extracted from the logs, e.g. payload.object, and will appear as suggestions as you construct the where clause of your LEQL request.

LEQL

More detailed information on LEQL can be found at https://logentries.com/doc/search/, but for the purposes of this blog, it can be considered as a query language consisting of:

  • a where clause that can contain a keyword to search on or use a regular expression
  • a groupby clause that allows you to select heavy hitters from your data
  • a calculate clause that can perform a  range of mathematical operations on your data - count, average, sum, min/max, uniqueand then display it using
  • sort (advanced mode only)
  • timeslice(advanced mode only)
  • percentile (advanced mode only)

The LEQL request will also contain:

  • log keys to specify logs to search
  • a from/to time range to search for log events in specified logs

LEQL examples

These examples will show how we can search and analyse SensorTag data

  • using where to specify a particular sensor from the range of sensors
  • using groupby to reduce the amount of data and produce key results
  • using calculate to find the average
  • using named capture groups
  • using timeslice to show changes in accelerometer readings

where

The SensorTag supplies data from a range of sensors, but where allows us to create a simple request to query only one of the sensors, with a greater than condition to make it more specific, as below:

where(payload.temperature>23)

Note that the displayed events also have a label called “Too Hot” which was also added for all log events > 23.

count

I now want to see how many requests I got from my humidity sensor over a period

where(payload.humidity) calculate (count)

average

Having reviewed the temperature readings that were >23, using the earlier query, I want to calculate the average in a time range. So, I use a regular expression in the where clause to capture the temperature readings in the message into a named group called temp and then calculate(average) on that group – I could easily change average to max/min/sum/unique on those temperature readings.

where(/temperature=(?P<temp>\d*)/) calculate(average:temp)

groupby

Having reviewed the average temperature readings, I now want to look at temperatures above 22 and group particular temperature values to see which occurs most frequently, for which I use groupby

where(payload.temperature>22) groupby(payload.temperature)

But I decide that is not a useful grouping, so I use the same regular expression as above and groupby to get a count in a set of temperature ranges/groups, using the request below:

where(/temperature=(?P<temp>\d*)/) groupby(temp)

Another example of groupby would be where I want to group the number of requests by the type of sensor – I use a regular expression to extract the type that follows “sensorTag/” into type and do a groupby on type, which shows that during the period under test, all the sensors were reporting at the same frequency, which can then be adjusted as appropriate for each sensor.

where(/sensorTag\/(?P<type>[^ ]*)/) groupby(type)

percentile

While the groupby has shown  that there are no real outliers outside the range 22-26. I can also use the LEQL percentile function to explicitly exclude outliers from the search functions in time ranges, by selecting a 99th percentile search for payload.temperature=value key value pair, as below:

calculate(percentile(99):payload.temperature)

timeslice

The LEQL functions we have seen until now are useful for periodic/regular sensors like temperature where the readings are less likely to have sudden abrupt changes in value. An accelerometer may, however, undergo rapid changes in short time periods and we may want to isolate those times for further analysis. This is where timeslice comes in. The following request breaks the count for the overall from/to period into 5 time slices:

where(accelerometer AND payload.x >0.03) calculate(count) timesl
ice(5)

This indicates that there were periods with rapid changes (I shook the SensorTag device  🙂 ). I can narrow those ranges down with more timeslices and I can then do more specific “where” searches in the time ranges of interest:

where(accelerometer AND payload.x >0.03) calculate(count) timesl
ice(150)

Summary

This Blog has shown how easy it is to search and analyze real sensor data sent to Logentries using LEQL – these same queries can be used in dashboards in the UI that update the data at regular periods. Future blogs will show how to generate alerts from this sensor data, including inactivity and anomaly alerts and also provide examples of how to send data to Logentries from more devices.