Splunk Search

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

rberman
Path Finder

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!

Labels (1)
Tags (2)
0 Karma
1 Solution

johnhuang
Motivator

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, ",")

 

View solution in original post

johnhuang
Motivator

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, ",")

 

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 ...