Get first value of a field after filtering its row out

[pedropiin]

Hi everyone.

I'm really new to Splunk, so I'm confused with what seems to be a simple problem. 
I'm using "where row_num > 1" to remove the first row of my search, as I need to calculate lots of metrics based on the whole data without this specific first row. But I'm also supposed to show the value of this first row in a specific field. 

My query is of the following structure:

my_search...
| eval val1=...
| sort val1
| streamstats count as row_num
| where row_num > 1
| stats avg(...) as metric1,
  max(...) as metric2,
  count(...) as metric3
...
| fields metric1, metric2, metric3

But I also need to output the value 'x' that is specifically on field 'y' on row 1. 

How would I do this? 

Thanks in advance

[livehybrid · Accepted solution]

Hi @pedropiin 

Im not 100% sure if I'm on the right page...but nevertheless have a look to see if this helps you 🙂

Ive used some sample data inline to help build out a working query for you to work with.

| makeresults 
| eval _raw = "[{\"name\":\"SampleMetric\",\"metric\":\"42\",\"xfield\":\"Mickey Mouse\"},{\"name\":\"SampleMetric\",\"metric\":\"17\",\"xfield\":\"Donald Duck\"},{\"name\":\"SampleMetric\",\"metric\":\"36\",\"xfield\":\"Goofy\"},{\"name\":\"SampleMetric\",\"metric\":\"29\",\"xfield\":\"Minnie Mouse\"},{\"name\":\"SampleMetric\",\"metric\":\"48\",\"xfield\":\"Pluto\"}]" 
| eval events=json_array_to_mv(_raw) 
| mvexpand events 
| eval _raw=events 
| fields _raw 
| spath 
| table name metric xfield
    ``` Sample data Prep work complete ``` 
| sort metric 
| streamstats count as row_num 
| eval xfield=IF(row_num==1,xfield,NULL()) 
| filldown xfield 
| where row_num > 1 
| stats values(xfield) as xfield,
    avg(metric) as metric1,
    max(metric) as metric2,
    count(metric) as metric3 
| fields xfield metric1, metric2, metric3

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will

[pedropiin]

Hi @livehybrid 

It worked perfectly! 

Thank you so much