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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...