Splunk Search

How can I get box and whisker plot values?

caffein
Path Finder

I don't have any problem getting the Q1, Median, Q3, and IQR values using percX(), median and eval. What I'm having trouble with is separating the outliers from the rest of the data. I'm trying to do something like this:

index=foo 
|stats p25(bar) as q1, p50(bar) as bmed, p75(bar) as q3
|eval irq=q3-q1
|eval lfence=q1-1.5*iqr
|eval ufence=q3+1.5*iqr
|search bar>=lfence bar<=ufence
|stats min(bar) as lfence, max(bar) as ufence
|table lfence,q1,bmed,q3,ufence,iqr

However this returns no results. Does anyone know how I can get these values? I know outlier uses IQR to remove outliers, but if I used that first it would change the median, q1, q3 and iqr.

Tags (2)
1 Solution

araitz
Splunk Employee
Splunk Employee

You have at least three problems with your search:

  • When comparing two fields, you cannot use search - you must use where. search always assumes that the value to the right of the comparator is a string literal, whereas where will treat the right-hand side as a field.
  • In your comparison, you are attempting to compare lfence and ufence to bar - however, bar is no longer a valid field in the result set after you invoke the stats command. Take a look at the tabular output of just the initial search and stats to see what I mean.
  • You need to use an explicit AND in your where clause (as well as if you were using a search clause)

You probably want to do something like this:

index=foo 
|eventstats p25(bar) as q1, p50(bar) as bmed, p75(bar) as q3
|eval irq=q3-q1
|eval lfence=q1-1.5*irq
|eval ufence=q3+1.5*irq
|where bar>=lfence AND bar<=ufence
|stats min(bar) as lfence max(bar) as ufence values(q1) as q1 values(bmed) as bmed values(q3) as q3 values(irq) as irq

View solution in original post

araitz
Splunk Employee
Splunk Employee

You also have a typo - irq vs. iqr. See my answer below, I tested this on splunk's _internal index using "instantaneous_eps" rather than "bar".

0 Karma

araitz
Splunk Employee
Splunk Employee

You have at least three problems with your search:

  • When comparing two fields, you cannot use search - you must use where. search always assumes that the value to the right of the comparator is a string literal, whereas where will treat the right-hand side as a field.
  • In your comparison, you are attempting to compare lfence and ufence to bar - however, bar is no longer a valid field in the result set after you invoke the stats command. Take a look at the tabular output of just the initial search and stats to see what I mean.
  • You need to use an explicit AND in your where clause (as well as if you were using a search clause)

You probably want to do something like this:

index=foo 
|eventstats p25(bar) as q1, p50(bar) as bmed, p75(bar) as q3
|eval irq=q3-q1
|eval lfence=q1-1.5*irq
|eval ufence=q3+1.5*irq
|where bar>=lfence AND bar<=ufence
|stats min(bar) as lfence max(bar) as ufence values(q1) as q1 values(bmed) as bmed values(q3) as q3 values(irq) as irq

caffein
Path Finder

YES! That did the trick. Thanks. I'm not that familiar with eventstats, and where, so I'll have to spend some time reading up on them.

0 Karma

caffein
Path Finder

I wish it were that simple, but that was just a typo. Changing select to search doesn't help at all, and I still get no results back. If I remove the last 3 lines I can get the q1,median,q3 and iqr though.

0 Karma

araitz
Splunk Employee
Splunk Employee

Perhaps you mean | search bar>=lfence ... rather than | select...

Unless select is some brand new or custom search command I am unaware of, I think that is your problem.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...