Getting Data In

How to group events into interactions for analysis for JSON events indexed into Splunk?

Cheetah05
Engager

I have a series of differently-shaped JSON events indexed into Splunk (as JSON). They have a correlation id to link the events into "interactions".

Example events that form an interaction:

{ "correlationId": 1, "type": "start", "qty": 10, "product": "product-1", "client": "client-1" }
{ "correlationId": 1, "type": "quote", "price": 100 }
{ "correlationId": 1, "type": "quote", "price": 101 }
{ "correlationId": 1, "type": "end", "buy": "true , "qty": 1, "price": 101 }

Not every interaction will be complete, there might not be "quote" or "end" types.

Imagine I'm looking for every "interaction", that has an end which is buy, last price > 10, extracting the fields: start-qty, end-qty, price, product, client

The fields "price" and "qty" have different meanings depending on which event type it belongs to, so I need some way to rename these events before I do my search!

Is the idea to flatten it into one record by naming each field manually?

I could write something like (might be a few syntactic errors):

... | eventstats latest(price) AS BuyPrice latest(qty) As BuyQty BY correlationId | where type="start" | table correlationId, product, client, buy, BuyPrice, BuyQty

This is flattening everything onto the "start" type and using that record to build my results table. Is this the correct way of handling this? I'm conscious of the fact this will be probably doing multiple iterations of the data, when infact you could probably do just one iteration to build the result set.

How do I handle the fact that technically the "BuyPrice" column could have a way from a "quote" which was never bought? or what if I wanted to introduce the start type "qty" aswell?

Also - is it possible, once I've built my query, to give it an alias that I can use as my base search result set so I don't have to keep writing the query?

0 Karma
1 Solution

niketn
Legend

[UPDATED ANSWER]
Please try the following which will perform an average of quotes as well and give you remaining details (assuming multiple quotes and only one start and end type per correlationId) :

| makeresults 
| eval data="{\"correlationId\": 1, \"type\": \"start\", \"qty\": 10, \"product\": \"product-1\", \"client\": \"client-1\" }; { \"correlationId\": 1, \"type\": \"quote\", \"price\": 100 }; { \"correlationId\": 1, \"type\": \"quote\", \"price\": 101 }; { \"correlationId\": 1, \"type\": \"end\", \"buy\": \"true\", \"qty\": 1, \"price\": 101 };{\"correlationId\": 2, \"type\": \"start\", \"qty\": 5, \"product\": \"product-2\", \"client\": \"client-2\" }; { \"correlationId\": 2, \"type\": \"quote\", \"price\": 120 }; { \"correlationId\": 2, \"type\": \"quote\", \"price\": 80 }; { \"correlationId\": 2, \"type\": \"end\", \"buy\": \"true\", \"qty\": 2, \"price\": 110 }" 
| makemv data delim=";" 
| mvexpand data 
| rename data as _raw 
| spath 
| eval {type}:price"=price , {type}:qty"=qty
| stats avg(quote:price) as AvgQuote values(end:price) as BuyPrice values(start:qty) as start:qty values(end:qty) as end:qty values(client) as client values(product) as product by correlationId

@Cheetah05, are you interested only in start and end types? You can try the following:

<yourBaseSearch> type="start" OR type="end" correlationId=*
| stats latest(price) AS BuyPrice latest(qty) As BuyQty latest(product) as product latest(client) as client latest(buy) as buy BY correlationId 
| table correlationId, product, client, buy, BuyPrice, BuyQty

Following is run anywhere example based on sample data provided:

| makeresults 
| eval data=" { \"correlationId\": 1, \"type\": \"start\", \"qty\": 10, \"product\": \"product-1\", \"client\": \"client-1\" }; { \"correlationId\": 1, \"type\": \"quote\", \"price\": 100 }; { \"correlationId\": 1, \"type\": \"quote\", \"price\": 101 }; { \"correlationId\": 1, \"type\": \"end\", \"buy\": \"true\", \"qty\": 1, \"price\": 101 }" 
| makemv data delim=";" 
| mvexpand data 
| rename data as _raw 
| spath 
| search type="start" OR type="end" 
| stats latest(price) AS BuyPrice latest(qty) As BuyQty latest(product) as product latest(client) as client latest(buy) as buy BY correlationId 
| table correlationId, product, client, buy, BuyPrice, BuyQty
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Cheetah05
Engager

@niketnilay, for some reason I am not allowed to comment so I can on reply in the form of an answer...

Whilst I get the sentiment, not the answer I was looking for (perhaps my fault for the wording of the question). I may still want say the avg(price) from the quote type or the start type qty.

0 Karma

niketn
Legend

@Cheetah05, please try the updated answer below. I am surprised, usually answers go for moderation but comment should not be blocked.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

[UPDATED ANSWER]
Please try the following which will perform an average of quotes as well and give you remaining details (assuming multiple quotes and only one start and end type per correlationId) :

| makeresults 
| eval data="{\"correlationId\": 1, \"type\": \"start\", \"qty\": 10, \"product\": \"product-1\", \"client\": \"client-1\" }; { \"correlationId\": 1, \"type\": \"quote\", \"price\": 100 }; { \"correlationId\": 1, \"type\": \"quote\", \"price\": 101 }; { \"correlationId\": 1, \"type\": \"end\", \"buy\": \"true\", \"qty\": 1, \"price\": 101 };{\"correlationId\": 2, \"type\": \"start\", \"qty\": 5, \"product\": \"product-2\", \"client\": \"client-2\" }; { \"correlationId\": 2, \"type\": \"quote\", \"price\": 120 }; { \"correlationId\": 2, \"type\": \"quote\", \"price\": 80 }; { \"correlationId\": 2, \"type\": \"end\", \"buy\": \"true\", \"qty\": 2, \"price\": 110 }" 
| makemv data delim=";" 
| mvexpand data 
| rename data as _raw 
| spath 
| eval {type}:price"=price , {type}:qty"=qty
| stats avg(quote:price) as AvgQuote values(end:price) as BuyPrice values(start:qty) as start:qty values(end:qty) as end:qty values(client) as client values(product) as product by correlationId

@Cheetah05, are you interested only in start and end types? You can try the following:

<yourBaseSearch> type="start" OR type="end" correlationId=*
| stats latest(price) AS BuyPrice latest(qty) As BuyQty latest(product) as product latest(client) as client latest(buy) as buy BY correlationId 
| table correlationId, product, client, buy, BuyPrice, BuyQty

Following is run anywhere example based on sample data provided:

| makeresults 
| eval data=" { \"correlationId\": 1, \"type\": \"start\", \"qty\": 10, \"product\": \"product-1\", \"client\": \"client-1\" }; { \"correlationId\": 1, \"type\": \"quote\", \"price\": 100 }; { \"correlationId\": 1, \"type\": \"quote\", \"price\": 101 }; { \"correlationId\": 1, \"type\": \"end\", \"buy\": \"true\", \"qty\": 1, \"price\": 101 }" 
| makemv data delim=";" 
| mvexpand data 
| rename data as _raw 
| spath 
| search type="start" OR type="end" 
| stats latest(price) AS BuyPrice latest(qty) As BuyQty latest(product) as product latest(client) as client latest(buy) as buy BY correlationId 
| table correlationId, product, client, buy, BuyPrice, BuyQty
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
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 ...