How to group by from two fields and perform stats depending of the field?

[davietch]

Hello,

Let me give you an example. I've got the following table to work with:

src_group	dest_group	count
A	B	10
B	A	21
A	C	32
B	Z	6

I'd like to have something like this for result:

group	src_count	dest_count
A	42	21
B	27	10
C	0	32
Z	0	6

As you can see, I have now only one colomn with the groups,  and the count are merged by groups while the direction (src or dest) is now on the counts : we sum the count for each group depending of whether the group was the source or the destination in the first table.

Any clue?

Edit: to help me, you can use this search that will generate the first table:

| stats count | eval src_group="A", dest_group="B", count=10
| append [| stats count | eval src_group="B", dest_group="A", count=21 ]
| append [| stats count | eval src_group="A", dest_group="C", count=32 ]
| append [| stats count | eval src_group="B", dest_group="Z", count=6 ]

[to4kawa]

| makeresults
| eval _raw="src_group	dest_group	count
A	B	10
B	A	21
A	C	32
B	Z	6"
| multikv forceheader=1
| table src_group	dest_group	count
| rename COMMENT as "this is sample you provide. From here, the logic"
| eval tmp=mvappend(src_group,dest_group)
| eventstats values(tmp) as group
| mvexpand group
| stats sum(eval(if(src_group=group,count,NULL))) as src_count sum(eval(if(dest_group=group,count,NULL))) as dest_count by group
| fillnull src_count dest_count

how about this? @davietch 

[davietch]

Hello,

Thanks, it looks like it works for my example, but on production data, I very quickly reach a limit that I cannot increase...

command.mvexpand: output will be truncated at 946100 results due to excessive memory usage. Memory threshold of 500MB as configured in limits.conf / [mvexpand] / max_mem_usage_mb has been reached.

Could I use a workaround for mvexpand?

[to4kawa]

what's your full query?

[davietch]

This is it:

| tstats count from datamodel="Network_Traffic" where nodename="All_Traffic.Traffic_By_Action" All_Traffic.src_ip="*" AND All_Traffic.dest_ip="*" All_Traffic.action="blocked" by All_Traffic.src_ip, All_Traffic.dest_ip
| rename All_Traffic.* AS *, values(All_Traffic.*) AS *
| lookup ipgroups.csv ip AS src_ip OUTPUT group AS src_group | lookup ipgroups.csv ip AS dest_ip OUTPUT group AS dest_group | table src_group, dest_group, count

[to4kawa · Accepted solution]

| tstats count from datamodel="Network_Traffic" where nodename="All_Traffic.Traffic_By_Action" All_Traffic.src_ip="*" AND All_Traffic.dest_ip="*" All_Traffic.action="blocked" by All_Traffic.src_ip, All_Traffic.dest_ip
| rename All_Traffic.* AS *, values(All_Traffic.*) AS *
| lookup ipgroups.csv ip AS src_ip OUTPUT group AS src_group | lookup ipgroups.csv ip AS dest_ip OUTPUT group AS dest_group 
| table src_group dest_group count

| eval group=mvappend(src_group,dest_group)
| mvexpand group
| eval src_count=if(src_group=group,count,NULL), dest_count=if(dest_group=group,count,NULL)
| stats sum(src_count) as src_count sum(dest_count) as dest_count by group

maybe this query can work.

[gcusello]

Hi @davietch,

Try something like this:

your_search
| eval group=coalesce(src_group,dest_group)
| stats count(src_group) AS src_group count(dest_group) AS dest_group BY group

Ciao.

Giuseppe

[davietch]

Hi, Unfortunately this is not what I want.

| eval group=coalesce(src_group,dest_group)

will give me only the src_group value and, in my example, discard C & Z.

| stats count(src_group) AS src_group count(dest_group) AS dest_group BY group

 will just count the number of lines. I would need to do a sum().

[gcusello]

Hi @davietch,

could you share two or three samples of your logs?

Ciao.

Giuseppe

[davietch]

Hi,

You can use 

| stats count | eval src_group="A", dest_group="B", count=10
| append [| stats count | eval src_group="B", dest_group="A", count=21 ]
| append [| stats count | eval src_group="A", dest_group="C", count=32 ]
| append [| stats count | eval src_group="B", dest_group="Z", count=6 ]

To work on

[davietch]

| stats count | eval src_group="A", dest_group="B", count=10
| append [| stats count | eval src_group="B", dest_group="A", count=21 ]
| append [| stats count | eval src_group="A", dest_group="C", count=32 ]
| append [| stats count | eval src_group="B", dest_group="Z", count=6 ]