Getting Data In

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

JJPROSE
Engager

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

Tags (1)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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"))
0 Karma

JJPROSE
Engager

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.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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
Engager

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 🙂

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Glad to help you.

0 Karma

JJPROSE
Engager

@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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...