Splunk Search

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

edookati
Path Finder

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

Tags (4)
1 Solution

somesoni2
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

martin_mueller
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

lguinn2
Legend

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

lguinn2
Legend

Or, even easier

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

somesoni2
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...