topic Re: Newbie: Splunk equivalent of NATURAL JOIN in Splunk Search

Newbie: Splunk equivalent of NATURAL JOIN

travispowell — Tue, 19 Apr 2011 23:42:22 GMT

I don't really know what to search for on here, but I can't seem to get the | (pipe operator) to work like UNIX.

I have two sets of records. Examples of each type:

A) April 18, session=12345 http_status=404
   April 19, session=78912 http_status=200
   April 20, session=45678 http_status=503

and...

B) April 18, session=12345 ipaddr=89.76.45.34
   April 19, session=78912 ipaddr=44.27.53.78
   April 20, session=45678 ipaddr=79.73.25.39

Here's what I'm trying to get my query to do: get all session ids where http_status >= 400, and then give me the result set of logs where session = x, and ipaddr exists.

Thank you! One example will open up a lot of doors for me with Splunk.

UPDATED: In SQL: SELECT * FROM "B" NATURAL JOIN "A"

Re: Newbie: Splunk equivalent of NATURAL JOIN

sideview — Tue, 19 Apr 2011 23:44:47 GMT

Let's say that the events in A have sourcetype="A", and the events in B have sourcetype="B". Maybe in your actual data it'll be source or host or something else but it doesnt matter.

1) If you're looking to stitch everything together on session ids, there are several ways to do this sort of thing. Here are two

(sourcetype=A) OR (sourcetype=B ipaddr=*) | transaction session | search http_status>=400

and sometimes stats is the best tool for the job, although here you need a little mvexpand and it's a bit more complicated:

(sourcetype=A ) OR (sourcetype=B ipaddr=*) | stats count first(ipaddr) as ipaddr values(http_status) as http_status by session | mvexpand http_status | search http_status>=400 | fields ipaddr

2) However depending on how sparse the http_status values over 400 are, you might want to take the approach of using a subsearch. The following will use a subsearch to get all the session id's that had errors, and then it searches on all of those session Id's against sourcetype B. Then finally we just pipe to top to get the top ipaddr values.

sourcetype=B ipaddr=* [sourcetype=A http_status>=400 | fields session] | top ipaddr

Re: Newbie: Splunk equivalent of NATURAL JOIN

travispowell — Tue, 19 Apr 2011 23:47:54 GMT

Let me explain better.

The above example should return the first and last logs from B:

April 18, session=12345 ipaddr=89.76.45.34

April 20, session=45678 ipaddr=79.73.25.39

Re: Newbie: Splunk equivalent of NATURAL JOIN

travispowell — Tue, 19 Apr 2011 23:48:49 GMT

because those are the two logs that have http_status >= 400 in log type A.

Re: Newbie: Splunk equivalent of NATURAL JOIN

sideview — Tue, 19 Apr 2011 23:52:44 GMT

I see. sorry I misinterpreted yr question. See updated answer.

Re: Newbie: Splunk equivalent of NATURAL JOIN

travispowell — Wed, 20 Apr 2011 00:02:58 GMT

No problem. Thanks for that. I updated my question so it's clearer for anyone else.

Re: Newbie: Splunk equivalent of NATURAL JOIN

travispowell — Wed, 20 Apr 2011 00:23:23 GMT

2 -- Stats worked the best 🙂