Splunk Search

How to exclude a field from one sourcetype when it appears in two joined searches

markhvesta
Path Finder

I have a search that is joining two sourcetypes that has multiple fields that have the same name. I want to join on one field that is in both, but would like to exclude some of the fields that are present in both.

(sourcetype="sourceA" OR sourcetype="sourceB" ) fieldinAandB=*   
|stats values(*) as * by fieldinAandB
| fields fieldinAandB fieldcalledsamethinginAandBButWeOnlyWantA fieldinA fieldinB

In this search, how would I exclude fieldcalledsamethinginAandBButWeOnlyWantA for B only?

Tags (1)
0 Karma
1 Solution

aberkow
Builder

I think you basically want an if clause that says "if I'm from the right sourcetype, keep my value, else overwrite me as null()". Then, when you write the stats call, you won't be picking up any of the values when you don't want them. FWIW you should also specify an index whenever possible, that's a huge performance key for Splunk.

Example below:

{code}
(sourcetype="sourceA" OR sourcetype="sourceB" ) fieldinAandB=*

| eval fieldInA=if(sourcetype="sourceA", fieldinAandB, null())
| eval fieldInB=if(sourcetype="sourceB", fieldinAandB, null())
|stats values(fieldInA) as fieldInA, values(fieldInB) as fieldInB by fieldinAandB
| fields whateverYouWant
{code}

Does that answer your question?

View solution in original post

woodcock
Esteemed Legend

Like this:

index="YouShouldAlwaysSpecifyAnIndex" AND (sourcetype="sourceA" OR sourcetype="sourceB" ) AND fieldinAandB="*"
| fields fieldinAandB and any others you would like to keep here   
| stats values(*) AS * by fieldinAandB
| fields fieldinAandB fieldcalledsamethinginAandBButWeOnlyWantA fieldinA fieldinB

aberkow
Builder

How do you get the nice table formatting for your code? 😧

0 Karma

aberkow
Builder

I think you basically want an if clause that says "if I'm from the right sourcetype, keep my value, else overwrite me as null()". Then, when you write the stats call, you won't be picking up any of the values when you don't want them. FWIW you should also specify an index whenever possible, that's a huge performance key for Splunk.

Example below:

{code}
(sourcetype="sourceA" OR sourcetype="sourceB" ) fieldinAandB=*

| eval fieldInA=if(sourcetype="sourceA", fieldinAandB, null())
| eval fieldInB=if(sourcetype="sourceB", fieldinAandB, null())
|stats values(fieldInA) as fieldInA, values(fieldInB) as fieldInB by fieldinAandB
| fields whateverYouWant
{code}

Does that answer your question?

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...