I have the following table of activities:
Internal | External | Direction |
1.1.1.1 | 2.2.2.2 | Outbound |
3.3.3.3 | 4.4.4.4 | Inbound |
5.5.5.5 | 4.4.4.4 | Inbound |
1.1.1.1 | 8.8.8.8 | Outbound |
I want to group them by either Internal OR External, based on what is in the Direction field, if its Outbound I want to group by Internal , if its Inbound I want to group by External, and get the count.
I would like to get the following table as a result:
Internal | External | Count | Grouped by | Direction |
1.1.1.1 | 2.2.2.2 8.8.8.8 |
2 | 1.1.1.1 | Outbound |
3.3.3.3 5.5.5.5 |
4.4.4.4 | 2 | 4.4.4.4 | Inbound |
Thanks.
So effectively you want to group by src_ip
...your existing query...
| eval src_ip = if(Direction="Outbound",Internal,External)
| stats values(Internal) as Internal values(External) as External count values(Direction) as Direction by src_ip
Try something like this
| eventstats values(Internal) as internals by External
| eventstats values(External) as externals by Internal
| eval groupby=if(Direction="Outbound",Internal,External)
| stats values(internals) as internals values(externals) as externals values(Direction) as Direction by groupby
| eval count=max(mvcount(internals), mvcount(externals))