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!

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 ...