topic Re: Remove duplicates in multiple columns (Column format - abc_000001 ) and sorting in same order with wild card? in Splunk Search

Remove duplicates in multiple columns (Column format - abc_000001 ) and sorting in same order with wild card?

Kukkadapu — Tue, 29 Sep 2020 10:51:27 GMT

Hi, I need some help to transform the below event? Thanks for your time.

2016-08-30 13:13:48,525 log_level='INFO' abc_000001="temp" abc_000002="temp1" abc_000003="temp2" abc_000004="temp2" abc_000005="temp2" abc_000006="temp3" ....... abc_000255="123" abc_000256="xyz"

| table abc_*

doesn't display all the fields and the order is missing. There are almost 300 fields. How to table all the field values using wild card?

How to create a new field - NEW_FIELD with the unique values of abc_* in the same order.

Expected Output :

NEW_FIELD

temp
temp1
temp2 (abc_000003,abc_000004 has the same value. So need to remove duplicates)
temp3
123
xyz

Thanks.

Re: Remove duplicates in multiple columns (Column format - abc_000001 ) and sorting in same order with wild card?

sundareshr — Wed, 31 Aug 2016 01:58:12 GMT

How about this

base search | extract pairdelim=" " kvdelim="=" | table abc_* | untable _hidden _current_field new_field | dedup new_field

Re: Remove duplicates in multiple columns (Column format - abc_000001 ) and sorting in same order with wild card?

Kukkadapu — Wed, 31 Aug 2016 17:23:47 GMT

Wow !! This works perfect 🙂 If you don't mind, can you explain the query?

Re: Remove duplicates in multiple columns (Column format - abc_000001 ) and sorting in same order with wild card?

sundareshr — Wed, 31 Aug 2016 17:35:28 GMT

I think you first problem was that the fields were not extracted properly, hence | table abc_* did not produce any results. The extract does just that. It extract kv pairs from _raw events.

https://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Extract

The untable command converts results from a tabular format to a format similar to stats output. I added a hidden field that, which has no values. But, but default all fields that begin with `are hidden by default. So naming a field starting with_is the same as adding| fields - hidden`

https://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/Untable

Hope this helps. Feel free to ask questions if this doesn't clarify.

Re: Remove duplicates in multiple columns (Column format - abc_000001 ) and sorting in same order with wild card?

Kukkadapu — Wed, 31 Aug 2016 18:33:03 GMT

That makes sense. Thanks for your time sundareshr .