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
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
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"))
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.
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
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 🙂
Glad to help you.
@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.