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

[santosh_hb]

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,

[santosh_hb]

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.

[woodcock]

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

[woodcock]

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

[knielsen · Accepted solution]

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

[santosh_hb]

It works fine. Thanks

[rjthibod]

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

[richgalloway]

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.

[DalJeanis]

Not "confusing" it is "confused." 😉

[niketn]

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