topic Re: How can I join two searches on a common field? in Splunk Search

How can I join two searches on a common field?

AverageMale — Thu, 22 Sep 2016 03:54:42 GMT

This is my sample logs in [bowlers]:

"doYouBowl":"YES", "pin":"123", "name":"Billy"
"doYouBowl":"NO", "pin":"456", "name":"Bob"
"doYouBowl":"NO", "pin":"123", "name":"Mike"

Expected results is that the "pin" number must match and the resulting join results will be:

"doYouBowl":"YES", "pin":"123", "name":"Billy"
"doYouBowl":"NO", "pin":"123", "name":"Mike"

This is what I came up with by researching, but I get an error at 121-ish, where I try to join on pin and the second search:

 sourcetype="bowlers" \"doYouBowl\":\"NO\" | rex field=_raw "\"(?<pin>\d+)\"" | join pin [\"doYouBowl\":\"YES\" | rex field=_raw "\"(?<pin>\d+)\""]

Looking at the answers here, got suggestions to use transactions or (translate?), but want to get this join to work first and foremost.
Any assistance would be appreciated.

Thank you.

Re: How can I join two searches on a common field?

Richfez — Thu, 22 Sep 2016 11:41:08 GMT

Try making "pin" the field on all of it at once. No "join" is needed at that point, instead you can use a stats, transaction or other method to group them. Which is best all depends on what you are trying to do.

 sourcetype="bowlers"  | rex field=_raw "\"pin\":\"(?<pin>\d+)\"" | stats count by pin

You could transaction on pin, too, which would group the events a different way.

 sourcetype="bowlers"  | rex field=_raw "\"pin\":\"(?<pin>\d+)\"" | transaction maxspan=1d pin

Adjust your maxspan to be "long enough" but no longer.

You'll notice my rex "pins" the field so it'll look for the string "pin:" and the digits after that will be used as the field pin. This should make it more reliable in case you get other digits somewhere.

Ask if you need more, otherwise great question, thanks!

Re: How can I join two searches on a common field?

AverageMale — Thu, 22 Sep 2016 16:02:12 GMT

Hi Rich7177,

Sorry, I didn't emphasize on the major criteria that the query is to find all cases where records exists that have the same "pin" but different values for "doYouBowl"? This is why I wanted to use the join operation.

I tried your solutions the query just looked for where the row contained "pin" and reported stats on it regardless if "doYouBowl" had same or different values.

Any ideas?
Thanks.

Re: How can I join two searches on a common field?

sundareshr — Thu, 22 Sep 2016 16:09:34 GMT

Try this

sourcetype="bowlers"  | rex field=_raw "\"pin\":\"(?<pin>\d+)\"" | rex "(?<bowl>YES|NO)"  | rex field=_raw "\"name\":\"(?<name>\w+)\"" | stats values(bowl) as doYouBowl values(name) as bowlers by pin | where mvcount(doYouBowl )=2

*UPDATED*

 sourcetype="bowlers"  | rex field=_raw "\"pin\":\"(?<pin>\d+)\"" | rex "(?<bowl>YES|NO)"  | rex field=_raw "\"name\":\"(?<name>\w+)\"" | eventstats values(bowl) as doYouBowl by pin | where mvcount(doYouBowl)=2

Re: How can I join two searches on a common field?

AverageMale — Thu, 22 Sep 2016 16:52:28 GMT

This appears to find the occurrences I am looking for but I have to drill down against each pin to confirm as the returned results are noted as:

1234567 NO/YES
5445435 NO/YES
...
...

Rather than the actual expected results noted in my original post:

"doYouBowl":"YES", "pin":"1234567", "name":"Billy"
"doYouBowl":"NO", "pin":"1234567", "name":"Mike"

Is there a way to get this results?

Re: How can I join two searches on a common field?

sundareshr — Thu, 22 Sep 2016 17:02:24 GMT

See updated query

Re: How can I join two searches on a common field?

AverageMale — Thu, 22 Sep 2016 17:26:40 GMT

Your last update seems to have done the trick! Thank you so much!

Re: How can I join two searches on a common field?

somesoni2 — Thu, 22 Sep 2016 17:29:40 GMT

Another alternative

your base search | table _raw | extract kvdelim=":" pairdelim=", " | stats values(doYouBowl) as doYouBowl by pin | where mvcount(doYouBowl)=2