How to create a list of first X values based on another field's value?

[rberman]

I have a set of results for the search with id="base_metrics_search" which provide 3 panels with data.  The events each contain a bunch of question metrics and have two fields of note: question_id and is_answered, which I'd like to use to provide data for another 2 panels. An example result set would be:

question_id	is_answered
1	1
2	0
3	1
4	0
5	0
6	1
7	1
8	1
9	0
10	0
11	1
12	0
13	0
14	1
15	1

How do I find the ids of the first 5 answered and unanswered questions? The first 5 of each type could be in any order. I am hoping to use two tokens to pass these values to other panels using a multivalue or comma separated list. So for the above example, I would end up with something like:

answered_ids = 1,3,6,7,8

unanswered_ids = 2,4,5,9,10

I have searched around the doc and I haven't figured out what SPL to use to do this.

I am currently using a chained search approach using "head" but this gives me results in the first panel but none in the second:  (I'm using splunk enterprise 8.2.2)

<panel>
  <title>Top Questions</title>
  <table>
    <title>Answered Questions</title>
    <search base="general_metrics_base">
      <query>| head limit=5 (is_answered=1) | fields ...</query>
    </search>
    <option name="drilldown">none</option>
    <option name="refresh.display">none</option>
   </table>
   <table>
     <title>Unanswered Questions</title>
     <search base="general_metrics_base">
       <query>| head limit=5 (is_answered=0) | fields ...</query>
     </search>
     <option name="drilldown">none</option>
     <option name="refresh.display">none</option>
   </table>
</panel>

I'm looking into passing just the question_Ids on since I need to do further querying in those next two panels anyway.  I assume the answered questions search gets rid of events in the base_metrics_search results preventing the unanswered questions panel search from using them. Should the second panel (for the unanswered questions) of a pair of panels, both in a chained search based off the same original search, get the same original results set to process that the base_metrics_search returned to the first panel? Thanks in advance for any help you could offer!

[johnhuang · Accepted solution]

Try something like this.

Note: use list() if you care about the order. If you don't, change list() to values()

| makeresults 
| eval events="1:1,2:0,3:1,4:0,5:0,6:1,7:1,8:1,9:0,10:0,11:1,12:0,13:0,14:1,15:1" 
| eval events=SPLIT(events, ",") | mvexpand events
| rex field=events "(?<question_id>\d+)\:(?<is_answered>\d+)"
| table question_id is_answered

| dedup 5 is_answered
| eval answered_ids=CASE(is_answered=1, question_id)
| eval unanswered_ids=CASE(is_answered=0, question_id)
| stats list(unanswered_ids) AS unanswered_ids list(answered_ids) AS answered_ids
| eval unanswered_ids=MVJOIN(unanswered_ids, ",")
| eval answered_ids=MVJOIN(answered_ids, ",")