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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...