Splunk Search

What am I doing wrong with my stats table?

indeed_2000
Motivator

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,

Labels (5)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| 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

indeed_2000
Motivator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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))).")")

indeed_2000
Motivator

@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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

indeed_2000
Motivator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

As separate events:

| eval fields=mvrange(1,4)
| mvexpand fields
| eval _count=0
| rename fields as _fields
| foreach *
    [| eval _count=_count+1
    | eval <<FIELD>>=if(_count=_fields,<<FIELD>>,null())]
| rename _fields as fields
| eventstats count as cname by name
| eventstats count as ccolor by color
| eventstats count as cstatus by status
| eventstats count(name) as nametotal
| eventstats count(color) as colortotal
| eventstats count(status) as statustotal
| eval pname=round(100*cname/nametotal,1)
| eval pcolor=round(100*ccolor/colortotal,1)
| eval pstatus=round(100*cstatus/statustotal,1)
| sort 0 -cname -ccolor -cstatus
| uniq
| streamstats global=f count by fields
| fields - fields
| stats values(*) as * by count
| table name cname pname color ccolor pcolor status cstatus pstatus

ITWhisperer
SplunkTrust
SplunkTrust
| 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

indeed_2000
Motivator

@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)))

 

0 Karma

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

indeed_2000
Motivator

does not work!

0 Karma

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

indeed_2000
Motivator

still not!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Really? It works for me

0 Karma

indeed_2000
Motivator

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"

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

| eval name=printf("%04u|%s|%d", 10000-namecount, name, nametotal)
| eval cname=mvmap(name,10000-tonumber(mvindex(split(name,"|"),0)))
etc.

indeed_2000
Motivator

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

Thanks

0 Karma

indeed_2000
Motivator

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

             

 

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 ...