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,
| 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
| 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
@ITWhisperer need to sort each column from top to down.
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))).")")
@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
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.
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
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
| 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
@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)))
| eval pname=mvmap(name,round(100*(10000-tonumber(mvindex(split(name," "),0)))/tonumber(mvindex(split(name," "),2),2)))
does not work!
| eval pname=mvmap(name,round(100*(10000-tonumber(mvindex(split(name," "),0)))/tonumber(mvindex(split(name," "),2)),2))
still not!
Really? It works for me
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"
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.
i remove the part that i use in beginning and use your suggestion. work like charm 🙂
Thanks
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