topic Re: How do I control returned fields from a join? in Splunk Search

How do I control returned fields from a join?

LordVoldemort — Mon, 28 Sep 2020 12:16:49 GMT

This issue continually bites me and there's something I'm just not understanding.

If I search like so:

sourcetype=one | join common_field [ search sourcetype=two ] | table one_field, one_field_two, one_field_three, two_field_one, two_field_two

The resulting table will often be missing fields such as one_field_two and return them empty, even when the data is there.

I understand that if I'm doing a " | stats" on some fields, they will definitely get returned, but could someone explain how I otherwise explicitly ensure fields from one, two or both get returned as non-empty values? I'm not doing a stats here, I just want a table and want those values returned.

Re: How do I control returned fields from a join?

lguinn2 — Thu, 16 Aug 2012 21:51:27 GMT

I would do

sourcetype=one
| fields one_field one_field_two one_field_three common_field
| join common_field [ search sourcetype=two | fields common_field two_field_one, two_field_two ]
| table one_field one_field_two one_field_three two_field_one two_field_two common_field

Using the fields command is a best practice; it can increase efficiency of the search and it explicitly names what you want to have returned.

Is there a possibility that your events don't all have all of the fields?

Re: How do I control returned fields from a join?

edookati — Mon, 28 Jul 2014 18:12:18 GMT

Can you please tell me how to filter results if few of my events don't have all of the fields...?

Re: How do I control returned fields from a join?

somesoni2 — Mon, 28 Sep 2020 17:11:33 GMT

If you want to include events where all needed fields are present, try something like this (same example).

sourcetype=one one_field_one=* one_field_two=* one_field_three=* common_field=* | fields one_field one_field_two one_field_three common_field
| join common_field [ search sourcetype=two | fields common_field two_field_one, two_field_two ]
| table one_field one_field_two one_field_three two_field_one two_field_two common_field

This way all the entries selected from sourcetype=one will have non-null values of one_field one_field_two one_field_three common_field

Re: How do I control returned fields from a join?

edookati — Mon, 28 Jul 2014 18:31:34 GMT

that is the problem. I am trying to form a table with values from soucetype=one & sourcetype=two, but not all fields in sourcetype=two are in sourcetype=one and the table is showing me a lot of duplicate rows with common_field value and null for all other columns.

Re: How do I control returned fields from a join?

edookati — Mon, 28 Jul 2014 18:36:04 GMT

I can't put conditions on all fields.