Identify Matching Fields using transpose() | LogScale Query Examples

Identify Matching Fields using `transpose()`

Identify the name of a matched field during a query using 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() function can be used to convert 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.

In this example, the transpose() function 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.

Example incoming data might look like this:

user	command	parent
lewisr	/usr/bin/sed	/usr/bin/perl
kingm	/bin/mount	/bin/bash
jonesk	/usr/bin/rsync	/usr/bin/ssh
jacksonb	/sbin/fdisk	powershell.exe
howardn	/usr/bin/tail	cmd.exe
hillp	/bin/systemctl	/usr/bin/sudo
harrisl	/usr/sbin/tcpdump	/usr/sbin/sshd

The data shows both the command executed and its parent command in the parent field. 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

Starting with the source repository events.
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
```
Filters events containing the string ssh (case-insensitive) in any field. This initial filter improves query performance by reducing the data set before applying the transpose operation. This will limit the processing of the full set of data, but is optional if you wanted a view across multiple potential commands.

logscale

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

Group the events, using the user field as the basis for the grouping. This will create a unique set of events based on the username.

The transpose() function 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:

user	column	row[1]	row[2]	row[3]	row[4]	row[5]
adamsb	#hostname	user="adamsb"	user="adamsb"	user="adamsb"	user="adamsb"	<no value>
adamsb	#repo	stddata	stddata	stddata	stddata	<no value>
adamsb	#type	syslog-datagen	syslog-datagen	syslog-datagen	syslog-datagen	<no value>
adamsb	@id	TENLqJfjZQ4NG7s4AEs5b9zP_9_7_1765871856	TENLqJfjZQ4NG7s4AEs5b9zP_9_9_1765871897	TENLqJfjZQ4NG7s4AEs5b9zP_9_189_1765872044	TENLqJfjZQ4NG7s4AEs5b9zP_9_369_1765872191	<no value>
adamsb	@ingesttimestamp	1765871856736	1765871897806	1765872044775	1765872191822	<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	@timestamp	1765871856020	1765871897095	1765872044042	1765872191109	<no value>
adamsb	@timestamp.nanos	0	0	0	0	<no value>
adamsb	@timezone	Z	Z	Z	Z	<no value>
adamsb	app	command="/usr/bin/grep"	command="/usr/bin/grep"	command="/usr/bin/grep"	command="/usr/bin/grep"	<no value>
adamsb	command	/usr/bin/grep	/usr/bin/grep	/usr/bin/grep	/usr/bin/grep	<no value>
adamsb	host	user="adamsb"	user="adamsb"	user="adamsb"	user="adamsb"	<no value>
adamsb	msg	parent="/usr/bin/ssh"	parent="/usr/bin/ssh"	parent="/usr/sbin/sshd"	parent="/usr/bin/ssh"	<no value>
adamsb	parent	/usr/bin/ssh	/usr/bin/ssh	/usr/sbin/sshd	/usr/bin/ssh	<no value>
adamsb	priority	34	34	34	34	<no value>
adamsb	user	adamsb	adamsb	adamsb	adamsb	<no value>
adamsb	version	1	1	1	1	<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.

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/|
```
Searches for the matching command pattern in the first row of the transposed data. This identifies which fields contained the matching pattern in the original data structure.
Note that the output will include rows (matching columns from the original event set) that are not needed. These can now be filtered out.
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
```
Removes unnecessary columns from the output by filtering out the @rawstring and msg fields. This improves the readability of the final results by showing only relevant field matches.
Event Result set.

Summary and Results

The query is used to identify whether specific commands appear in either the command or parent command fields by transposing the data structure.

This query is useful, for example, to track command execution patterns and identify potential security concerns in command chains.

Sample output from the incoming example data:

user	column	row[1]	row[2]	row[3]	row[4]	row[5]
evansm	command	/usr/bin/ssh	/usr/bin/ssh	/usr/bin/ssh	/usr/bin/ssh	/usr/bin/ssh
fosterd	parent	/usr/sbin/sshd	/usr/bin/ssh	<no value>	<no value>	<no value>
wardj	command	/usr/sbin/sshd	/usr/sbin/sshd	/usr/sbin/sshd	/usr/sbin/sshd	/usr/sbin/sshd
wardj	parent	/usr/sbin/sshd	/bin/bash	cmd.exe	/sbin/lsof	/usr/bin/ssh
watsonm	parent	/usr/sbin/sshd	/usr/bin/ssh	<no value>	<no value>	<no value>
whitep	parent	/usr/bin/ssh	/usr/sbin/sshd	<no value>	<no value>	<no value>
williamsr	parent	/usr/sbin/sshd	/usr/bin/ssh	<no value>	<no value>	<no value>
wilsonk	parent	/usr/bin/ssh	/usr/sbin/sshd	<no value>	<no value>	<no value>
woodm	parent	/usr/bin/ssh	/usr/sbin/sshd	<no value>	<no value>	<no value>
wrightj	parent	/usr/sbin/sshd	/usr/bin/ssh	<no value>	<no value>	<no value>
youngd	parent	/usr/bin/ssh	/usr/sbin/sshd	<no value>	<no value>	<no value>
zimmermanr	parent	/usr/sbin/sshd	/usr/bin/ssh	<no value>	<no value>	<no value>

Note that the output clearly shows whether matches were found in the command or parent fields, making it easy to identify the context in which SSH-related commands appear.

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 and identifying which field that data was located in. This can be useful to identify conts or security instances.

Versions of this Page

Examples Library