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
Revered Legend

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!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...