Splunk Search

How to remove duplicate column values in table?

the_wolverine
Champion

I have a table that contains several columns. The table looks something like this:

timestamp,region,product_number,status,count
time1,americas,12345,done,5
time2,americas,23456,fail,4
time3,emea,34567,done,3
time4,emea,56789,fail,1
time5,emea,56789,done,2

I would like to reformat it to remove the column values that are duplicated:

timestamp,region,product_number,status,count
time1,americas,12345,done,5
time2,        ,23456,fail,4
time3,emea,34567,done,3
time4,    ,56789,fail,1
time5,    ,     ,done,2
0 Karma
1 Solution

Ayn
Legend

I don't think there's a generic solution to remove "duplicates" in any field (at least I can't think of one), but if it's OK to specify fields you could achieve this with streamstats.

... | streamstats current=f last(region) as _lastregion, last(product_number) as _last_product_number | eval region=if(region==_lastregion,"",region) | eval product_number=if(product_number==_last_product_number,"",product_number) | ... (and so on)

View solution in original post

Ayn
Legend

I don't think there's a generic solution to remove "duplicates" in any field (at least I can't think of one), but if it's OK to specify fields you could achieve this with streamstats.

... | streamstats current=f last(region) as _lastregion, last(product_number) as _last_product_number | eval region=if(region==_lastregion,"",region) | eval product_number=if(product_number==_last_product_number,"",product_number) | ... (and so on)

the_wolverine
Champion

Um, it works. Turns out I had bad data. Thanks, Ayn!

0 Karma

bmacias84
Champion

have you considered using selfjoin on region?

0 Karma

the_wolverine
Champion

It doesn't produce the table I'm looking for unfortunately. What I get from your suggestion is:

timestamp,region,product_number,status,count
time1, ,12345,done,5
time2, ,23456,fail,4
time3,emea,34567,done,3
time4,emea,56789,fail,1
time5,emea, ,done,2

0 Karma
Get Updates on the Splunk Community!

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...

Splunk AppDynamics Agents Webinar Series

Mark your calendars! On June 24th at 12PM PST, we’re going live with the second session of our Splunk ...

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...