Combine a field from a previous row based on other fields and create another field

[datatan]

Here's an example data in splunk (bookstore logs):

time(ms)	id	stage	payload
1020984	aaaa-bbbb-cccc	checkout	Lord Of The Rings;
1310953	aaaa-bbbb-cccc	cart	Harry Potter;Game Of Thrones;
1340932	aaaa-bbbb-cccc	cart	Harry Potter;
1345608	dddd-eeee-ffff	cart	Splunk for Dummies;
1352093	dddd-eeee-ffff	cart	Splunk for Dummies;Java 101;
1420838	dddd-eeee-ffff	checkout	Order#999999999
1450928	aaaa-bbbb-cccc	checkout	Order #123456789

This shows 2 customers shopping for books and then buying them. The most recent cart row contains what they bought. It also contains a previous checkout from one of the customers.

I want to create a query that will return this:

time(ms)	id	stage	payload	time_spent_browsing
1420838	dddd-eeee-ffff	checkout	Splunk for Dummies;Java 101;	6485
1450928	aaaa-bbbb-cccc	checkout	Harry Potter;	29979

The payload field should contain the most recent cart row's payload with matching id

The time_spent_browsing field should be the the (most recent cart's time - the earliest cart time). The earliest cart event should be after the previous thank you event.

I hope that makes sense.

[richgalloway · Accepted solution]

See if this helps.

| transaction id startswith="cart" endswith="checkout" keeporphans=0 
| eval time_spent_browsing = tonumber(mvindex(time,1)) - tonumber(mvindex(time, 0)), time=mvindex(time,1), stage=mvindex(stage,1), payload=mvfilter(NOT match(payload,"Order\s?#"))
| table time, id, stage, payload, time_spent_browsing

 I try to avoid the transaction command, but sometimes it's the only thing that works.

[datatan]

Thank you for the quick reply. This is very close to what I need. The only problem is that it seems to calculate time spent browsing by subtracting the checkout time by the latest cart time. I don't know if it's possible but I'm trying to do this:

time	id	stage	payload
5000	abc	cart	Harry Potter;
10000	abc	checkout	Order #123123
20000	abc	cart	Lord Of The Rings;
30000	abc	cart	Lord Of The Rings;Game Of Thrones;
40000	abc	cart	Game Of Thrones;
50000	abc	checkout	Order #123131

Time spent browsing for the last checkout should be the difference of the two bolded time values. Is this possible?

Thank you very much for your help!

[to4kawa]

| stats last(eval(if(stage="checkout",'time(ms)',null())))  as time last(eval(if(stage="cart",payload,null())))  as payload last(stage) as stage by id