Hi - I am a relatively novice Splunk user.
I am looking at implict vs explicit audit events and looking to do a calculation based on a count of these two events. I was trying to write an eval but wasn't getting anywhere
This is my search (redacted)
| multisearch
[| search auditSource=SOURCE auditType=TYPE 1 | regex tags.path=PATH ]
[| search auditSource=SOURCE auditType=TYPE2]
| stats dc(SESSIONS) as Total by auditType
So, now I have a count of the sessions in both audit types, where unique sessions in TYPE1 are journey starts, and unique sessions in TYPE2 are completions.
I want to calculate the completion rate so essentially what I need is the distinct session count in TYPE1 divided by the distinct session count in TYPE2.
p.s. I should note the audit sources for both are the same, and there are no other unique fields I can use instead.
| multisearch
[| search auditSource=SOURCE auditType=TYPE 1 | regex tags.path=PATH ]
[| search auditSource=SOURCE auditType=TYPE2]
| stats dc(eval(if('auditType'="TYPE1",'SESSIONS',null()))) as Starts dc(eval(if('auditType'="TYPE2",'SESSIONS',null()))) as Ends
| eval PercentCompletion=round(Ends*100/Starts,2)
Give this a try
| multisearch
[| search auditSource=SOURCE auditType=TYPE 1 | regex tags.path=PATH ]
[| search auditSource=SOURCE auditType=TYPE2]
| stats dc(eval(if(auditType="TYPE1",SESSIONS,null()))) as Starts dc(eval(if(auditType="TYPE2",SESSIONS,null()))) as Ends by auditType
| eval PercentCompletion=round(Ends*100/Starts,2)
Hi Somesoni
I've plugged that in but I get the following error (subbed audit type and session field)
Error in 'stats' command: The eval expression for dynamic field 'eval(if(auditType=TYPE1,SESSIONS,null()))' is invalid. Error='Typechecking failed. '-' only takes numbers.'
I tried replacing the null() with 0 instead to see if that kicked it in but no luck 😞
Thanks for replying 🙂
From the error message it looks like your real field names have "-" in. If this is the case, enclose the field name in single quotes
| multisearch
[| search auditSource=SOURCE auditType=TYPE 1 | regex tags.path=PATH ]
[| search auditSource=SOURCE auditType=TYPE2]
| stats dc(eval(if('auditType'="TYPE1",'SESSIONS',null()))) as Starts dc(eval(if('auditType'="TYPE2",'SESSIONS',null()))) as Ends by 'auditType'
| eval PercentCompletion=round(Ends*100/Starts,2)
Hi @ITWhisperer
This works for doing the counting! Thank you so much
The issue now is calculating the % as there are null values in the table. I tried adding in a where clause to the end specifying where Starts>0 and Completions>0 but this doesn't return any results.
p.s. I know the completions are more than the starts just now as this is in a testing environment.
| multisearch
[| search auditSource=SOURCE auditType=TYPE 1 | regex tags.path=PATH ]
[| search auditSource=SOURCE auditType=TYPE2]
| stats dc(eval(if('auditType'="TYPE1",'SESSIONS',null()))) as Starts dc(eval(if('auditType'="TYPE2",'SESSIONS',null()))) as Ends
| eval PercentCompletion=round(Ends*100/Starts,2)