Splunk Search

How to create eval to divide counts by auditType?

Megz
Explorer

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.

Labels (4)
0 Karma
1 Solution

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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

Megz
Explorer

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 🙂 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

Megz
Explorer

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

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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)
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...