Getting Data In

How to omit duplicate values of a column

harish_ka
Communicator

Hi i have a report as below,

Col A -----Col B--------Col C-----Col D
-------------------------------------------------
ABCD-----US  ----------323------12
XYZZ------AM-----------323------11
SADF-----SD------------323-------88

i need to remove the duplicate values of col C, i need to show only once in the first row like below,

Col A -----Col B--------Col C-----Col D
-------------------------------------------------
ABCD-----US  ----------323------12
XYZZ------AM----------------------11
SADF-----SD-----------------------88

Here the report is grouped by ColA and ColB...

Can anyone help me on this....

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Based on your current update, this should work.

your base search giving "Col C" and "Col D" group By "Col A" and "Col B"  | streamstats count as sno by "Col C"| eventstats max(sno) as max count as total  | eval "Col C"=if(total>max,'Col C', if(sno=1,'Col C', null())) | fields - sno,max,total

View solution in original post

chimell
Motivator

Hi

Try this search code I ‘m sure that it will be work well

replace omitduplication.csv by a source name of your file

source="omitduplication.csv "| stats count by colA ,colB, colD | appendcols[search source="omitduplication.csv" | dedup colC] | table colA colB colC colD
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Based on your current update, this should work.

your base search giving "Col C" and "Col D" group By "Col A" and "Col B"  | streamstats count as sno by "Col C"| eventstats max(sno) as max count as total  | eval "Col C"=if(total>max,'Col C', if(sno=1,'Col C', null())) | fields - sno,max,total

rsathish47
Contributor

base search | streamstats count as sno by Col_C | eval Col_C=if(sno=1,Col_C,"") | fields - sno

0 Karma

harish_ka
Communicator

Thank you so much somesoni2 and rsatish47 🙂
its working as i expected...

0 Karma

somesoni2
SplunkTrust
SplunkTrust

What should be the output if report is like this.

 Col A -----Col B--------Col C-----Col D
-------------------------------------------------
ABCD-----US ----------323------12
XYZZ------AM-----------323------11
SADF-----SD------------323-------88
PQRS------PM-----------999------11
QWER-----GB------------323-------88
0 Karma

harish_ka
Communicator

The column C values remains the same...
if i have 5 rows..all values of Col C will be 323

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...