How to compare fields from two different event types

[corwinz6]

Hello,

I am trying to come up with a search to compare the IP address values from two different log types contained in the same sourcetype. i.e. sourcetype=firewall (type=traffic AND status!=deny) Grab src and compare to src in (type=emailfilter AND blacklist)

I would then chart values that aren't denied in traffic but are blacklisted in a table showing the top 10.

How/is it possible to compare the src field in the traffic log to the src field in the email log if the above conditions are true?

Thanks

[Johnvey · Accepted solution]

It sounds like you're trying to do an intersection of the following 2 searches:

(1)  sourcetype=firewall (type=traffic AND status!=deny)
(2)  sourcetype=firewall (type=emailfilter AND blacklist)

If that's true, then you can use stats to identify a field value for src that appears in both searches:

sourcetype=firewall (type=traffic AND status!=deny) OR (type=emailfilter AND blacklist) | stats count dc(type) as type_count by src | where type_count > 1 | sort count desc | head 10

This search assumes that the two searches actually produce mutually exclusive sets.

[corwinz6]

Thanks, that looks to be working great. What prevents it from incrementing type_count when more than one event gets generated for (type=traffic AND status!=deny)with the same src value but no (type=emailfilter AND blacklist)?