How to combine my two event count searches into one search and then do an eval function to divide the event counts?

[john122089]

Need to search for different event counts in the same sourcetype. I can do it in 2 different searches, but I need it in one.

index="ally_nga_sm" sourcetype="custom_auth_scheme_log" "invalid credentials" | stats count as IC
index="ally_nga_sm" sourcetype="custom_auth_scheme_log" "SuccessfulLogin" | stats count as SL

Then I need to do an eval function to divide the two counts IC/SL

[hortonew]

Perhaps something like:

 index="ally_nga_sm" sourcetype="custom_auth_scheme_log" ("invalid credentials" OR "SuccessfulLogin")
 | eval status=if(like(_raw, "%SuccessfulLogin%"), Successful, Invalid)
 | stats count by status

[somesoni2 · Accepted solution]

I would do like this

Fixed typos

index="ally_nga_sm" sourcetype="custom_auth_scheme_log" "invalid credentials" OR "SuccessfulLogin" | eval IC=if(searchmatch("invalid credentials"),1,0) | eval SL=if(searchmatch("SuccessfulLogin"),1,0) | stats sum(IC) as IC sum(SL) as SL

[john122089]

Error in 'eval' command: The 'search_match' function is unsupported or undefined.

I am getting the above error

[john122089]

This great! Now can it also show the IC/SL?

[john122089]

I tried adding count(eval(sum(IC)/(sum(IC)+sum(SL)))) to the end. Getting an error. Is there a better way?

[aaraneta_splunk]

Hi @john122089 - If somesoni2 ended up answering your question, please don't forget to resolve this post by clicking on "Accept" below the answer 🙂 If not, please provide him with some more feedback. Thanks!

[somesoni2]

Guessing you want to add a ratio of both. Add following to end of search

..current search.. | eval "IC/SL"=IC/(IC+SL)

IF you see the result of current search, column names being shown is IC and SL, so you're use those field names now for any further calculation.

[somesoni2]

My bad, updated the function name now.