Splunk Search

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

singhh4
Path Finder

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!

0 Karma
1 Solution

sundareshr
Legend

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")

View solution in original post

sundareshr
Legend

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
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...