Table showing when a a user started an action but didn't finish it with the first and last event times

[johnansett]

Hey guys,

Looking for some help with a search. When a user starts first logs into an application to on board themselves we see an event
"User Started Onboarding"

When a user has successfully completed onboarding we see a success event
"User Successfully Onboarded"

In most cases this process should take no longer than 1 hour and if there is no success event there was likely an error by the user or the system.

I have built a search which will table the users who have this issue:

sourcetype=onboardapp "User Started Onboarding" NOT
[search sourcetype=onboardapp "User Onboarded Successfully"
|dedup username]
| table username

Which works fine and provides me a list of these users.

What I want to do is include when we saw the first event and the last event, I can include something like this

| stats count, earliest(_time) AS "First Event", latest(_time) as "Last Event" by username

But the times are the same, so it's only looking at the first event I assume. How can I create a table with:
Username, First Event, Last Event

For bonus points, there are other events which are indexed as between the Start and Success event which include other useful information, such as IP address (although not always), this is a field "ip="
How can I search on the username over the first and last period event for event's that include "ip=*" and then include it in the table if it exists:
Username, ip, First Event, Last Event

Any help appreciated!

[elliotproebstel]

This search should generate the table with users who never successfully onboarded (assuming the "success" message appears in a field called msg_field - so customize that to match your environment)

sourcetype=onboardapp 
| eval successful=if(msg_field="User Onboarded Successfully", 1, 0) 
| eventstats max(successful) AS successful BY username 
| where successful=0 
| stats values(ip) AS IP, earliest(_time) AS "First Event", latest(_time) AS "Last Event" BY username 

You were right in your post that the approach you'd been taking was first scoping the search down to only the initial event for each user - eliminating the possibility to gather the IP field or find the time of a user's last event.

[johnansett]

I like this, but the problem is that there are a lot of events where the success message is absent... these are fine usually but it's only an issue where it's absent AND they've started to process (which is indicated by the first event "User Started Onboarding"). So I need to find this first and then look for the absence of the success message.

[elliotproebstel]

Ahh, certainly! In that case, here's a revision:

sourcetype=onboardapp 
 | eval started_onboard=if(msg_field="User Started Onboarding", 1, 0)
 | eval successful=if(msg_field="User Onboarded Successfully", 1, 0) 
 | eventstats max(successful) AS successful, max(started_onboard) AS started_onboard BY username 
 | where successful=0 AND started_onboard=1
 | stats values(ip) AS IP, earliest(_time) AS "First Event", latest(_time) AS "Last Event" BY username 

[harsmarvania57]

Hi @johnansett,

Can you please try this query ?

1. This query is to find out if any user didn't completed User Onboarded Successfully and there is some issue

   sourcetype=onboardapp | transaction keepevicted=true Username startswith="User Started Onboarding" endswith="User Onboarded Successfully" | where closed_txn=0 | dedup Username | table Username
   

2. To find out earliest and latest time for users who completed User Onboarded Successfully, in this case you can use below query.

   sourcetype=onboardapp | transaction keepevicted=true Username startswith="User Started Onboarding" endswith="User Onboarded Successfully" | where closed_txn=1 | dedup Username | eval latesttime_ep=_time + duration | eval earliesttime=strftime(_time, "%d-%m-%Y %H:%M:%S"), latesttime=strftime(latesttime_ep, "%d-%m-%Y %H:%M:%S")| stats values(earliesttime) AS "First Event" values(latesttime) AS "Last Event" values(ip) AS ip by Username
   

I hope this helps.

Thanks,
Harshil

[johnansett]

Thanks, I tried this, but the results were off, it seemed to have issues with the startswith and endswith. I did have a transaction search working but was trying to avoid using transaction. I ultimately went with this search:
sourcetype=onboardapp
| transaction maxspan=1h user
| search ("started*" AND NOT "successful")
| eval durationstr=tostring(duration,"duration")
| dedup use
| table use, durationstr, _time
| rename user AS "UserName", durationstr AS "Duration between starting and last event", _time as "Time of last event"
| convert timeformat="%H:%M:%S" ctime("Time of last event")

Thanks!