How to create eval to divide counts by auditType?

[Megz]

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.

[somesoni2]

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)

[Megz]

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 🙂 

[ITWhisperer]

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)

[Megz]

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.

Megz_0-1652874813903.png

[ITWhisperer · Accepted solution]

| 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)