Can’t figure out how to display a percentage in another column grouped by its total count per ‘Code’ only.
For instance code ‘A’ grand total is 35 ( sum of totals in row 1&2)
The percentage for row 1 would be (25/35)*100 = 71.4 or 71
The percentage for row 2 would be (10/35)*100 =28.57 or 29
Then the next group (code “B”) would display their percentage of their grand total.
Etc.
How to do this and display the percentage in the Single Value widget?
Code rounded(DelayInMin) TotalCount Percentage
A 0 25 71
A 1 10 29
B 0 100 100
C 0 5 18
C 1 10 36
C 2 13 46
Like this:
|makeresults | eval raw="A 0 25 71:::A 1 10 29:::B 0 100 100:::C 0 5 18:::C 1 10 36:::C 2 13 46"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| fields - _time
| rex "^(?<code>\S+)\s+(?<subcode>\S+)\s+(?<count>\d+)\s+(?<percentage>\d+)$"
| table code subcode count percentage
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| eventstats sum(count) AS _grandtotal
| chart sum(count) OVER _grandtotal BY code
| foreach * [ eval <<FIELD>> = round((100 * <<FIELD>> / _grandtotal), 2) ]
If you are using count
for your stats
aggregation function, then you can use the top
command which automatically generates percentages. You can also use eventstats
after the fact to calculate a grand total
value that can then be used to calculate percentages.
still correcting LOL
|29.00| |100.00| |18.00|
the minimum percentage in the Single value widget from these results
Code Count Percentage
A 0 25 71
A 1 10 29
B 0 100 100
C 0 5 18
C 1 10 36
C 2 13 46
Can you give an example giving these results?
Code Count Percentage
A 0 25 71
A 1 10 29
B 0 100 100
C 0 5 18
C 1 10 36
C 2 13 46
Where the results would display this in a Single Value widget (for all 3)
A B C
|25.00| |100.00| |18.00|
You gave a header with 3 fields but rows with 4 fields...???
Sorry
ABC
|20.00| |100.00| |18.00|
Yep, there is a Critically Level than Count Percentagee
Code Criticality_Level Count Percentage
A 0 25 71
A 1 10 29
B 0 100 100
C 0 5 18
C 1 10 36
C 2 13 46
Where the results would display this in a Single Value widget (for all 3)
For those with the min Criticality Level
A B C
|25.00| |100.00| |18.00|
Hello, the percentage is now displaying in the results - thank you.
However on the Single Value widget, I want to show the lowest percentage for each code. It only show code A
and the trellis option do not show split by code, only an aggregation. Is there a way to fix this visualization?
A B C
|25.00| |100.00| |18.00|
Instead it is showing
|25.00| only and have an aggregation split option.
I
Thank you for you quick response, The results do not show the percentage column. Very new to SPLUNK, and there must be additional code for display?
Below is the search anywhere search, You can replace the search per your requirement.
| makeresults
| eval Text="A 0 25 71, A 1 10 29, B 0 100 100, C 0 5 18, C 1 10 36, C 2 13 46"
| makemv Text delim=","
| mvexpand Text
| makemv Text delim=" "
| eval Code=mvindex(Text,0)
| eval Rounded=mvindex(Text,1)
| eval TotalCount=mvindex(Text,2)
| eval Percentage=mvindex(Text,3)
| table Code Rounded TotalCount Percentage
| convert num(TotalCount)
| eventstats Sum(TotalCount) as Total by Code | eval Percentage=round(TotalCount/Total*100,0)
Or Run the below search as mentioned by @renjith.nair .
"your search to get the values"|eventstats sum(TotalCount) as _total by Code|eval Percentage=round((TotalCount/_total)*100,2)
He created another field called perc
, you can either rename it to Percentage or create your own as mention in my above search.
@msmullinax ,
"your search to get the values"|eventstats sum(TotalCount) as _total by Code|eval perc=round((TotalCount/_total)*100,2)