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,
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
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.
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
Or, if you need to see the original events, then change my stats
to eventstats
.
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
It works fine. Thanks
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
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.
Not "confusing" it is "confused." 😉
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