How to perform math on single values

[tsmithsplunk]

Hello experts. After mining this site I figure its not possible to do math on distinct vales. I've seen answers that help people munge multiple searches into one to avoid this issue. Well I have 2 distinct searches that don't have any fields in common. I only want to perform math on the counts from both.

1st search:
index=here sourcetype=this SpecialKeyword

2nd search:
index=here sourcetype=this SqlTransactionRollbackException AND CertainCommandObject

You could say its a flaw in the logs that the data I need is written in separate events. Nevertheless I'd like to take the count of the 2nd search and divide by the count of the 1st, giving me a percentage of errors for a certain type of action.

There are no fields in common between the two that differ in their values. The 2nd has a SQLSTATE of "40001" ... can that be compared to null somehow?

index=here sourcetype=this SpecialKeyword OR (SqlTransactionRollbackException AND CertainCommandObject) | stats eval(count(SQLSTATE="40001")/count(SQLSTATE=null)) as "my desired value"

I can't seem to find the right query.

[gkanapathy · Accepted solution]

There are several ways to do this, and you don't need to screw around with finding field values. You can do it the most crude way:

index=here sourcetype=this SpecialKeyword
| stats count as c_special 
| append [ search index=here sourcetype=this SqlTransactionRollbackException AND CertainCommandObject 
           | stats count as c_other]
| eval ratio=c_other/c_special

But you can refactor to make the search more efficient, which was kind of what you were trying:

index=here sourcetype=this (SpecialKeyword OR (SqlTransactionRollbackException AND CertainCommandObject))
| stats count(searchmatch("SpecialKeyword")) as c_special
        count(searchmatch("SqlTransactionRollbackException AND CertainCommandObject")) as c_other
| eval ratio = c_other/c_special

The searchmatch() function lets you just use the same search terms as before (just a substitute for the looking for a SQLSTATE value or other field), though it makes a bunch of redundancy and makes it harder to see what you're doing. But the overall search will be faster.

Another way to solve this makes use of the multisearch search command. It's arguably clearer than the above refactoring, and the good thing is it doesn't have the redundancy:

| multisearch
  [ search index=here sourcetype=this SpecialKeyword
    | eval marker="s" ]
  [ search index=here sourcetype=this SqlTransactionRollbackException AND CertainCommandObject
    | eval marker="o" ]
| stats count(eval(marker=="s")) as c_s
        count(eval(marker=="o")) as c_o
| eval r=c_o/c_s

This is pretty much what you were trying to do, except without you having to try to find a special marker field (you just create it with | eval marker=...) and without having to redundantly specify search terms (which is what i did above). You also get the improved performance of not having to dispatch two searches in sequence (which is what the version with append), as Splunk does the refactor and runs both multisearches in a single pass.

[tsmithsplunk]

I tried the multisearch approach and it works great. Pretty quick too. I tacked on a nice

| untable "" measure value

which allowed me to chart the results as a PIE.

[Ayn]

You could run your two searches using append, calculate the counts independently and then do your eval.

index=here sourcetype=this SpecialKeyword | stats count as count1 | append [search index=here sourcetype=this SqlTransactionRollbackException AND CertainCommandObject | stats count as count2] | eval value=count2/count1

...or you could do something more similar to the search you have in the end:

index=here sourcetype=this SpecialKeyword OR (SqlTransactionRollbackException AND CertainCommandObject) | stats count(eval(SQLSTATE=="40001")) as count2, count(eval(isnull(SQLSTATE))) as count1 | eval value=count2/count1