Starting with the source repository events.

groupBy([type,actor.user.id],function={groupBy(actor.user.id, function=max(@timestamp))})

The first step to creating a pivot table is the base query that will create the initial summary of the information. In this fragment, a nested groupBy() aggregation. The embedded aggregation creates a group of the maximum access time for a given user, by using max() on the @timestamp against the actor.user.id. This creates a table of the last event by the user. The outer groupBy() then creates an aggregation of this maximum user time against the type which defines the operation performed.

The result is a table of the last user and time for a specific operation; for example, the last time a query was executed. An example of this table can be seen below:

|transpose(header=type)

The transpose() will convert individual columns into rows, switching the orientation. For example, the type column will now become the type row. However, there are no row titles, so the title for the resulting table will by default create a header row containing the column and row numbers, like this:

However, the aggregate grouping, type could be used instead as a valid header for each column. To achieve that, use the header parameter to specify type as the column. The resulting table now looks like this:

|drop(column)

The table created contains the summarized information pivoted around the user ID and last event time further summarized by the type of the event. However, there is a column in the table, column, which is now a field in the event stream that was generated from the old row before the table was pivoted.

That column can be removed by dropping the column field from the event using drop() to remove the column from the events.

Event Result set.

Starting with the source repository events.

splitString(field=userAgent,by=" ",as=agents)

Splits up the userAgent field using a call to splitString() and places the output into the array field agents

This will create individual array entries into the agents array for each event:

|array:union(array=agents,as=browsers)

Using array:union() we aggregate the list of user agents across all the events to create a list of unique entries. This will eliminate duplicates where the value of the user agent is the same value.

|transpose()

Using the transpose() function, we transpose the rows and columns for the list of matching field names and values, turning:

into:

|drop(column)

We do not need the column information, just the unique list of browsers in the row[1] column, so we drop the field from the event list.

|groupBy(row[1])

Now we can aggregate the list, by the remaining field row[1] to provide the unique list of potential values. This is not a count of the times each has occurred, but a unique list of all the different possible values. The resulting list looks like this:

Event Result set.

Starting with the source repository events.

groupby([loglevel)

Aggregates the lows by the loglevel. This field will either be WARN, ERROR or INFO depending on the level of the event log entry. The default function is used, which results in a count in the number of times an event of this type has been seen:

In this output, each event is a row in the table, each with two fields, loglevel and _count.

| transpose(header=loglevel)

Transposing the events within the transpose() will make each field in each event a row in the new stream of events, for example, the loglevel field with the value ERROR will become the field ERROR, swapping the rows for columns. By using the header parameter, transpose() uses the value of the aggregated field as the fieldname. The output will now be a table with a column (field) for each value, and a single row with the count:

| drop(column)

In the final output, the column field in the events is the one generated from the field names of the original events and it's not needed, so it can be removed by using the drop() function to remove the field from the event set.

Event Result set.