Splunk Search

Finding a field value that is available in sourcetype2 but not available in sourcetype1

santosh_hb
Explorer

Hi,
I would like to find a field value of a field (Email_Address) that is available in only sourcetype2 and not available in sourcetype1
Email_Address field is available in both sourcetypes.

My sample query is like this:

sourcetype=sourcetype2 | dedup Email_Address | stats values(Email_Address) AS count_sourcetype1 | join type=Email_Address [search sourcetype=sourcetype1 | dedup Email_Address | stats values(Email_Address) AS count_sourcetype2 ]

The above query is not returning any results.
Can you please help me with the correct query.
Thanks,

Tags (1)
0 Karma
1 Solution

knielsen
Contributor

Hi,

what about:

sourcetype=sourcetype1 OR sourcetype=sourcetype2 | stats count by Email_Address,sourcetype | xyseries Email_Address sourcetype count | fillnull sourcetype1 sourcetype
2 | search sourcetype1=0 sourcetype2>0 | fields Email_Address

View solution in original post

0 Karma

santosh_hb
Explorer

To give more clarity on my query, please find the below example:

Email_Address field is extracted and is available for both sourcetypes (sourcetype1 and sourcetype2).

Ex:
Value of Email_Address that is available in only sourcetype1 is: sample1@sample.com
Value of Email_Address that is available in only sourcetype2 is: sample2@sample.com
Value of Email_Address that is available in both sourcetype1 and sourcetype2 is: test@testing.com

Now, my requirement is:
I should be able to fetch and display only sample2@sample.com as it is present in only sourcetype2.
How can I do this?

I hope this will give some clarity.

0 Karma

woodcock
Esteemed Legend

Try this:

sourcetype="sourcetype1" OR sourcetype="sourcetype2"
| stats dc(sourcetype) AS sourcetypes values(sourcetype) AS sourcetype BY Email_Address
| search sourcetype="sourcetype2" AND sourcetypes=1
0 Karma

woodcock
Esteemed Legend

Or, if you need to see the original events, then change my stats to eventstats.

0 Karma

knielsen
Contributor

Hi,

what about:

sourcetype=sourcetype1 OR sourcetype=sourcetype2 | stats count by Email_Address,sourcetype | xyseries Email_Address sourcetype count | fillnull sourcetype1 sourcetype
2 | search sourcetype1=0 sourcetype2>0 | fields Email_Address
0 Karma

santosh_hb
Explorer

It works fine. Thanks

0 Karma

rjthibod
Champion

It will be much better to do this in one query.

(sourcetype=sourcetype1 OR sourcetype=sourcetype2) Email_Address=*
| fields Email_Address sourcetype 
| chart count over Email_Address by sourcetype
| where sourcetype2 > 0 AND (sourcetype1 < 1 OR isNull(sourcetype1))
| fields Email_Address
0 Karma

richgalloway
SplunkTrust
SplunkTrust

This question is confusing. First it says the Email_Address field is only available in sourcetype2, then it says the field is available in both sourcetypes. The sample query implies the field is available in both sourcetypes.

---
If this reply helps you, Karma would be appreciated.

DalJeanis
SplunkTrust
SplunkTrust

Not "confusing" it is "confused." 😉

0 Karma

niketn
Legend

Can you try this?

sourcetype="sourcetype2" OR sourcetype="sourcetype1"
| stats dc(sourcetype) as Count values(sourcetype) as sourcetype by Email_Address
| search Count=1 AND sourcetype="sourcetype2"
| table Email_Address
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

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

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