How many Users have 2 events recorded out of 3 events pulled back in script?

[JJPROSE]

I have a script:

index=idaas EventType=Start OR EventType=Pass OR EventType=SignIn | eventstats dc(UserID) as dcUser by EventType | stats count by dcUser

This shows me distinct count of UserID's for each event. However, I am now trying to know how many unique Users hit all 3 events. Basically, the main outcome is to work out how many people have started, they get a pass event but we do not record a SignIn.

Any help would be great - I did try transaction but I can only tie together 2 events and not 3.

Thanks for your help

[somesoni2]

Try like this
Below search will get all EventTypes for all users (Base Search)

index=idaas EventType=Start OR EventType=Pass OR EventType=SignIn
| values(EventType) as EventTypes by dcUser

Now adding below will give you list of users which have all three EventType entries

Base Search | where mvcount(EventTypes)=3

If you want to see " how many people have started, they get a pass event but we do not record a SignIn.", add this to base search

Base Search | where mvcount(EventTypes)=2 AND isnull(mvfind(EventTypes,"SignIn"))

[JJPROSE]

Thanks @somesoni2 i tried the above script but i get the following error message: Search Factory: Unknown search command 'values' i did try and enter stats before the values but no results where found.

[kamlesh_vaghela · Accepted solution]

Hi @JJPROSE,

Can you please try this?

Below Search will gives you userID wise status count. This will make you know about whether user event has Start, Pass OR SignIn type.

index=idaas EventType=Start OR EventType=Pass OR EventType=SignIn | | stats count(eval(EventType="Start")) as Start_count count(eval(EventType="Pass")) as Pass_count count(eval(EventType="SignIn")) as SignIn_count by UserID 

As per your need, just add where condition on status count to filter user. Please see below search.

index=idaas EventType=Start OR EventType=Pass OR EventType=SignIn | | stats count(eval(EventType="Start")) as Start_count count(eval(EventType="Pass")) as Pass_count count(eval(EventType="SignIn")) as SignIn_count by UserID | where Start_count!=0 AND Pass_count!=0 AND SignIn_count=0

My Sample Search:

| makeresults | eval EventType="Start",UserID=1 | append [ | makeresults | eval EventType="Pass",UserID=1] | append [ | makeresults | eval EventType="SignIn",UserID=1] | append [| makeresults | eval EventType="Start",UserID=2 | append [ | makeresults | eval EventType="Pass",UserID=2] ] 
| stats count(eval(EventType="Start")) as Start_count count(eval(EventType="Pass")) as Pass_count count(eval(EventType="SignIn")) as SignIn_count by UserID | where Start_count!=0 AND Pass_count!=0 AND SignIn_count=0

Thanks

[JJPROSE]

Hi @kamlesh_vaghela - this is fantastic. Thank you very much. I used the script above and it works perfectly this will help me out a lot. Thank You 🙂

[kamlesh_vaghela]

Glad to help you.

[JJPROSE]

@kamlesh_vaghela just a quick one if I wanted to timechart span=1 count how would I add this to the script - when I add this the results don't return. I am guessing because we are doing some logic with the stats eval.