Splunk Search

Move values in columns to top independently?

skriticos2
Loves-to-Learn Lots

Hi,

I'm a bit stuck with a data transformation. I got it to a point where all the columns and values are in the right order, but the rows are offset for each column.
I need to find a way to move the all the values in subsequent columns up to the top so they align.
See below example of before/after data alignment I want to realize.
Thanks for ideas!

 

 

 

 

f1      f2      f3      f4
----------------------------
foo
bar
baz
        x
        y
                a
                b
                        1
                        2
                        3
                        4

                        
                        
f1      f2      f3      f4
----------------------------
foo     x       a       1
bar     y       b       2
baz                     3
                        4

 

 

 

 

 

 

 

Labels (1)
0 Karma

peter_krammer
Communicator

Here is another solution for the originally requested example:

generate sample events:
| makeresults  count=11
| streamstats count
| eval f1 = case(count=1, "foo", count=2, "bar", count=3, "baz")
| eval f2 = case(count=4, "x", count=5, "y")
| eval f3 = case(count=6, "a", count=7, "b")
| eval f4 = case(count=8, "1", count=9, "2", count=10, "3", count=11, "4")
| fields - count, - _time

output: 

f1      f2      f3      f4
----------------------------
foo
bar
baz
x
y
a
b
1
2
3
4
solution SPL:
<yoursearch>
| foreach * [| eval field = if(isnotnull(<<FIELD>>), "<<MATCHSTR>>", field )]
| streamstats count as index by field
| stats values(*) as * by index
| fields - field, - index
output:
f1      f2      f3      f4
----------------------------
foo x a 1
bar y b 2
baz 3
4
 
0 Karma

peter_krammer
Communicator

Note: This option only works correctly if every row in the input has only 1 of the fields with a value. 

0 Karma

peter_krammer
Communicator

I tried to solve this for you, but the query might be a bit inefficient:

| makeresults
| eval a = mvappend("a1", "a2", "a3"), b = mvappend("b1", "b2"), c = mvappend("c1", "c2", "c3", "c4" )

| rename * as *_values | foreach *_values [| eval max = if(max > mvcount(<<FIELD>>), max, mvcount(<<FIELD>>) )]
| foreach *_values [| eval <<MATCHSTR>>_filler = mvrange(mvcount(<<FIELD>>), max, 1), <<MATCHSTR>>_filler = mvmap(<<MATCHSTR>>_filler, " "), <<FIELD>> = mvappend(<<FIELD>>, <<MATCHSTR>>_filler)]
| foreach *_values [| eval <<FIELD>> = mvmap(<<FIELD>>, "<<MATCHSTR>>=\"" . <<FIELD>> . "\"")]
| fields - *_filler
| eval raw = mvmap(mvrange(0, max, 1), " ")
| foreach *_values [| eval raw = mvzip(raw, <<FIELD>>)]
| fields raw
| mvexpand raw
| rename raw to _raw
| extract

 

VatsalJagani
SplunkTrust
SplunkTrust

Thanks, @peter_krammer  

This solution works! @skriticos2 - please consider accepting the solution.

0 Karma

peter_krammer
Communicator

The code above solves this example:

header, values (mv field)

a = { a1, a2, a3 }
b = { b1, b2 }
c = { c1, c2, c3, c4 }

target table (each value in separate row/field)
a       b       c
-------------------
a1      b1      c1
a2      b2      c2
a3              c3
                c4

 

0 Karma

renjith_nair
Legend

Knowing your current search would be useful to construct the final output. However, try below

your search|stats values(*) as * by "your grouping field"

There should be a field to co-relate the data , for e,g "y" should be matched to bar,b and 2 for consistency in the result

Happy Splunking!
0 Karma

skriticos2
Loves-to-Learn Lots

Thanks, but I've been there (actually that is a previous transformation to this). Having the data in multi value fields does not help me so far, as I want to expand them to a number of new columns (based on the number of values in the multi value field). And each row has a different number of values between 10 and 70. Something like an mvexpand command, but sideways would help me (creating new fields for each value), but I'm not aware of how to achieve that.

0 Karma

skriticos2
Loves-to-Learn Lots

To illustrate from another perspective, I have a table where one column contains the field name and another the values (all properly sorted). Now I want to create a transposed table where each of the values goes into a separate row for the header that it belongs to. Tricky part is, that the number of values is variable. I think I have a dimension too much here for a simple mvexpand. A sideways mvexpand would help. So, I'm a bit stuck.

header, values (mv field)

a = { a1, a2, a3 }
b = { b1, b2 }
c = { c1, c2, c3, c4 }

target table (each value in separate row/field)
a       b       c
-------------------
a1      b1      c1
a2      b2      c2
a3              c3
                c4

 

0 Karma

skriticos2
Loves-to-Learn Lots

Something like this would also work. Create a new column for each value in an mvfield, then use a transpose afterward. Is that possible? (I mean something like a foreach statement - not if/case - I have up to 70 values)

 

header  values (mv)
-------------------
a       a1 a2 a3
b       b1 b2
c       c1 c2 c3 c4


header  values (mv)     r1      r2      r3      r4
--------------------------------------------------
a       a1 a2 a3        a1      a2      a3
b       b1 b2           b1      b2
c       c1 c2 c3 c4     c1      c2      c3      c4
0 Karma
Get Updates on the Splunk Community!

Updated Team Landing Page in Splunk Observability

We’re making some changes to the team landing page in Splunk Observability, based on your feedback. The ...

New! Splunk Observability Search Enhancements for Splunk APM Services/Traces and ...

Regardless of where you are in Splunk Observability, you can search for relevant APM targets including service ...

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...