How to join 2 searches and control table output to remove rows with null field values?

[edookati]

I am using the below query to join 2 searches, but the table is showing me duplicate rows with only common_fields and null values in one_field one_field_tow one_field_three columns for these rows. Please let me know how to control the table output and remove all rows with one_field* null values.

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

[martin_mueller]

Alternatively, try stats:

  sourcetype=one OR sourcetype=two
| stats values(1f1) as 1f1 values(1f2) as 1f2
        values(2f1) as 2f1 values(2f2) as 2f2 by common_field

[lguinn2]

And BTW, @martin_mueller's technique will be MUCH faster...

[lguinn2]

Or, even easier

sourcetype=one OR sourcetype=two
| stats values(*) as * by common_field

[somesoni2 · Accepted solution]

Try this

sourcetype=one one_field=* 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

If there are still duplicates, try this

sourcetype=one one_field=* one_field_two=* one_field_three=* common_field=*| stats count by one_field one_field_two one_field_three common_field | fields - count| join common_field [ search sourcetype=two | stats count by common_field two_field_one, two_field_two | fields - count] | table one_field one_field_two one_field_three two_field_one two_field_two common_field