Splunk Search

How to write a search to return all rows for each value of field_b where field_a = a, b, and c?

xcheng123
Engager

I am new to Splunk with questions below. Can anyone can help interpret the following request into a Splunk search statement?

Dataset is like below:
field_a field_b field_c
string_a b1 c1
string_b b1 c2
string_c b1 c3
string_c b2 c3
string_d b2 c4
string_d b1 c5

I am going to create a splunk search like following SQL

select*
from table
where field_a in ("string_a","string_b","string_c")
and field_b in (
select field_b from table
where field_a in ("stirng_a","string_b","string_c")
group by field_b
having count(distinct field_a) = 3)

The expectation is to return all the rows for a field_b with all where all string_a, string_b, and string_c occurred in field_a, such as:
field_a field_b field_c
string_a b1 c1
string_b b1 c2
string_c b1 c3

Thank you all in advance.

0 Karma
1 Solution

xcheng123
Engager

I figured this out with the following Splunk search:

index=*  (field_a=string_a OR field_a=string_b OR field_a=string_c) [search index=* (field_a=string_a OR field_a=string_b OR field_a=string_c) | eval category = case(match(field_a,"string_a"),"a",match(field_a,"string_b"),"b",match(field_a,"string_c"),"c") | stats dc(category) as dcCat by field_b | where dcCat = 3 | TABLE field_b] | eval category = case(match(field_a,"string_a"),"a",match(field_a,"string_b"),"b",match(field_a,"string_c"),"c") | Table field_a, field_b, field_c

If it is incorrect or if anyone has any other solutions, please feel free to post.
Thanks.

View solution in original post

0 Karma

xcheng123
Engager

I figured this out with the following Splunk search:

index=*  (field_a=string_a OR field_a=string_b OR field_a=string_c) [search index=* (field_a=string_a OR field_a=string_b OR field_a=string_c) | eval category = case(match(field_a,"string_a"),"a",match(field_a,"string_b"),"b",match(field_a,"string_c"),"c") | stats dc(category) as dcCat by field_b | where dcCat = 3 | TABLE field_b] | eval category = case(match(field_a,"string_a"),"a",match(field_a,"string_b"),"b",match(field_a,"string_c"),"c") | Table field_a, field_b, field_c

If it is incorrect or if anyone has any other solutions, please feel free to post.
Thanks.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I don't understand the reason for the subsearch, but if it works then accept the answer. I advise against using index=* for performance reasons. Use specific index names.

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

This should get you started.

index = foo (field_a="string_a" OR field_a="string_b" OR field_a="string_c") | head 3 field_a | sort field_b
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...