How to define results from an initial search as a variable for other searches?

[BWhisler2015]

Hello,

I am working on a search and eventually a dashboard that displays the count per field based on the characteristics or results from an initial search. I'm pulling all my data into Splunk using the DB connect app.

For example, let's assume our entire data set consists of three fields (ID, eye color, and hair color) and four events.
Silly example, but bare with me.

ID          Eye Color          Hair Color
1           Blue               Brown
2           Green              Brown
3           Blue               Black
4           Brown              Brown

Now we want to find the count of IDs for each field based off the characteristics of a specified ID. In other words, if we choose ID 1 as our input, then with some Splunk magic the dashboard will spit out the following table (Note: I am not worried too much about formatting at this point).

Eye Color      Eye Color Count      Hair Color     Hair Color Count
Blue                  2               Brown               3

Does anyone have any ideas how I can go about writing this search? I originally thought I could pull in two sets of data (all the data and the row I want to pivot off of) and then either append or join the two sets together.

Thanks in advance, please let me know if my questions makes sense or if I need to clear something up.

[woodcock]

Once you have a selection for eye in selected_eyes and hair in selected_hair, and your data uses fields Eye Color and Hair Color, you can do it like this

... | stats count(eval($Eye Color$=selected_eyes)) AS "Eye Color Count" count(eval($Hair Color$=selected_hair)) AS "Hair Color Count" | rename selected_eyes AS "Eye Color" selected_hair AS "Hair Color" | table "Eye Color" "Eye Color Count" "Hair Color" "Hair Color Count"

[martin_mueller · Accepted solution]

The easiest to write would be something like this:

  sourcetype=your_data [search sourcetype=your_data ID=1 | table eye_color] | stats count as "Eye Color Count" by eye_color
| appendcols [search sourcetype=your_data [search sourcetype=your_data ID=1 | table hair_color] | stats count as "Hair Color Count" by hair_color

That'll go through your data several times, picking out the parts you need at each go.

[BWhisler2015]

Thanks! This works!