Splunk Search
Highlighted

How to sum output of table command

Path Finder

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

dataset datacount

corpzero 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

corpzero 32
ebz
europe 6
icm 366
mbs 2
rm_iso 2

rm_strips 2

and so on below is the search
rex field=raw maxmatch=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
cacorpzero 2
corpzero 286
ebz
europe 90
HKGgeneric 18
icm 3802
mbs 16
rm
agency 4
rmiso 16
rm
strips 25

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

Tags (1)
0 Karma
Highlighted

Re: How to sum output of table command

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.

Highlighted

Re: How to sum output of table command

Path Finder

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

0 Karma
Highlighted

Re: How to sum output of table command

Path Finder

rex field=raw maxmatch=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
Highlighted

Re: How to sum output of table command

Influencer

What version of splunk ?

Try using the regex method instead

0 Karma
Highlighted

Re: How to sum output of table command

Path Finder

Hi Regex method give an error

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

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

0 Karma
Highlighted

Re: How to sum output of table command

Path Finder

Version is 4.2.3

0 Karma
Highlighted

Re: How to sum output of table command

Builder

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

0 Karma
Highlighted

Re: How to sum output of table command

Legend

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

0 Karma
Highlighted

Re: How to sum output of table command

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