Solution 1: (Easier way)
I would suggest using a summary index rather than using the proxy index directly. I would setup a scheduled search that will keep on writing to a summary index and then use that in the join subsearch. Something like this:
| tstats summariesonly=t count as Count, dc(fw.rule) as dc_rules, values(fw.rule) as rules, max(_time) as LastSeen, values(fw.dest_ip) as Destination FROM datamodel=Firewall.fw WHERE fw.dest_ip = 8.8.4.4 OR fw.dest_ip = 8.8.8.8 AND fw.action = "blocked" BY fw.src_ip, fw.action | rename src_ip as src_host | join src_host [ search index= sourcetype= | fields src_host,UserName] | table src_host,Destination,action,UserName,Count
Solution 2 (Not so straightforward):
Second solution is where you use the tstats in the inner query. But as you may know tstats only works on the indexed fields. So in this solution you can make src_host and UserName as indexed fields that are extracted index time (Writing a transform to keep it simply). Then using these fields using the tstats
| tstats summariesonly=t count as Count, dc(fw.rule) as dc_rules, values(fw.rule) as rules, max(_time) as LastSeen, values(fw.dest_ip) as Destination FROM datamodel=Firewall.fw WHERE fw.dest_ip = 8.8.4.4 OR fw.dest_ip = 8.8.8.8 AND fw.action = "blocked" BY fw.src_ip, fw.action | rename src_ip as src_host | join src_host [ | tstats count where index=proxy sourcetype= by src_host, UserName | fields src_host,UserName] | table src_host,Destination,action,UserName,Count
2nd one is relatively long term solution.
Note: Adding index time fields has some trade-offs and I would consider checking the indexing rates and other performance parameters before doing it.
... View more