Getting Data In

Find time difference between two events with specific condition

jamie0510
Engager

So i have numerous logs regarding user accessing app to order food for delivery.
based on the session id, and user id, I'm able to find the first and last timestamp of each session and calculate the duration of it.
However, I also want to calculate the duration between user firstly access the app and the moment the user places order.
basically each step the users engages with the app, there's a specific API for it. so the moment the user places order , there's field called route_path: API/place_order. I simply want to find out the timestamp where user placed order using this route_path field and find difference, anyone could help? appreciate it.

the current query only finds the first and last timestamp for each session.

index="some jason file" stats earliest(_time) as first,latest(_time) as last values(user_id) as user_id by session_id | convert ctime(first) as First ctime(last) as Last |eval duration=last-first | eval difference=strftime(duration,"%m/%d-%Y %H:%M:%S") | eval entire_session_duration=tostring(duration, "duration") | eval entire_session_time = replace(entire_session_duration,"(?:()+)?0?(\d+):0?(\d+):0?(\d+)"," \2h \3m \4s") | table user_id user_id session_id First Last entire_session_duration entire_session_time | search session_id!=""

0 Karma
1 Solution

to4kawa
Ultra Champion

UPDATE:(except route_path="api/place_order")

index="some json file"
| reverse
| streamstats current=f count(eval(route_path="api/place_order")) as session by user_id session_id
| stats range(_time) as duration min(_time) as First max(eval(if(route_path!="api/place_order",_time,NULL))) as Last by user_id session_id session
| eval entire_session_duration=tostring(duration, "duration") 
| eval entire_session_time = replace(entire_session_duration,"(?:()+)?0?(\d+):0?(\d+):0?(\d+)"," \2h \3m \4s") 
| convert ctime(First) ctime(Last)
| table user_id session_id First Last entire_session_duration entire_session_time 

Hi,@jamie0510
How about this?

View solution in original post

to4kawa
Ultra Champion

UPDATE:(except route_path="api/place_order")

index="some json file"
| reverse
| streamstats current=f count(eval(route_path="api/place_order")) as session by user_id session_id
| stats range(_time) as duration min(_time) as First max(eval(if(route_path!="api/place_order",_time,NULL))) as Last by user_id session_id session
| eval entire_session_duration=tostring(duration, "duration") 
| eval entire_session_time = replace(entire_session_duration,"(?:()+)?0?(\d+):0?(\d+):0?(\d+)"," \2h \3m \4s") 
| convert ctime(First) ctime(Last)
| table user_id session_id First Last entire_session_duration entire_session_time 

Hi,@jamie0510
How about this?

jamie0510
Engager

thanks for the effort, so I want to extract the time not count for the route_path="api/place_order", is there a relative time command i can use ?

0 Karma

anmolpatel
Builder

explore the transaction command using the session_id.
alternatively, you can use stats range(_time) by session_id

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Firs things first, you should rewrite the query to include the steps. You should add a stats command by <said field>. This will give you the time for each action. Once you have this in a tabular format, you can than do another stats on the data to find the difference in timestamp between the last action and first action which would give you the result you're looking for

0 Karma

jamie0510
Engager

thanks i will look into.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Just add the filters in your base search for specific API calls. E.g. index="some jason file" api="first_access_api_name" OR api="plac_order_api_name"

0 Karma

jamie0510
Engager

thanks, the fact is the first access api name varies as the user may open the app from different sources, such as text msg, app redirect, direct openning app, etc. so I couldn't specify the api for first access.

0 Karma

to4kawa
Ultra Champion
such as text msg, app redirect, direct openning app, etc

In the log, is written up?

0 Karma

jamie0510
Engager

each of those are indicated by specific API name. I thought about getting timestamp by include all possible API names upon first access. However, some APIs have multiple occurrences in a session. not like api/place_order, which will only occur once in a session, API such as API/shop etc may occur multiples times during a session because user click around here and there leading to duplicate logs basically. Thanks.

here's the example for better illustration.
https://gyazo.com/c123c1fe28b169ef8fc93cce60516828

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