topic How to remove duplicate values only in one column out of four columns? in Splunk Search

How to remove duplicate values only in one column out of four columns?

im_bharath — Tue, 22 Nov 2022 14:24:55 GMT

Hello All,

When using the "stats count by column1, column2, column3, column4"

I get the below result

Existing table :

column1	column2	column3	column4
XXXXXXXX	YYYYY	A	123
XXXXXXXX	YYYYY	B	123
XXXXXXXX	YYYYY	C	123
XXXXXXXX	YYYYY	D	123
XXXXXXXX	YYYYY	E	123

Where as I need this result :

column1	column2	column3	column4
XXXXXXXX	YYYYY	A	123
		B
		C
		D
		E

Could somebody please help me with the query?

Thanks,

Re: Remove duplicate values only in one column out of four columns.

kamlesh_vaghela — Mon, 21 Nov 2022 12:47:18 GMT

@im_bharath

Can you please try this?

YOUR_SEARCH | streamstats count by column1 column2 column4 | eval column1=if(count==1,column1,""), column2=if(count==1,column2,""),column4=if(count==1,column4,"") |fields - count

My Sample Search :

| makeresults | eval _raw="column1 column2 column3 column4 XXXXXXXX YYYYY A 123 XXXXXXXX YYYYY B 123 XXXXXXXX YYYYY C 123 XXXXXXXX YYYYY D 123 XXXXXXXX YYYYY E 123 1XXXXXXXX 1YYYYY A 1234 1XXXXXXXX 1YYYYY B 1234 1XXXXXXXX 1YYYYY C 1234 1XXXXXXXX 1YYYYY D 1234 1XXXXXXXX 1YYYYY E 1234" | multikv forceheader=1 | table column1 column2 column3 column4 | rename comment as "upto this is sample data" | streamstats count by column1 column2 column4 | eval column1=if(count==1,column1,""), column2=if(count==1,column2,""),column4=if(count==1,column4,"") |fields - count

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Remove duplicate values only in one column out of four columns.

gcusello — Mon, 21 Nov 2022 12:48:44 GMT

Hi @im_bharath,

please try something like this:

<your_search> | stats count AS column4 values(column2) AS column2 values(column3) AS column3 BY column1

Ciao.

Giuseppe

Re: Remove duplicate values only in one column out of four columns.

im_bharath — Mon, 21 Nov 2022 13:16:26 GMT

Hey @gcusello,

That worked.. thanks a lot.

Re: Remove duplicate values only in one column out of four columns.

im_bharath — Mon, 21 Nov 2022 13:17:24 GMT

Hey @kamlesh_vaghela,

I will try and let you know if this works as well or not..

Re: Remove duplicate values only in one column out of four columns.

im_bharath — Tue, 22 Nov 2022 09:34:07 GMT

Hey @kamlesh_vaghela ,

I have tried below SPL query as suggested by you,

| multikv forceheader=1
| table column1	column2	column3	column4
| rename comment as "upto this is sample data"
| streamstats count by column1 column2	column4
| eval column1=if(count==1,column1,""), column2=if(count==1,column2,""),column4=if(count==1,column4,"")
|fields - count

so i got below result similar to the other post, but we were getting the multiple values for column3 like 1,1,2,3,3,3,3,4,5,5,5,6,6, instead of 1,2,3,4,5,6

So i have added the "dedup column3" to your search and it removed the duplicate results.

Thank you for the solution.