How to sum output of table command

[ashu_g50]

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.

[Ayn]

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

[Ayn]

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.

[ashu_g50]

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

[ashu_g50]

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

[ashu_g50]

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

[jonuwz]

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]

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

[ashu_g50]

Version is 4.2.3

[ashu_g50]

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

[jonuwz]

What version of splunk ?

Try using the regex method instead

[ashu_g50]

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?

[ashu_g50]

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