Splunk Search

How do I control returned fields from a join?

LordVoldemort
Explorer

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.

Tags (1)
0 Karma

lguinn2
Legend

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?

edookati
Path Finder

I can't put conditions on all fields.

0 Karma

edookati
Path Finder

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.

0 Karma

somesoni2
Revered Legend

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

0 Karma

edookati
Path Finder

Can you please tell me how to filter results if few of my events don't have all of the fields...?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...