topic Re: stats table in Splunk Search

What am I doing wrong with my stats table?

indeed_2000 — Wed, 01 Jun 2022 15:32:45 GMT

Hi I have table like this:

name color status

jack red fail

jack blue fail

daniel green pass

expected output:

name color status

jack(2) red(1) fail(2)

daniel(1) blue(1) pass(1)

green(1)

any idea?

Thanks,

Re: stats table

ITWhisperer — Wed, 01 Jun 2022 11:57:58 GMT

| eventstats count as namecount by name | eventstats count as colorcount by color | eventstats count as statuscount by status | eval name=name."(".namecount.")" | eval color=color."(".colorcount.")" | eval status=status."(".statuscount.")" | stats values(name) as name values(color) as color values(status) as status

Re: stats table

indeed_2000 — Wed, 01 Jun 2022 12:19:16 GMT

@ITWhisperer need to sort each column from top to down.

Re: stats table

ITWhisperer — Wed, 01 Jun 2022 13:02:11 GMT

multivalue fields returned by values() are sorted lexicographically so you might need to do some manipulation to get these values sorted in a different order

| eventstats count as namecount by name | eventstats count as colorcount by color | eventstats count as statuscount by status | eval name=printf("%04u %s", 10000-namecount, name) | eval color=printf("%04u %s", 10000-colorcount, color) | eval status=printf("%04u %s", 10000-statuscount, status) | stats values(name) as name values(color) as color values(status) as status | eval name=mvmap(name,mvindex(split(name," "),1)."(".(10000-tonumber(mvindex(split(name," "),0))).")") | eval color=mvmap(color,mvindex(split(color," "),1)."(".(10000-tonumber(mvindex(split(color," "),0))).")") | eval status=mvmap(status,mvindex(split(status," "),1)."(".(10000-tonumber(mvindex(split(status," "),0))).")")

Re: stats table

indeed_2000 — Wed, 01 Jun 2022 15:04:15 GMT

@ITWhisperer Thank you for answer,

1-is it possible to do this with simpler SPL? e.g seperate count column?

2-is it possible add percentage beside count?

expected output:

name count percentage color count percentage

jack 2 66.6 red 1 33.3

daniel 1 33.3 blue 1 33.3

green 1 33.3

Re: stats table

ITWhisperer — Wed, 01 Jun 2022 15:09:33 GMT

Simpler SPL, yet you want a more complex output?

Your expected output is not possible because you have multiple fields with the same names.

Even if you had different names, you are trying to treat the fields in the events as independent from each other. To do this, you may have to split the fields into separate events, do your calculations, then recombine them into a single event - this is definitely not simpler.

Re: stats table

indeed_2000 — Wed, 01 Jun 2022 15:30:06 GMT

how about this?

expected output:

Name cName %Name Color cColor %Color

jack 2 66.6 red 1 33.3

daniel 1 33.3 blue 1 33.3

green 1 33.3

Re: stats table

ITWhisperer — Wed, 01 Jun 2022 15:45:25 GMT

| eventstats count as namecount by name | eventstats count as colorcount by color | eventstats count as statuscount by status | eventstats count(name) as nametotal | eventstats count(color) as colortotal | eventstats count(status) as statustotal | eval name=printf("%04u %s %d", 10000-namecount, name, nametotal) | eval color=printf("%04u %s %d", 10000-colorcount, color, colortotal) | eval status=printf("%04u %s %d", 10000-statuscount, status, statustotal) | stats values(name) as name values(color) as color values(status) as status | eval cname=mvmap(name,10000-tonumber(mvindex(split(name," "),0))) | eval ccolor=mvmap(color,10000-tonumber(mvindex(split(color," "),0))) | eval cstatus=mvmap(status,10000-tonumber(mvindex(split(status," "),0))) | eval pname=mvmap(name,100*(10000-tonumber(mvindex(split(name," "),0)))/tonumber(mvindex(split(name," "),2))) | eval pcolor=mvmap(color,100*(10000-tonumber(mvindex(split(color," "),0)))/tonumber(mvindex(split(color," "),2))) | eval pstatus=mvmap(status,100*(10000-tonumber(mvindex(split(status," "),0)))/tonumber(mvindex(split(status," "),2))) | eval name=mvmap(name,mvindex(split(name," "),1)) | eval color=mvmap(color,mvindex(split(color," "),1)) | eval status=mvmap(status,mvindex(split(status," "),1)) | fields name cname pname color ccolor pcolor status cstatus pstatus

Re: stats table

ITWhisperer — Wed, 01 Jun 2022 16:13:01 GMT

As separate events:

Re: stats table

indeed_2000 — Wed, 01 Jun 2022 16:49:47 GMT

@ITWhisperer about this line, how can i limit to 2 decimal places like this 33.33?

| eval pname=mvmap(name,100*(10000-tonumber(mvindex(split(name," "),0)))/tonumber(mvindex(split(name," "),2)))

Re: stats table

ITWhisperer — Wed, 01 Jun 2022 17:01:42 GMT

| eval pname=mvmap(name,round(100*(10000-tonumber(mvindex(split(name," "),0)))/tonumber(mvindex(split(name," "),2),2)))

Re: stats table

indeed_2000 — Wed, 01 Jun 2022 17:11:04 GMT

does not work!

Re: stats table

ITWhisperer — Wed, 01 Jun 2022 17:13:30 GMT

| eval pname=mvmap(name,round(100*(10000-tonumber(mvindex(split(name," "),0)))/tonumber(mvindex(split(name," "),2)),2))

Re: stats table

indeed_2000 — Wed, 01 Jun 2022 17:20:29 GMT

still not!

Re: stats table

ITWhisperer — Wed, 01 Jun 2022 17:24:22 GMT

Really? It works for me

Re: stats table

indeed_2000 — Wed, 01 Jun 2022 19:02:34 GMT

you right it's typo, now fixed

i have some "date" or "color" like this: 'Mon May 30 00:00:00 USDT 2022' or ''

FYI: some of them contain space between Single quotation like this 'Mon May 30 00:00:00 USDT 2022', some of them are empty just has Single quotation like this ''

not show them correcty and won't calculate percentage of them.

current output:

Date cDate %Date Color cColor %Color

'Mon 2 '' 1

'Today' 1 33.0 'red' 2 66.0

Re: stats table

indeed_2000 — Thu, 02 Jun 2022 08:23:19 GMT

Thanks it work, i just add this to the beginning of spl to remove spaces that exist in fields and work perfectly.

... | rex mode=sed field=A "s/ //g"

Re: stats table

ITWhisperer — Thu, 02 Jun 2022 09:01:16 GMT

Alternatively, where I have used a space to delimit parts in the printf and split, use a different character

Re: stats table

indeed_2000 — Thu, 02 Jun 2022 09:37:45 GMT

i remove the part that i use in beginning and use your suggestion. work like charm 🙂

Thanks