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:
ID | PRODUCT |
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,
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
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
Thank you so much! 10/10
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