topic Re: How to create eval to divide counts by auditType? in Splunk Search

How to create eval to divide counts by auditType?

Megz — Tue, 17 May 2022 15:12:15 GMT

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)

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.

Re: How to create eval to divide counts by auditType?

somesoni2 — Tue, 17 May 2022 15:26:03 GMT

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)

Re: How to create eval to divide counts by auditType?

Megz — Wed, 18 May 2022 10:50:08 GMT

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 🙂

Re: How to create eval to divide counts by auditType?

ITWhisperer — Wed, 18 May 2022 11:37:09 GMT

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)

Re: How to create eval to divide counts by auditType?

Megz — Wed, 18 May 2022 11:53:40 GMT

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.

Re: How to create eval to divide counts by auditType?

ITWhisperer — Wed, 18 May 2022 11:55:42 GMT