Identifying Matching Fields using transpose()

Identifying the name of a matched field during a query by making use of the transpose() function

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result
logscale
/ssh/i
| groupBy(field=user,function=transpose())
      | row[1] = /ssh/|
| column != @rawstring|column != msg

Introduction

The transpose() converts the rows of each event into columns, and the columns (the fields) into a row. This changes the way the events are displayed and provides for querying or matching data on the field name rather than the field contents. This can be used to identify where in a query a particular match has been located when the data might be spread across multiple potential fields.

The transpose() swaps columns (fields) for rows and rows (events) for columns. This can be useful to determine which field a particular match has been located in. For example, given the following input data:

usercommandparent
lewisr/usr/bin/sed/usr/bin/perl
kingm/bin/mount/bin/bash
jonesk/usr/bin/rsync/usr/bin/ssh
jacksonb/sbin/fdiskpowershell.exe
howardn/usr/bin/tailcmd.exe
hillp/bin/systemctl/usr/bin/sudo
harrisl/usr/sbin/tcpdump/usr/sbin/sshd

Here we have data that shows both the command and the parent command parent. To identify whether a particular command was run either as a command or as a parent (for example a shall or ssh you could independently search each field. Alternatively, use transpose() to transpose the columns and then be shown the matching column.

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    /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.

  3. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | 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:

    usercolumnrow[1]row[2]row[3]row[4]row[5]
    adamsb#hostnameuser="adamsb"user="adamsb"user="adamsb"user="adamsb"<no value>
    adamsb#repostddatastddatastddatastddata<no value>
    adamsb#typesyslog-datagensyslog-datagensyslog-datagensyslog-datagen<no value>
    adamsb@idTENLqJfjZQ4NG7s4AEs5b9zP_9_7_1765871856TENLqJfjZQ4NG7s4AEs5b9zP_9_9_1765871897TENLqJfjZQ4NG7s4AEs5b9zP_9_189_1765872044TENLqJfjZQ4NG7s4AEs5b9zP_9_369_1765872191<no value>
    adamsb@ingesttimestamp1765871856736176587189780617658720447751765872191822<no value>
    adamsb@rawstring<34>1 2025-12-16T07:57:36.020Z user="adamsb" command="/usr/bin/grep" parent="/usr/bin/ssh" <34>1 2025-12-16T07:58:17.095Z user="adamsb" command="/usr/bin/grep" parent="/usr/bin/ssh" <34>1 2025-12-16T08:00:44.042Z user="adamsb" command="/usr/bin/grep" parent="/usr/sbin/sshd" <34>1 2025-12-16T08:03:11.109Z user="adamsb" command="/usr/bin/grep" parent="/usr/bin/ssh" <no value>
    adamsb@timestamp1765871856020176587189709517658720440421765872191109<no value>
    adamsb@timestamp.nanos0000<no value>
    adamsb@timezoneZZZZ<no value>
    adamsbappcommand="/usr/bin/grep"command="/usr/bin/grep"command="/usr/bin/grep"command="/usr/bin/grep"<no value>
    adamsbcommand/usr/bin/grep/usr/bin/grep/usr/bin/grep/usr/bin/grep<no value>
    adamsbhostuser="adamsb"user="adamsb"user="adamsb"user="adamsb"<no value>
    adamsbmsg parent="/usr/bin/ssh" parent="/usr/bin/ssh" parent="/usr/sbin/sshd" parent="/usr/bin/ssh" <no value>
    adamsbparent/usr/bin/ssh/usr/bin/ssh/usr/sbin/sshd/usr/bin/ssh<no value>
    adamsbpriority34343434<no value>
    adamsbuseradamsbadamsbadamsbadamsb<no value>
    adamsbversion1111<no value>

    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.

  4. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | 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.

  5. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2[/Filter/] 3[/Filter/] 4[/Filter/] result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 4 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | 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.

  6. Event Result set.

Summary and Results

The final data might look like:

usercolumnrow[1]row[2]row[3]row[4]row[5]
evansmcommand/usr/bin/ssh/usr/bin/ssh/usr/bin/ssh/usr/bin/ssh/usr/bin/ssh
fosterdparent/usr/sbin/sshd/usr/bin/ssh<no value><no value><no value>
wardjcommand/usr/sbin/sshd/usr/sbin/sshd/usr/sbin/sshd/usr/sbin/sshd/usr/sbin/sshd
wardjparent/usr/sbin/sshd/bin/bashcmd.exe/sbin/lsof/usr/bin/ssh
watsonmparent/usr/sbin/sshd/usr/bin/ssh<no value><no value><no value>
whitepparent/usr/bin/ssh/usr/sbin/sshd<no value><no value><no value>
williamsrparent/usr/sbin/sshd/usr/bin/ssh<no value><no value><no value>
wilsonkparent/usr/bin/ssh/usr/sbin/sshd<no value><no value><no value>
woodmparent/usr/bin/ssh/usr/sbin/sshd<no value><no value><no value>
wrightjparent/usr/sbin/sshd/usr/bin/ssh<no value><no value><no value>
youngdparent/usr/bin/ssh/usr/sbin/sshd<no value><no value><no value>
zimmermanrparent/usr/sbin/sshd/usr/bin/ssh<no value><no value><no value>

Now the data is in a a format where the source of the information, in this case either the original command or the parent command, can be identified. The use of transpose() here provides a method for changing the way the data is identified from searching specific fields for data to search for data dn identifying which field that data was located in. This can be useful to identify conts or security instances.