I think this SPL should do what you are asking.
<base_search>
| stats
count as count
by appName, resultCode
| eventstats
sum(count) as app_total_count
by appName
| eval
pct_of_total=round(('count'/'app_total_count')*100, 3)
| chart
sum(pct_of_total) as pct_of_total
over appName
by resultCode
| fillnull value=0.000
Using dummy data on my local instance I was able to get the output to look like this.
where each cell value represents the percentage of events that occurred with that status code for each app.
For a full overview of how I simulated locally you can reference this code.
| makeresults
| eval
appName="app1",
resultCode=500
| append
[
| makeresults
| eval
appName="app1",
resultCode=500
]
| append
[
| makeresults
| eval
appName="app1",
resultCode=split("404|404|200|404|500|404|200", "|")
]
| append
[
| makeresults
| eval
appName="app2",
resultCode=split("200|200|404|200", "|")
]
| append
[
| makeresults
| eval
appName="app3",
resultCode=split("404|200|200|200|500|404|200", "|")
]
| mvexpand resultCode
``` below is the relevant SPL ```
| stats
count as count
by appName, resultCode
| eventstats
sum(count) as app_total_count
by appName
| eval
pct_of_total=round(('count'/'app_total_count')*100, 3)
| chart
values(pct_of_total) as pct_of_total
over appName
by resultCode
| fillnull value=0.000
Edit:
And to use this at scale (more that 10 unique status_codes) you would need to add a limit=<number> parameter to the chart command, otherwise it will populate another values named "OTHER" after 10 unique values are transformed.
This is what a bigger table would look like
Notice there is also another field at the right of the table to give more context at the total count of status codes seen for each application so that percentages can be inferred to a total count if desired.
Code to do this would look something like this. (Added comments for each line to add detail)
<base_search>
``` simple count of events for each unique combo of appName/resultCode values ```
| stats
count as count
by appName, resultCode
``` sum up the counts accross each unique appName ```
| eventstats
sum(count) as app_total_count
by appName
``` add aditional rows as a Total count for each unique appName value (This is optional since this step is just providing more context to the final table) ```
| appendpipe
[
| stats
sum(count) as count
by appName
| eval
resultCode="Total",
app_total_count='count'
]
``` calculate percentage of occurrence for a particular resultCode across each unique appName
with the exception of the Total rows, for these we want to just carry over the total app count (for context)
```
| eval
pct_of_total=if(
'resultCode'=="Total",
'count',
round(('count'/'app_total_count')*100, 2)
)
```
This chart command builds a sort contingency table, using the derived 'pct_of_total' values and putting them in there respective cell when
appName values are in the left-most column and using the values from resultCode as the table header.
Note: If a specific resultCode that showed for one app that did not show for another, then charting this way will leave a blank slot in that cell.
To get around this we will use a fillnull command in the next step of the search.
Adding the limit=<number> parameter sets your upper limit of the number of columns to generate for each unique resultCode value. This defaults to 10 if not specified and for resultCode values exceeding the top 10 will fall into the bucket "OTHER" and be tabled that way.
Example: If you only want to see the top 3 resultCode values in the table and dont want an additional column "OTHER", you have the option to also set 'useother=false' along with 'limit=3'
```
| chart limit=100 useother=true
sum(pct_of_total)
over appName,
by resultCode
``` renaming the "Total" column name to something more descriptive ```
| rename
Total as "Total Count for App"
``` fill any blank cells with 0 as this is the implied percentage if it is null ```
| fillnull value=0.00
I think this SPL should do what you are asking.
<base_search>
| stats
count as count
by appName, resultCode
| eventstats
sum(count) as app_total_count
by appName
| eval
pct_of_total=round(('count'/'app_total_count')*100, 3)
| chart
sum(pct_of_total) as pct_of_total
over appName
by resultCode
| fillnull value=0.000
Using dummy data on my local instance I was able to get the output to look like this.
where each cell value represents the percentage of events that occurred with that status code for each app.
For a full overview of how I simulated locally you can reference this code.
| makeresults
| eval
appName="app1",
resultCode=500
| append
[
| makeresults
| eval
appName="app1",
resultCode=500
]
| append
[
| makeresults
| eval
appName="app1",
resultCode=split("404|404|200|404|500|404|200", "|")
]
| append
[
| makeresults
| eval
appName="app2",
resultCode=split("200|200|404|200", "|")
]
| append
[
| makeresults
| eval
appName="app3",
resultCode=split("404|200|200|200|500|404|200", "|")
]
| mvexpand resultCode
``` below is the relevant SPL ```
| stats
count as count
by appName, resultCode
| eventstats
sum(count) as app_total_count
by appName
| eval
pct_of_total=round(('count'/'app_total_count')*100, 3)
| chart
values(pct_of_total) as pct_of_total
over appName
by resultCode
| fillnull value=0.000
Edit:
And to use this at scale (more that 10 unique status_codes) you would need to add a limit=<number> parameter to the chart command, otherwise it will populate another values named "OTHER" after 10 unique values are transformed.
This is what a bigger table would look like
Notice there is also another field at the right of the table to give more context at the total count of status codes seen for each application so that percentages can be inferred to a total count if desired.
Code to do this would look something like this. (Added comments for each line to add detail)
<base_search>
``` simple count of events for each unique combo of appName/resultCode values ```
| stats
count as count
by appName, resultCode
``` sum up the counts accross each unique appName ```
| eventstats
sum(count) as app_total_count
by appName
``` add aditional rows as a Total count for each unique appName value (This is optional since this step is just providing more context to the final table) ```
| appendpipe
[
| stats
sum(count) as count
by appName
| eval
resultCode="Total",
app_total_count='count'
]
``` calculate percentage of occurrence for a particular resultCode across each unique appName
with the exception of the Total rows, for these we want to just carry over the total app count (for context)
```
| eval
pct_of_total=if(
'resultCode'=="Total",
'count',
round(('count'/'app_total_count')*100, 2)
)
```
This chart command builds a sort contingency table, using the derived 'pct_of_total' values and putting them in there respective cell when
appName values are in the left-most column and using the values from resultCode as the table header.
Note: If a specific resultCode that showed for one app that did not show for another, then charting this way will leave a blank slot in that cell.
To get around this we will use a fillnull command in the next step of the search.
Adding the limit=<number> parameter sets your upper limit of the number of columns to generate for each unique resultCode value. This defaults to 10 if not specified and for resultCode values exceeding the top 10 will fall into the bucket "OTHER" and be tabled that way.
Example: If you only want to see the top 3 resultCode values in the table and dont want an additional column "OTHER", you have the option to also set 'useother=false' along with 'limit=3'
```
| chart limit=100 useother=true
sum(pct_of_total)
over appName,
by resultCode
``` renaming the "Total" column name to something more descriptive ```
| rename
Total as "Total Count for App"
``` fill any blank cells with 0 as this is the implied percentage if it is null ```
| fillnull value=0.00
