topic How to merge cells in a table based on value in Splunk Search

How to merge cells in a table based on value

szabados — Wed, 27 Jul 2016 15:50:56 GMT

I have a table like this:

col1        | col2     | col3
samevalue   | value1   | value2
samevalue   | value3   | value4
samevalue   | value6   | value5
samevalue2  | value1   | value2
samevalue 2 | value3   | value4
samevalue 2 | value6   | value5

What I want to achieve is to merge the cells in col1, where the value is the same as the cell above, like this:

col1        | col2     |  col3
samevalue   | value1   | value2
            | value3   | value4
            | value6   | value5
samevalue2  | value1   | value2
            | value3   | value4
            | value6   | value5

I'm producing the table with pivot, but in that column I need some sort of the grouping as I would have with stats, but without messing up the relation between the rows in other columns

Edit: my padding was broken when I posted the question, sorry

Re: How to merge cells in a table based on value

kbarker302 — Wed, 27 Jul 2016 18:21:51 GMT

If I understand correctly, you can do this with a combination of streamstats and eval. In the below example, streamstats appends the previous col1 value to the current record. The eval statement then sets col1 to blank if the value hasn't changed.

your base search
| sort col1 col2 
| streamstats current=f window=1 global=f last(col1) as last_col1 
| eval col1=if(col1==last_col1,'',col1)
| table col1 col2 col3

This link gave me some guidance on using the streamstats statement:

https://answers.splunk.com/answers/87382/comparing-fields-with-previous-events.html

Re: How to merge cells in a table based on value

sundareshr — Wed, 27 Jul 2016 19:35:03 GMT

Try this

your base search | stats values(col2) as col2 values(col3) as col3 by col1

Re: How to merge cells in a table based on value

szabados — Thu, 28 Jul 2016 07:52:09 GMT

Thanks!

Not exactly the same approach as I head in my head, but I like it a lot.
I learned something new again.

Re: How to merge cells in a table based on value

kbarker302 — Thu, 28 Jul 2016 14:39:04 GMT

Thank you - I used custom JavaScript to solve a similar issue previously, but your question made me reconsider whether there was a way to do it purely with SPL.

Re: How to merge cells in a table based on value

adam_reber — Wed, 30 Aug 2017 18:44:29 GMT

Just wanted to say thank you for this clever solution, it helped me immensely today!