Splunk Search
Highlighted

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

Path Finder

I am using the below query to join 2 searches, but the table is showing me duplicate rows with only commonfields and null values in onefield onefieldtow onefieldthree 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 onefield onefieldtwo onefieldthree commonfield
| join commonfield [ search sourcetype=two | fields commonfield twofieldone, twofieldtwo ]
| table onefield onefieldtwo onefieldthree twofieldone twofieldtwo commonfield

Tags (4)
Highlighted

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

SplunkTrust
SplunkTrust

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

View solution in original post

Highlighted

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

SplunkTrust
SplunkTrust

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
Highlighted

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

Legend

Or, even easier

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

Highlighted

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

Legend

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