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!

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 ...