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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...