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.
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?
I can't put conditions on all fields.
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.
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
Can you please tell me how to filter results if few of my events don't have all of the fields...?