topic Re: How to group by from two fields and perform stats depending of the field? in Splunk Search

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

davietch — Wed, 22 Jul 2020 07:54:01 GMT

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:

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

gcusello — Wed, 22 Jul 2020 07:42:45 GMT

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

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

davietch — Wed, 22 Jul 2020 08:42:28 GMT

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().

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

gcusello — Wed, 22 Jul 2020 08:45:18 GMT

Hi @davietch,

could you share two or three samples of your logs?

Ciao.

Giuseppe

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

davietch — Wed, 22 Jul 2020 08:46:45 GMT

Hi,

You can use

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

to4kawa — Wed, 22 Jul 2020 09:47:40 GMT

| 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

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

davietch — Wed, 22 Jul 2020 10:12:47 GMT

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?

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

to4kawa — Wed, 22 Jul 2020 10:25:42 GMT

what's your full query?

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

davietch — Wed, 22 Jul 2020 12:05:09 GMT

This is it:

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

to4kawa — Wed, 22 Jul 2020 13:34:01 GMT

| 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.