Splunk Search

## How to collate each consecutive N rows into one row with N columns? Communicator

I have a search which produces a `stats` with 2 columns, and `n = 3k`, where k is an integer, rows. The second column is a simple integer, and the rows form logical triplets, like so:

``````foo_x  2
foo_y  2
foo_z  5
bar_x 12
bar_y  5
bar_z  9
...
``````

How can I transform my result into a form with `k` rows and `3+1` columns, like so?:

``````        x   y   z
foo     2   2   5
bar    12   5   9
``````

Normally I would achieve this by changing the form of the query that produces the `n` rows into something more sensible. In this case, I'm not sure if that's possible - read on for why -

#### Extra information regarding source of these `3k` rows

I have a list of users, and `k` properties of those users, like so:

``````             fishs_age    dogs_age    cats_age    babys_age
user_a
user_b
user_c
...
``````

For each property, I classify `3` risk labels (they need to be separate so I can later `sum` them):

``````| foreach fishs_age,
dogs_age,
cats_age  [eval <<FIELD>>_hi = if(<<FIELD>> >= 9, 1, 0) ,
<<FIELD>>_md = if(<<FIELD>> >= 4 AND <<FIELD>> < 9, 1, 0) ,
<<FIELD>>_lo = if(<<FIELD>> >= 2 AND <<FIELD>> < 4, 1, 0)]
``````

I then calculate `sum`s and flip the axes in hopes to make a chart:

``````| stats sum(*_lo), sum(*_md), sum(*_hi)
| rename sum(*) as *
| transpose
``````

And this is the source of the original dataset

``````foo_x  2
foo_y  2
foo_z  5
bar_x 12
bar_y  5
bar_z  9
...
``````

i.e.

``````fishs_age_hi    2
fishs_age_md    5
fishs_age_lo    6
dogs_age_hi     3
dogs_age_md     4
dogs_age_lo    12
...
``````

Again: what I want is to transform this into:

``````        x   y   z
foo     2   2   5
bar    12   5   9
``````

i.e.

``````             hi  md  lo
fishs_age     2   2   5
dogs_age     12   5   9
``````
Tags (5)
1 Solution Motivator

Assuming this is how your original data showed up in respective fields `myField` and `value`:

``````myField     value
dogs_age_lo 12
dogs_age_md 4
dogs_age_hi 3
fishs_age_lo   6
fishs_age_md   5
fishs_age_hi   2
``````

Then try this:

``````your query to return myField and value
| rex field=myField "(?<fieldName>.*)_(?<columnName>[\S]+)"
| chart values(value) over fieldName by columnName
`````` Motivator

Assuming this is how your original data showed up in respective fields `myField` and `value`:

``````myField     value
dogs_age_lo 12
dogs_age_md 4
dogs_age_hi 3
fishs_age_lo   6
fishs_age_md   5
fishs_age_hi   2
``````

Then try this:

``````your query to return myField and value
| rex field=myField "(?<fieldName>.*)_(?<columnName>[\S]+)"
| chart values(value) over fieldName by columnName
`````` Communicator

That's done it - thanks a bunch! I would have never found this functionality without your help. .conf21 Now Fully Virtual!