How to correlate more than 2 data points in a search?

[ltrand]

So I am trying to tackle a real doozy of a search (at least for me) that has me stumped. I am attempting to learn to correlate more than one reference point at a time.

Here is a sample of what I am trying to do:

sourcetype="network_av_prod1"
| fields src, sig_name, threat_class
| dedup src
| rename src AS source
| join source [ search sourcetype="network_av_prod2"
|fields src_ip, Malware_Name
| dedup src_ip
| rename src_ip AS source ]
|join source [ search sourcetype="proxy" category="Malicious" OR category="bot" OR category="Potentially*" OR category="Illegal" OR category="peer-to-peer" OR category="Uncategorized"
| fields src, dest, http_method, uri_query, category
| dedup src
| rename src AS source ]
| table source, dest_ip, category, http_method, uri_query, Malware_Name, sig_name, threat_class

If I remove all of the second join I can get it to work. I can do any combination of two of these and it works. As soon as I add the third point it breaks with 0 results. The idea is that I only want results for source IP's that are in all sourcetypes and it is assumed they will ALWAYS have something in the proxy.

Ideally I would like to constrain it to events that are all within 1 minute of eachother and do stats functions, but I'm not there yet, I need to figure out correlation of multiple sourcetypes first then add complexity. So, can anyone help out with properly correlating these?

::EDIT::
1: I added the last bracket to close of the last join as it was a typo.
2: The desired outcome is a panel that looks something like this:

Source 1|Dest IP| Category 1 | http_Method | uri_query | Malware Name 1 | sig_name 1 | threat_class 1
Source 2|Dest IP| Category 1 | http_Method | uri_query | Malware Name 2 | sig_name 1 | threat_class 1

[chris]

Usually you can solve this kind of problem with either the stats or the transaction command.

Try one of these:

sourcetype="network_av_prod1" OR sourcetype="network_av_prod2" OR ( sourcetype="proxy" (category="Malicious" OR category="bot" OR category="Potentially*" OR category="Illegal" OR category="peer-to-peer" OR category="Uncategorized") ) |  bucket _time span=1m | eval source=coalesce(src_ip,src)| stats dc(sourcetype) as source_type_count values(sig_name) as sig_names values(threat_class) as threat_classes  values(Malware_Name) as MW_Names values(des) as dests values(http_method) as http_methods values(uri_query) as uri_queries values(category) as categories by source _time | search source_type_count=3

sourcetype="network_av_prod1" OR sourcetype="network_av_prod2" OR ( sourcetype="proxy" (category="Malicious" OR category="bot" OR category="Potentially*" OR category="Illegal" OR category="peer-to-peer" OR category="Uncategorized") )  | eval source=coalesce(src_ip,src) | transaction source maxspan=1m | where mvcount(sourcetype)=3

The stats version will be better performance wise but maybe not what you want.

[chris]

Sorry I forgot to add the filtering commands at the end of the searches. This should now give you results where every sourcetype appears at least once per source. But maybe a | dedup source sourcetype _time after the bucket and the eval cmd of the first search is what you are after? You can the still do a stats or transaction after that

[ltrand]

The problem with this search is that it shows all hits for each, regardless of correlation.

[sk314]

Is the sourcetype same for the first search and first join? Also, where does the last join end?

[ltrand]

Corrected typos, thanks!