topic Re: How to group events into interactions for analysis for JSON events indexed into Splunk? in Getting Data In

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

Cheetah05 — Sun, 27 May 2018 10:29:13 GMT

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?

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

niketn — Sun, 27 May 2018 11:45:41 GMT

[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

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

Cheetah05 — Mon, 28 May 2018 07:35:21 GMT

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

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

niketn — Mon, 28 May 2018 11:55:31 GMT

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