Loggr Query Language

So your posting all your valuable event data to Loggr. Your data is safe and accessible through our tools, but you own your data and we know how important it is to have full control and access to it. Whether you want to integrate it into other business applications, perform deeper analysis, archive it or simply move it to a different service, we make it pretty easy to do.

Tools
Usage
Limitations
Examples

Tools

There are a couple ways to execute queries with Loggr.

Query Editor

One of the tools we provide for digging through your events is a custom query language. We call it the Loggr Query Language (or LQL). It’s a very simple language similar to SQL that can be used in our API or within the query editor on the website.

sss

Web API

You can also run queries through the Loggr Web API using the Query endpoint.

GET /logs/{logkey}/query?query={lql}

top

Usage

The Loggr Query Language is a language with syntax similar to SQL. The query syntax is:

GET TABLE[.<class_name>]
  [ WHERE <search_condition> ]
  [ TAKE n ]
  [ SKIP n ]
  [ SORT <field_name> [ ASC | DESC ] ]
  [ GROUPBY <field_name> | DAY | HOUR ]
  [ <field_name> OCCURS( TODAY | <date_literal> [ +/- NOW | MINUTES(n) | HOURS(n) | DAYS(n) | WEEKS(n) | MONTHS(n) ]
    [, TODAY | <date_literal> [ +/- NOW | MINUTES(n) | HOURS(n) | DAYS(n) | WEEKS(n) | MONTHS(n)] ) ]
  [ COLUMNS( <field_name> | COUNT | AVG | SUM | MIN | MAX | KEY [, ...n] ) ]


TABLE Clause

The TABLE keyword allows you to specify what kind of record you are querying. Currently we only support events and users. Depending on which table you specify the query behaves slightly different.

GET events.<class_name>
  • If you specify events then you can use a dot notation to specify the class of events you want to query. Event classes are setup by your log's admin. (See Classes for more details).
  • Results for events are returned based on the created date of the event.
// returns all events of class 'order' with a value greater than $10
GET events.order WHERE value > 10

GET users
  • If you specify users then there isn't classes available, so just leave off the class_name.
  • Results for users are returned based on the first activity date for the user.
// returns all users with email address in the yahoo.com domain
GET users WHERE email LIKE '@yahoo.com'

WHERE Clause

This clause is very similar to SQL. It allows you to specify criteria to filter your records. You can filter on most fields of events and users.

// returns all events tagged with 'error' and contains extra data
GET events WHERE tags = ('error') AND data = true

Event Fields
Field Value Type Examples Comment
id string id = '519e5fd7151b4e13f800cbbc'
text string text LIKE 'foo bar'
created date created = '5/22/2013' matches day, overrides OCCURS clause
link string link LIKE 'http://'
hash string hash = 'b3c2H2fiNLD809oiBnB6EQ==' hash of event text
tags array tags = ('trace','failed')
source string source = 'RG-WEB001'
user string user LIKE '@yahoo.com'
bookmarks array bookmarks = ('user1@here.com','user2@here.com')
alerts array alerts = ('user1@here.com','user2@here.com')
data bool data = true indicates if event contains data
value number value > 20 AND value <= 50
geo array cannot be used in WHERE clause


User Fields
Field Value Type Examples Comment
id string id = '519e5fd7151b4e13f800cbbc'
firstActivity date
lastActivity date matches day, overrides default OCCURS clause
username string
email string
useragent string
lastSessionStarted date
lastSessionActionCount number
totalCurrentSession number
totalSession number
totalActions number
lastPage string
lastIP string

TAKE and SKIP Clauses

These clauses allow you to page the results. Since Loggr only allows a maximum of 500 results to be returned with a single query, retreiving more records will require multiple requests. Use paging to get all records for a large query.

The value specified for TAKE will be the page size. The default value for TAKE is 100. For example, 'TAKE 10' will return up to 10 records.

The value specified for SKIP is the number of pages to skip (NOT number of records). For example, if you use 'TAKE 10 SKIP 3' the records returned will be 21-30.

// returns records 21-30 of query
GET events TAKE 10 SKIP 2

SORT Clause

To specify how your records are sorted use the SORT clause. SORT accepts a field name and an order, specified with ASC or DESC.

// returns records sorted by Value from high to low
GET events SORT value DESC

GROUPBY Clause

This clause enables you to create aggregate results for your query. You can group by DAY, HOUR or a specific field (see Field lists above). The results that come back will be an array of aggregate totals, like:

[
    {
        "key":"9/19/2011",
        "count":"10",
        "avg":"83",
        "sum":"157",
        "min":"28",
        "max":"224"
    }
]

The key will reflect the keyword or field you grouped by.

OCCURS Clause

This clause lets you specify the time over which your query runs. The best way to explain is is by using some examples.

If you leave out the OCCURS clause the time frame will be the current day, or TODAY (TODAY equals 12am of the current day). The following queries are equivalent:

GET events
GET events OCCURS(TODAY)

The clause allows you to specify two parameters. One for the start of the timeframe and the second for the end of the timeframe. If you only specify one, the second one is automatically set to the end of the current day. In the second query above, the second parameter was ommitted which just assumed the end of TODAY. So using a more complex parameter, the following queries are also equivalent:

GET events OCCURS(TODAY)
GET events OCCURS(TODAY, TODAY + HOURS(24))

As you can see from the last example, each parameter can be an expression. This enables you to query on relative dates, or specific dates and times. Here are some samples of expressions that are permitted:

Expression Description
TODAY 12am of the current day
NOW The current date and time
'12/13/2010' 12am of the literal date
'12/13/2010 3:00pm' The literal date and time
TODAY - DAYS(1) 12am of the previous day
TODAY - HOURS(24) 12am of the previous day
NOW - DAYS(1) 24 hours from the current date and time
NOW - MINUTES(10) 10 minutes prior to the current date and time
TODAY + HOURS(12) 12pm of the current day
'12/13/2010' + DAYS(3) 12am of 12/16/2010

Note

The query will always assume the earliest date is the start of the timeframe, even if you happen to switch them as parameters.



Modifying OCCURS Default Behavior

You can change which field is used for filtering by date by preceding the OCCURS keyword with a field name. By default, the query will choose the field on which the OCCURS filter is applied. This is optional, but if supplied, it must be a date type.

GET events created OCCURS(TODAY)
GET users lastActivity OCCURS(TODAY, TODAY + HOURS(24))

Here is a list of the valid fields you can use by record type:

Field Record Type Comment
created Event This is the default
firstActivity User
lastActivity User This is the default
lastSessionStarted User


COLUMNS

This clause enables you to specify which fields you want returned in your results. They can be any field listed in the tables above (specific to the TABLE type you request).

GET users COLUMNS(username, email)
GET events COLUMNS(text, tags, value)

Or if you are doing a GROUPBY query, the fields are KEY, COUNT, AVG, SUM, MIN, MAX. Be sure to respect the case of these fields. They will not work using lowercase characters.

GET events GROUPBY HOUR COLUMNS(KEY, SUM)

It is helpful to specify columns to reduce the size of the result set.

top

Limitations

There are some limitations that we impose to make sure our service is able to handle all user's request.

1. Request can return a maximum of 500 records. By default, the page size is 100, but using the TAKE clause you can set the max to 500. To return more records than 500 you will need to make multiple requests, using TAKE and SKIP to specify which page of records you want.

2. Requests can not span a period of time greater than 30 days. Again, if you need to span a greater time period, please use paging as described above.

top

Examples

Some examples. First, to get the text from the last 5 events added to your log.

GET events TAKE 5 SORT created DESC OCCURS(TODAY) COLUMNS(text)

This next example uses a WHERE clause to get the last 5 events that have text matching ‘New subscriber’ exactly.

GET events WHERE text='New subscriber' TAKE 5 SORT created DESC
OCCURS(TODAY) COLUMNS(text)

Using the same query above, but replace the exact match on text with a regex match clause LIKE. Also, this time we’re going to return just the KEY for the event which we can use to call into the API for more specifics about the event.

GET events WHERE text LIKE '(D|d)ave' TAKE 5 SORT created DESC
OCCURS(TODAY) COLUMNS(key)

The following queries based on a set of tags. It also return the created date and geo info for each event.

GET events WHERE tags=('user','created') TAKE 5 SORT created DESC
OCCURS(TODAY) COLUMNS(text, created, geo)

You can also include boolean operators in the WHERE clause.

GET events WHERE tags=('error') OR tags=('failed') TAKE 5
SORT created DESC OCCURS(TODAY) COLUMNS(text)

Finally a more complex example showing how you can query for aggregate information and specify time ranges. This query gets the sum of all events of class ‘order’ having the tag ‘online’. It uses the OCCURS clause to get the aggregates over the last 7 days. It will return the results as a list of 7 records for each day in the query. Each record will contain the KEY (in this case, the date) and the SUM.

GET events.order WHERE tags=('online') GROUPBY DAY
OCCURS(TODAY - DAYS(7), TODAY) COLUMNS(KEY,SUM)

If you want to get specific dates, you can include them in the OCCURS clause instead of the keywords shown above.

GET events OCCURS('4/22/2011 12:00am', '4/22/2011 12:00pm')
COLUMNS(text)