Search Accross Multiple Structured Fields

Search across multiple structured fields using the transpose() function within groupBy()

Query

logscale
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:

@idfield1field2field3
1humiootherdata
2otherhumioother

Step-by-Step

  1. Starting with the source repository events.

  2. 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:

    columnrow[1]row[2]row[3]
    field1humiootherdata
    field2otherhumioother

  3. 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:

    columnrow[1]
    field1humio
    field2humio
    field3humio
  4. 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.

  5. 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
field11
field21
field31