Splunk Search

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

bhawkins1
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 sums 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

gokadroid
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

View solution in original post

gokadroid
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

bhawkins1
Communicator

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

Get Updates on the Splunk Community!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...