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
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
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
And BTW, @martin_mueller's technique will be MUCH faster...
Or, even easier
sourcetype=one OR sourcetype=two
| stats values(*) as * by common_field
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