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!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...