Why does eval with appended search give me a scope/visibility problem?

[dadkinson]

The background to this is that I'm trying to set an alert which is normalized, ie. the alert should only fire if the ratio of exception events to successful events over a 5 min period exceeds some pre-determined value. But stepping back several places... I started using an appended search with eval to calculate the ratio, thus:

"XXX targeting service enabled" | stats count as ALL | append [search "exception calling XXX targeting" | stats count as EXC] | eval A_ratio=ALL/10 | eval E_ratio=EXC/10 | eval ratio=ALL/EXC

this gives me accurate values for ALL, EXC, A_ratio, and E_ratio, but nothing at all for ratio. Now, given that ALL and EXC are visible outside of the appended search and can be divided by a constant, I would have thought the ratio calculation would also be fine, but it isn't. (NOTE: A_ratio and E_ratio mean nothing but I used them to debug the problem).

This is borne out by the fact that this simpler, test, search works fine:
"XXX targeting service enabled" | stats count as ALL dc(host) as EXC | eval A_ratio=ALL/10 | eval E_ratio=EXC/10 | eval ratio=ALL/EXC

in this case I do get an accurate ratio value back.

The mathematical operator doesn't matter and I even tried subtracting logs, but of course the problem appears to be one of visibility, not operation or syntax. So why are the variables ALL and EXC visible individually yet not visible to each other?

More to the point, can anyone help with creating a normalized alert?

Thanks.

[somesoni2 · Accepted solution]

Using append function, the result/rows of second search gets appended to first search results. If both results have different field names, each row after append will not have both the field values. The eval expects all the operand fields to be present in same rows (which will not be the case with your append search).

e.g The append result in your case will be like this

               ALL        EXC
row1 --  SomeValue    null()/blank
row2 --  null()/blank  SomeValue

Based on your calculation you should be using appendcols instead of append, like this. This will just add the columns of one result to other, row by row

"XXX targeting service enabled" | stats count as ALL | appendcols [search "exception calling XXX targeting" | stats count as EXC] | eval A_ratio=ALL/10 | eval E_ratio=EXC/10 | eval ratio=ALL/EXC

The output will be like this

            ALL        EXC
row1 --  SomeValue    SomeValue

In many cases, depending upon from what sources (index/sourcetype/source/hosts etc) you're getting the data, it's possible to combine both searches at base search level itself. Since you're just running search based on some string, it's not very efficient. Assuming you would be able to add some metadata field fields (index/sourcetype/source/host), your query can be combined like this

(search1 metadata fields- index=foo1 sourcetype=bar1 "XXX targeting service enabled" ) OR (search2 metadata fields- index=foo2 sourcetype=bar2 "exception calling XXX targeting" ) 
| eval ALL=if(searchmatch("XXX targeting service enabled"),1,0) | eval EXC=if(searchmatch("exception calling XXX targeting"),1,0)
| stats sum(ALL) as ALL sum(EXC) as EXC | eval A_ratio=ALL/10 | eval E_ratio=EXC/10 | eval ratio=ALL/EXC

[dadkinson]

Awesome! Thanks so much, this works perfectly and gives me the ratio number I'm looking for.

Now I just have to figure out how to alert based on that value, though that should be easier...

[sundareshr]

Here's what's probably happening with your query. The first search (main search for all) has a field called ALL, you then append new events that match a specific criteria. These appended events have a field called EXC. Note, no event has both ALL and EXC. Hence rest of your search returns incorrect results. You can achieve your desired results without using append/sub-search. Try it like this

"XXX targeting service enabled" | eval errors=match(_raw, "exception calling XXX targeting"), 1, 0) | stats count as ALL sum(errors) as EXC | eval ratio=ALL/EXC

[dadkinson]

Thanks Sundaresh. I had to change your search a bit but there's still a problem:

"XXX targeting service enabled" | eval errors=if(match(_raw, "exception calling XXX targeting"), 1, 0) | stats count as ALL sum(errors) as EXC | eval ratio=ALL/EXC

I think you're assuming that the two search strings could exist in the same event, when in fact they don't. There are some events that contain "XXX targeting service enabled" and some events that contain "exception calling XXX targeting" but it's the separate count of the two that I need.
If I group events into transaction streams (don't especially want to do) then the streams will contain either:
(a) "XXX targeting service enabled" OR
(b) "XXX targeting service enabled" and "exception calling XXX targeting"

which is why I figured this would need some kind of appended search or subsearch.

(On a technical point, you said "Hence rest of your search returns incorrect results.", but in fact it doesn't completely return the incorrect result I'd expect. I'd still like to know why splunk is able to use both ALL and EXC when dividing them by 10 but cannot divide them by each other. If EXC is not visible outside of the appended search then don't let me divide it by 10. This is a question for splunk though)