How do I count and sum multivalue fields by another multivalue field?

[Log_wrangler]

Hi,

I am hitting a dead end with my search...

I have two multivalue fields:

Site_ID - has 100's of values
Attack - has 10 values

I want a report that shows the count of individual attacks and total attacks by Site_ID

Site_ID                     XSS         SQLi    Total by Site
my.site.com              10           12         24

I have been using this as a base search

index=Firewall sourcetype=Firewall_logs
Attack = SQLi OR
Attack = xSS OR
| chart count over Site_ID by Attack

But I just cannot find the right syntax to produce the needed report.

Any help would be greatly appreciated!

[woodcock · Accepted solution]

The stats, chart, and timechart commands are all multi-value-safe so all that you need is a total column? That is cake, just add | addtotals row=t col=f fieldname="Total by Site". See this run-anywhere example:

index=_* 
| rename sourcetype AS Attack, host AS Site_ID 
| chart count OVER Site_ID BY Attack
| addtotals row=t col=f fieldname="Total by Site"

[Log_wrangler]

Thanks, you're right... cake, forgot how to do it.