Simple Search Turned complicated. Problem with subtracting 2 values from different indexes

[singhh4]

Hey people!

So I may be a big idiot and be missing something very simple but i cant seem to figure it out.

here is my search:

index=server1
|stats avg(score) as avgscore1
|append [index=server2
     |stats avg(score) as avgscore2

|eval avgscore1=tonumber(avgscore1)
|eval avgscore2=tonumber(avgscore2)
|eval diff = avgscore1 - avgscore2
|eval change=if(diff > 0,"Down",if(diff < 0,"Up",if(diff == 0,"Same","Broken")))

No matter what I try i keep getting "Broken" even when i know the numbers are different. When i table diff, its blank. I've tried |eval diff = 5 - 7 and it works fine. Thinking that the avgscores weren't numbers, i used tonumber() so convert them but didnt work either. Any ideas?

Thank you in advanced!

[sundareshr · Accepted solution]

When you use append, splunk adds the result as rows and not columns. So in your example you have two rows, one with a col called avgscore1 and second with avgscore2 neither have both. Hence always "Broken". What you need is appendcols. Like this

 index=server1
 |stats avg(score) as avgscore1
 |appendcols [index=server2
      |stats avg(score) as avgscore2

 |eval avgscore1=tonumber(avgscore1)
 |eval avgscore2=tonumber(avgscore2)
 |eval diff = avgscore1 - avgscore2
 |eval change=case(diff > 0,"Down", diff < 0, "Up", diff == 0, "Same",1=1, "Broken")

*OR

index=server1 OR index=server2 | eval x= " " | chart avg(score) as avg by x server | fields - x | eval diff=server1-server2 | eval change = case(diff > 0,"Down", diff < 0, "Up", diff == 0, "Same",1=1, "Broken")

[singhh4]

I know i was missing something simple! Thank you for the help!