Splunk Search

Is there any function works like group by grouping sets in Mysql?

krim
Explorer

is there any function works like group by grouping sets in Mysql?
So that I can get a value from each group and a total one

Labels (2)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

The stats XXX by YYY will do aggregations to group by YYY and you can include more than one field to group by.

Can you explain a bit more exactly what you are trying to do with the data you have

 

0 Karma

krim
Explorer

krim_1-1664438858620.png

 

select

coalesce(class,"total") as class,

coalesce(name,"total") as name,

sum(num) as sum

from table a
group by grouping sets (class,name,(class,name))

In mysql I can get sum from each group and a total at the same time, from the left table to the right one. 

can I do the same in Splunk?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

The answer is always YES with Splunk 😀 and often there is more than one way to achieve the same result.

Here is an example with your data setup (note that in your data example, your C2/C sum is shown as '3' in the right table, however, from the data in the left, it should be 4, right?

| makeresults
| eval _raw="Class,name,number
C1,A,1
C1,B,2
C2,C,1
C1,A,1
C1,B,2
C2,C,3"
| multikv forceheader=1 

| table Class,name,number

| stats sum(number) as sum by Class name
| addcoltotals labelfield="Class"
| appendpipe [
  | stats sum(sum) as sum by Class
]

so in this example, up to multikv is setting up the sample data from your left hand table, then:

the first stats sum... is calculating the totals by Class/name to give you 3 rows. Then addcoltotals is giving the total for those 3 rows. After that the appendpipe/stats is then calculating the totals for each class based on the values of those 3 rows.

Hope this helps.

 

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...