Skip to main content
Sumo Logic

first and last

You want to use first to find the earliest occurrence in search results, and last to find the result that follows all others, based on the sort order for the query.

Limitations

First and last are supported in Live Dashboards or any continuous query.

first

The default sort order for returned messages is reverse chronological—most recent descending to oldest. So first finds the most recent value of the field being evaluated within the time range. However, if you have specified a sort order other than descending chronological, then first finds the message that precedes all others based on the sort order defined in your query.

If there is no sort order specified for returned results (for example, when using limit 20), then first simply returns the first result encountered without respect to date or list order.

Syntax

  • first(fieldname)

Rules

  • Creates field named _first

Example

... | first(error_message) group by hostname

last

Finds the last value of the field being evaluated within the time range and according to the specified sort order. Remember that the default order for returned messages is reverse chronological—most recent descending to oldest. Therefore, last is the oldest result in the returned list. If you have specified an order other than reverse chronological, then last finds the ending message that follows all others based on your sort order.

Syntax

  • last(fieldname)

Rules

  • Creates field named _last

Example

... | last(status_code) group by hostname

Sample log message:

Aug 2 04:06:08 : host=10.1.1.124: local/ssl2 notice mcpd[3772]: filesize=20454: diskutilization=0.4 : 01070638:5: Pool member 172.31.51.22:0 monitor status down.

Example based on sample log message:

disk* 
| parse "diskutilization=*" as disk 
| disk>0.8?1:0 as overcapacity 
| last(overcapacity) by _sourceHost 
| sort by _last

This query finds all messages that contain the term disk* and parses out all that have a diskutilization= value. It then extracts the value of diskutilization into field disk. It then determines if that value is greater than 80% and will find the last occurrence of that value per host effectively producing a list of hosts that have disk utilization that is over 80%.

Using first and last operators in Data Panels

Data isn’t always ingested in perfect order, meaning that log messages may arrive out of sequence. In one-off queries that use the first operator or last operator nonsequential logs aren’t an issue. However, to save a query as a Data Panel, because the query is run continuously, logs need to be in perfectly sequenced to produce results. To make sure that they are in perfect order, you’ll need to explicitly tag fields with the withtime operator.

Using withtime forces log messages to be put in perfect order, which then allows you to add queries that contain the first or last operator. It created a field named xxx_withtime that will appear as part of your search results. The most_recent and least_recent operators allow you to order data from newest to oldest.

The withtime operator is not considered a standalone operator; it’s only used with the first and last operators to enable those operators to be used in Data Panels.

Syntax

  • * | parse ... as status | withtime status | most_recent(status_withtime) by _sourcehost
  • * | parse ... as status | withtime status | least_recent(status_withtime) by _sourcehost

Examples

Find the most recent visitors to our site by IP.

Say we’d like to keep an eye on visitors that hit our site from different countries. Running a query like:

*ip* OR *address*
| parse regex "(?<IP>\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"
| lookup latitude, longitude, country_code from geo://default on ip=IP
| where !isNull(country_code)
| withtime IP
| most_recent(ip_withtime) by country_code

produces results like:

mostRecent_IPs.png

Find the most recent user logged in by account. 

If you’d like to find the user who most recently logged in (per account or organization, which we’ve used in our example) you can use a first operator, in a query that looks something like:

...| parse "Successful login for user '*', organization: '*'" as user,org_id
| withtime user
| first(user_withtime) by org_id

This query would return a list of organizations and a list of the user ID that most recently logged in. You could save the query as a Data Panel to keep a constant eye on logins.