Splunk Search

How to prevent values from appearing twice in a single cell of a table

andres91302
Communicator

Hello Everyone I hope you are safe and sound,

I'm extracting values from events that come in a Json format and after that I want to create a Table were I can see each ID and the product thy bought from the store but I am always getting within a single cell the same value repeated two times and when I try to do a stats count then... it is also count twice...

This is my code:

index=purchase_store_x1
| rex mode=sed "s/^(?i)(?:(?!{).)+//g"
| spath
| search BodyJson.name="pdone.ok"
| rename BodyJson.product.ID as PRODUCT
| rename BodyJson.ID.CX.Unique as ID
| table ID PRODUCT
| sort -ID

and so instead of getting the ID asociaed with the product purchased I get something like this:

IDPRODUCT
31254
31254
XUI45
XUI45
54581
54581
XUI8
XUI45
47851
47851
XUIE58
XUI45


How can I just a normal table without having the same value repeated twice in the cell? THANK YOU SO MUCH for your help,

Labels (1)
1 Solution

bowesmana
SplunkTrust
SplunkTrust

@andres91302 

It looks like ID is always duplicated, but product can be different, so if that's the case

..your_search..
| eval ID=mvdedup(ID), PRODUCT=mvdedup(PRODUCT)
| table ID PRODUCT
| sort -ID

or

..your_search..
| eval ID=mvdedup(ID)
| stats values(PRODUCT) by ID
| sort -ID

use whichever suits your data better

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

@andres91302 

It looks like ID is always duplicated, but product can be different, so if that's the case

..your_search..
| eval ID=mvdedup(ID), PRODUCT=mvdedup(PRODUCT)
| table ID PRODUCT
| sort -ID

or

..your_search..
| eval ID=mvdedup(ID)
| stats values(PRODUCT) by ID
| sort -ID

use whichever suits your data better

 

andres91302
Communicator

Thank you so much! 10/10

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try mvexpand.

index=purchase_store_x1
| rex mode=sed "s/^(?i)(?:(?!{).)+//g"
| spath
| search BodyJson.name="pdone.ok"
| rename BodyJson.product.ID as PRODUCT
| rename BodyJson.ID.CX.Unique as ID
| mvexpand PRODUCT
| table ID PRODUCT
| sort -ID

 

---
If this reply helps you, Karma would be appreciated.

andres91302
Communicator

Thank you so much for your help this was excellent

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...