Splunk Search

how to do search median

New Member


Ihave a question

this is input

date       item   field_1     field_2  field_3
2016/01/01   x       1         2         3
2016/01/01   y       4         5         6

this I want the output

 date      item  median_field
 2016/01/01   x       2       
 2016/01/01   y       5     


Tags (1)
0 Karma
1 Solution


... | stats median(field_*) as median by date item

View solution in original post


... | stats median(field_*) as median by date item


Nice. Assuming that the field names match a mask, that's much more succinct, and it has the feature that it will correctly calculate the median if there are multiple records for a time and item combination.

0 Karma


Here's a run-anywhere example.

| makeresults
| eval mydata="2016/01/02,x,1,2,3 2016/01/02,y,6,4,5"
| makemv mydata | mvexpand mydata
| makemv delim="," mydata
| eval _time = strptime(mvindex(mydata,0),"%Y/%m/%d")
| eval item = mvindex(mydata,1)
| eval field1 = mvindex(mydata,2)
| eval field2 = mvindex(mydata,3)
| eval field3 = mvindex(mydata,4)
| table _time item field1 field2 field3
| rename COMMENT as "The above just generates test data"

| rename COMMENT as "We put together all the key fields we want to keep, in a single field."
| eval mykeystuff = _time."!!!!".item

| rename COMMENT as "We keep our key field, and all the numeric fields we want the median from."
| table mykeystuff field*

| rename COMMENT as "We untable to put the field names into field, and the field values into value, then calculate our median, ignoring the field names"
| untable mykeystuff field value
| stats median(value) as mymedian by mykeystuff

| rename COMMENT as "Now we unpack our key fields again"
| makemv delim="!!!!" mykeystuff
| eval _time = mvindex(mykeystuff,0)
| eval item = mvindex(mykeystuff,1)

| rename COMMENT as "And kill the combined key field, now that it's done its duty"
| table _time item mymedian

updated to use delim="!!!!" in case the item names have any spaces in them.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...