Splunk Search

How to merge cells in a table based on value

szabados
Communicator

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

0 Karma
1 Solution

kbarker302
Communicator

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

View solution in original post

sundareshr
Legend

Try this

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

kbarker302
Communicator

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

adam_reber
Path Finder

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

0 Karma

szabados
Communicator

Thanks!

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

0 Karma

kbarker302
Communicator

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.

0 Karma
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 ...