How do I control returned fields from a join?

[LordVoldemort]

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.

[lguinn2]

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?

[edookati]

I can't put conditions on all fields.

[edookati]

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.

[somesoni2]

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

[edookati]

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