topic Re: how to do "where field in" with splunk in Splunk Search

how to do "where field in" with splunk

hongduan — Fri, 25 Apr 2014 15:32:41 GMT

I need to do a query which looks like
field in [list of values]. The list could be another query's return values.

Re: how to do "where field in" with splunk

somesoni2 — Fri, 25 Apr 2014 17:35:06 GMT

Can provide more information, possibly with some examples?

Re: how to do "where field in" with splunk

mloven_splunk — Fri, 25 Apr 2014 19:58:38 GMT

So, you're looking for a set of results where a specific field (or list of fields) exist? So, let's say you have a field called 'myfield'. You want to show a list of results where myfield exists, right?

If your search has something like:

index=myindex myfield=*

Then only events with a field called myfield will return.

Re: how to do "where field in" with splunk

hongduan — Fri, 25 Apr 2014 20:03:19 GMT

Here is what I want to do;

I have such logs:
name="Susan", date=20130101
name="David", date=20140101
name="Mike", date=20130102
name="SomeName", date=20140102
name="Test", date=20130101
.....more log records

I want to query looks like:
name in ("Susan", "Mike", ....)
The names in bracket is a long list.

Re: how to do "where field in" with splunk

hongduan — Fri, 25 Apr 2014 20:05:50 GMT

And it's inconvenient for me to use OR, because, the list could be a form input field. If user enter some names in the form field, I want my query to take that as parameter. Also, the list is long, to use OR query will make the query even longer to read

Re: how to do "where field in" with splunk

somesoni2 — Fri, 25 Apr 2014 20:49:22 GMT

Try something like this. Here you have pass list of names as comma separated values (which should be easier with form input as well.

index=yourindex [|stats count | eval name="Susan,David,Mike" | table name| eval name=split(name,",") | mvexpand name| format]

In dashboards, it could be like this

index=yourindex [|stats count | eval name="$nameValues$" | table name| eval name=split(name,",") | mvexpand name| format]

Re: how to do "where field in" with splunk

mloven_splunk — Fri, 25 Apr 2014 20:50:32 GMT

because, the list could be a form
input field

So, this sounds like you want to match any value for 'name', right? Because a user could put 'Susan' or 'David', but could also put 'mloven'. So, again, my first answer would work in this scenario.

Maybe paste in some actual logs, and give an example of what you want the search to do. We can probably provide a more exact solution then.

Re: how to do "where field in" with splunk

hongduan — Fri, 25 Apr 2014 21:52:18 GMT

awesome. That's exactly what I need. Is it possible I could upload a file with the possible values and do the search.
Something like: a file contains: "Susan,David,Mike". and in splunk query reference to the file.

Re: how to do "where field in" with splunk

rahulroy_splunk — Tue, 29 Apr 2014 21:01:35 GMT

You can upload your file (list of name) as lookup table file and then use it in the query. E.g. your lookup table, say names.csv, with header as name and one name per row. Add that a lookup table under appropriate app and set necessary sharing permission. Then the updated query could be like this

index=yourindex [|inputlookup names.csv ]