topic Re: How to sum output of table command in Splunk Search

How to sum output of table command

ashu_g50 — Mon, 28 Sep 2020 12:50:57 GMT

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.

Re: How to sum output of table command

jonuwz — Thu, 22 Nov 2012 09:24:12 GMT

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.

Re: How to sum output of table command

Ayn — Thu, 22 Nov 2012 09:26:58 GMT

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

Re: How to sum output of table command

ashu_g50 — Thu, 22 Nov 2012 09:29:55 GMT

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

Re: How to sum output of table command

ashu_g50 — Mon, 28 Sep 2020 12:51:03 GMT

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

Re: How to sum output of table command

ashu_g50 — Mon, 28 Sep 2020 12:51:06 GMT

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

Re: How to sum output of table command

Ayn — Thu, 22 Nov 2012 09:47:07 GMT

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.

Re: How to sum output of table command

ashu_g50 — Thu, 22 Nov 2012 09:51:13 GMT

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

Re: How to sum output of table command

ashu_g50 — Mon, 28 Sep 2020 12:51:14 GMT

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?

Re: How to sum output of table command

jonuwz — Thu, 22 Nov 2012 14:13:35 GMT

What version of splunk ?

Try using the regex method instead

Re: How to sum output of table command

ashu_g50 — Mon, 28 Sep 2020 12:51:46 GMT

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

Re: How to sum output of table command

ashu_g50 — Mon, 26 Nov 2012 07:33:26 GMT

Version is 4.2.3

Re: How to sum output of table command

smolcj — Thu, 29 Nov 2012 12:16:39 GMT

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