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.

/ssh/i

An initial filter for the command we are looking for. This will limit the processing of the full set of data, but is optional if you wanted a view across multiple potential commands.

| groupBy(field=user,function=transpose())

Group the events, using the user as the basis for the grouping. This will create a unique set of events based on the username. The transpose() is then used as the aggregation function; this will swap rows (matching events) and columns (fields). This initially converts the data into the username and then a row for field of the incoming events:

Because there might be multiple matching input rows from the event set, each matching row of data is place into an array field row for each matching source event.

| row[1] = /ssh/|

Now search for the matching command that is being examined. A single field can be searched for this information looking for a valid match.

| column != @rawstring|column != msg

The output will include rows (matching columns from the original event set) that are not needed. These can now be filtered out by filtering out the columns we dont want.

Event Result set.

Starting with the source repository events.

groupBy(@id, function=transpose())

Groups events by unique @id values, applies the transpose() function for each group, converting row values into column headers. A new row-based structure for each @id field is created.

After using transpose(), the data might look like this:

| row[1] = /httpd/

Filters for events where row[1] regex matches the value httpd.

After filtering, the data might look like this (@rawstring has been removed from the below for clarity):

| groupBy(column)

Groups results by the column field, showing which original fields contained the value httpd, and makes a count of matches per field, returning the counted results in a field named _count. The final groupBy(column) removes duplicate entries.

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.