Splunk Search

How to sum output of table command

ashu_g50
Path Finder

Hi I have a output of the table command as below :

dataset datacount

corp_zero 32
ebz_europe 6
icm 362
mbs 2
rm_iso 2

rm_strips 2

ebz_europe 2

icm 24

HKG_generic 2
icm 72

rm_strips 1

HKG_generic 4
icm 144

rm_strips 2

HKG_generic 4
icm 144

rm_strips 2

corp_zero 32
ebz_europe 6
icm 366
mbs 2
rm_iso 2

rm_strips 2

and so on below is the search
rex field=raw max_match=20 "(?i)dataSetListCountInfo_(?P[^=]+)=(?P\d{1,3}+)" | table dataset datacount

I want to achieve a pivot table where in the individual dataset and the corresponding numbers are sumed up.

Dataset Datacount
ca_corp_zero 2
corp_zero 286
ebz_europe 90
HKG_generic 18
icm 3802
mbs 16
rm_agency 4
rm_iso 16
rm_strips 25

how can I achieve this? Stats sum(datacount) by dataset after table doesnt seem to work.

Tags (1)
0 Karma

Ayn
Legend

Just getting rid of the table command and using stats directly should work.

0 Karma

Ayn
Legend

Oh, I didn't see that these weren't unique events. You need to make it so first. Have a look at jonuwz's answer.

0 Karma

ashu_g50
Path Finder

now when i use
rex field=raw max_match=20 "(?i)dataSetListCountInfo_(?P[^=]+)=(?P\d{1,3}+)" | stats sum(datacount) by dataset, I get the correct number of rows expected but you see the datacount value? its wrong.

dataset sum(datacount)
HKG_generic 2294
ca_corp_zero 1534
corp_zero 5185
ebz_europe 5211
icm 6471
mbs 4993
rm_agency 1594
rm_iso 4993
rm_strips 6193

0 Karma

ashu_g50
Path Finder

heres te thing when I use
rex field=raw "(?i)dataSetListCountInfo_(?P[^=]+)=(?P\d{1,3}+)" | stats sum(datacount) by dataset I get correct datacount numbers but wrong number of rows as the query only pics the 1st instance from different instances.

dataset sum(datacount)
HKG_generic 36
ca_corp_zero 2
corp_zero 280
ebz_europe 2
icm 58

0 Karma

ashu_g50
Path Finder

ok stats directly is not yeilding correct results its a bit complex refer to my other questions "Need to Extract fields"

0 Karma

jonuwz
Influencer

You need to flatten the results into unique lines 1st.

There's 2 ways to do this, whats probably considered the right way

... | eval x=mvzip(dataset,datacount) | mvexpand x | makemv delim="," x | eval dataset=mvindex(x,0) | eval datacount=mvindex(x,1) | fields - x | ...

or the regex way

... | eval x=dataset.",".datacount | mvexpand x | rex field=x (?<dataset>.*?),(?<datacount>.*) | fields - x | ...

I'm a regex kinda person myself.

smolcj
Builder

How to avoid this "Field 'x' does not exist in the data" while using mvzip and mvexpand

0 Karma

ashu_g50
Path Finder

Version is 4.2.3

0 Karma

ashu_g50
Path Finder

Hi Regex method give an error

Error in 'rex' command: Invalid argument: '(?.*)'

gfi.wellington.com accounting-service: dataSetListCountInfo | rex field=raw max_match=20 "(?i)dataSetListCountInfo_(?P[^=]+)=(?P\d{1,3}+)" | eval x=dataset.",".datacount | mvexpand x | rex field=x (?.?),(?.) | fields - x

0 Karma

jonuwz
Influencer

What version of splunk ?

Try using the regex method instead

0 Karma

ashu_g50
Path Finder

rex field=raw max_match=20 "(?i)dataSetListCountInfo_(?P[^=]+)=(?P\d{1,3}+)" | table dataset datacount |eval x=mvzip(dataset,datacount) | mvexpand x | makemv delim="," x | eval dataset=mvindex(x,0) | eval datacount=mvindex(x,1) | fields - x

am I doing something wrong?

0 Karma

ashu_g50
Path Finder

Error in 'eval' command: The 'mvzip' function is unsupported or undefined.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...