topic Re: query using join in Splunk Search

query using join

cevyn — Wed, 19 Feb 2014 16:35:52 GMT

Trying to combine two logs .
Using this query to get a list of items from user log

source="/opt/mysplunk.log" earliest=-14days "logid=store-stuff" | eval storecode = substr(site,1,4) | top 3 storecode | FIELDS - count percent

that gives me a short list of
storecode

1 1234

2 6789

And the | FIELDS – count percent removes those extra fields so I just get my entries of 1234 6789
This also proves my substring is right because I’m catching the string I expect.
Now I try to feed that into a network log to catch possible related causes that show up there

source="/usr/local/nagios/var/nagios.log" earliest=-14days | join [ search source="/opt/mysplunk.log" earliest=-14days "logid=store-stuff" | eval storecode = substr(site,1,4) | top 3 storecode | FIELDS - count percent]

but the result seems to match 50k records that don’t have my identifiers of 1234 or 6789 in them .

What am I doing wrong or what debugging method in splunk do I use to figure out what it is really matching on? What is it actually piping through?
This is my first venture into join commands in splunk queries

Re: query using join

dart — Wed, 19 Feb 2014 16:40:38 GMT

You can see what is being returned if you use the search job inspector - it will have an entry like subsearch returned that should fill you in on what came through

Re: query using join

martin_mueller — Wed, 19 Feb 2014 16:47:26 GMT

Are you sure you're trying to perform a join? Usually you have one (or more) join columns/fields and one (or more) other columns/fields that get added in the join... but your subsearch only yields one column.

Maybe you're trying to filter the nagios source by results from the mysplunk source?

Re: query using join

somesoni2 — Wed, 19 Feb 2014 16:50:20 GMT

does your source nagios.log have a field called storecode or your just want to do literal search for your storecode in the nagios.log events?

Re: query using join

cevyn — Wed, 19 Feb 2014 17:10:12 GMT

So clearly your questions have revealed my limited splunk query experience. The response that asked about literal search best understood my failings. yes I want to go to nagios with the results of 1234 or 6789 in my example. STORECODE was a literal I made up to capture the result of my substr. Thanks for your persistence with my initial note.

Re: query using join

martin_mueller — Wed, 19 Feb 2014 17:16:37 GMT

Based on your comment, here's how you generically search in source A based on the top three values of somefield from source B:

source=A [search source=B | top 3 somefield | return 3 somefield]

Under the hood Splunk will first run the subsearch, translate the results to ((somefield="value1") OR (somefield="value2") OR (somefield="value3")), and then run the main search with that filter added.

If source A does not have a field called somefield you can search its raw text by adding a dollar sign in the return command like so: ... | return 3 $somefield. If the field is called something else in source A you can rename it on the fly like so: ... | return 3 otherfield=somfield.

Re: query using join

cevyn — Wed, 19 Feb 2014 17:34:42 GMT

so I changed it to source="/usr/local/nagios/var/nagios.log" earliest=-14days | [ search source="/opt/mysplunk.log" earliest=-14days "logid=store-stuff" | eval storecode = substr(site,1,4) | top 3 storecode | return 3 storecode ]

I get "Subsearches are only valid as arguments to commands." Perhaps i misunderstood your suggestion?

Re: query using join

martin_mueller — Wed, 19 Feb 2014 18:03:20 GMT

Remove the pipe before the opening square bracket.

Re: query using join

cevyn — Wed, 19 Feb 2014 19:53:06 GMT

You have been very generous. My query is close but I get no results. If I look at the job inspect function and look at subsearch it shows storecode="1234" when I think want it to just be "1234" (storecode being a variable I made up for the EVAL). Sorry I'm spending a lot of time getting used to the syntax . How do I drop the storecode= out .

I tried things like value(storecode) but have obviously not found the right thing. I feel like I'm trying to discuss philosophy in french and only have the vocabulary to say hello! thanks again.

Re: query using join

martin_mueller — Wed, 19 Feb 2014 20:01:21 GMT

Like I said in the answer, put a dollar sign in front of the field in the return command like this:

... | return 3 $otherfield

That will yield (("value1") OR ("value2") OR ("value3")) as a filter for the main search.