Splunk Search

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

davietch
Path Finder

Hello,

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

src_groupdest_groupcount
AB10
BA21
AC32
BZ6

 

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

groupsrc_countdest_count
A4221
B2710
C032
Z06

 

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 ]

Labels (3)
0 Karma
1 Solution

to4kawa
Ultra Champion
| 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.

View solution in original post

to4kawa
Ultra Champion
| 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 

0 Karma

davietch
Path Finder

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?

0 Karma

to4kawa
Ultra Champion

what's your full query?

0 Karma

davietch
Path Finder

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

0 Karma

to4kawa
Ultra Champion
| 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
Legend

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

0 Karma

davietch
Path Finder

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

0 Karma

gcusello
Legend

Hi @davietch,

could you share two or three samples of your logs?

Ciao.

Giuseppe

0 Karma

davietch
Path Finder

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
 
0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...