Starting with the source repository events

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:

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

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:

|transpose(header=type)

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.

|drop(column)

Event Result set

Starting with the source repository events

First, aggregate 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.

groupby([loglevel)

Transposing the events within the transpose() will make each field in each event a row in the new stream of events, i.e. 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:

| transpose(header=loglevel)

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.

| drop(column)

Event Result set