Search Accross Multiple Structured Fields
Search across multiple structured fields using the transpose() function within groupBy()
Query
groupBy(@id, function=transpose())
| row[1] = "humio"
| groupBy(column)
Introduction
The transpose()
function is used to transform table
data by converting columns into rows.
By transposing event set, the information can be viewed and
summarized in a more human readable form. In this example, the
transpose()
function is used within a
groupBy()
function to search across multiple
structured fields in the HUMIO repository.
Example incoming data might look like this:
@id | field1 | field2 | field3 |
---|---|---|---|
1 | humio | other | data |
2 | other | humio | other |
Step-by-Step
Starting with the source repository events.
- flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0{{Aggregate}} 1{{Aggregate}} 2{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> result style 0 fill:#ff0000,stroke-width:4px,stroke:#000;logscale
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:column row[1] row[2] row[3] field1 humio other data field2 other humio other - flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0{{Aggregate}} 1{{Aggregate}} 2{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;logscale
| row[1] = "humio"
Filters for events where row[1] exactly matches the value
humio
.After filtering, the data might look like this:
column row[1] field1 humio field2 humio field3 humio - flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0{{Aggregate}} 1{{Aggregate}} 2{{Aggregate}} result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;logscale
| groupBy(column)
Groups results by the column field, showing which original fields contained the value
humio
, 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.
Summary and Results
The query is used to search across multiple structured fields in the
HUMIO repository to show where humio
appears most often. It makes results more readable, identifies field
patterns, and is very useful for statistical analysis.
Sample output from the incoming example data:
column | _count |
---|---|
field1 | 1 |
field2 | 1 |
field3 | 1 |