How-To: Sorting by Timestamps within groupBy()

When collating a group of field data that includes a field that you also want to sort on, use the collect() and use an embedded call to the sort() function in your aggregate function call.

For example, if you are reporting on data that you want to summarize by a field value and a formatted timestamp, you can sort the collected fields with by the timestamp using this format:

Click + next to an example below to get the full details.

Sort Timestamps With groupBy()

Sorting fields based on aggregated field values

Query

Search Repository: humio

logscale
timestamp := formatTime(format="%H:%M")|groupby([thread],function=[{sort("timestamp")
| collect("timestamp")}])
Introduction

When using aggregation, you may want to sort on a field that is part of the aggregated set but not the main feature of the aggregated value. For example, sorting the values by their timestamp rather than the embedded value. To achieve this, you should use a function that sorts the field to be used as the sort field, and then use collect() so that the value from before the aggregaion can be displayed in the generated event set. This query can be executed in the humio respository.

Step-by-Step

  1. Starting with the source repository events.

  2. logscale
    timestamp := formatTime(format="%H:%M")

    Creates a new field, timestamp formatted as HH:MM.

  3. logscale
    |groupby([thread],

    Groups the events, first by the name of the thread and then the formatted timestamp.

  4. logscale
    function=[{sort("timestamp") 
| collect("timestamp")}])

    Uses the sort() combined with collect() as the method fo aggregation. As an embedded expression for the function, this will sort the events on the timestamp field and then retrieve the field as it would normally be removed as part of the aggregation process.

  5. Event Result set.

Summary and Results

The result set will contain a list of the aggregated thread names sorted by the timestamp:

threadtimestamp
BootstrapInfoJob10:09
DataSynchJob10:09
Global event loop10:10
LocalLivequeryMonitor10:09
LogCollectorManifestUpdate10:09
TransientChatter event loop10:10
aggregate-alert-job10:09
alert-job10:09
block-processing-monitor-job10:09
bloom-scheduler10:09
bucket-entity-config10:09
bucket-overcommit-metrics-job10:09
bucket-storage-download10:09
bucket-storage-prefetch10:09
chatter-runningqueries-logger10:09
chatter-runningqueries-stats10:09