topic Re: get the chart of count and percentage by, in one column in Splunk Search

How to get the chart of count and percentage by- in one column?

djoobbani — Tue, 16 Aug 2022 00:00:45 GMT

So i am representing endpoint url (y-axis) and http status code (x-axis).

I can show the count of each url & status code using chart like so:

<base search> | chart count by url_path, http_status_code

Now, i need to add another item into the chart command to show the percentage of each count in addition to count, so that i get something like this together: 48 (72%).

Also i know how to calculate the percentage as such:

eventstats sum(count) as total | eval percent=100*count/total | strcat percent "%" percent.

Can you please tell me how to construct the chart command to encapsulate the count and percentage together?

Re: get the chart of count and percentage by, in one column

bowesmana — Mon, 15 Aug 2022 23:53:15 GMT

Assuming you mean that percentage is the percentage of status codes per url_path, then this would construct the data,

your_search... | addtotals fieldname=total | foreach 2* 3* 4* 5* [ eval "percent_<<FIELD>>"=100*'<<FIELD>>'/total, "<<FIELD>>"='<<FIELD>>'." (".'percent_<<FIELD>>'."%)" ] | fields - percent_* total

but the problem is that the count is no longer a number, therefore will not chart as a number.

With this

| addtotals fieldname=total | foreach 2* 3* 4* 5* [ eval "percent_<<FIELD>>"=100*'<<FIELD>>'/total ] | fields - total

you would end up with percent_XXX fields that you could overlay onto a second Y axis, but that's not particularly pretty...

Re: get the chart of count and percentage by, in one column

djoobbani — Tue, 16 Aug 2022 01:43:53 GMT

Thanks but i need to use chart command to show both count and percentage by the url (y column) and status code e.g. 54 (81%) The above only shows the log information

Re: get the chart of count and percentage by, in one column

bowesmana — Tue, 16 Aug 2022 04:02:06 GMT

You are already using the chart command in your search... What do you mean by only showing log information? I suggested this option

your_search... | addtotals fieldname=total | foreach 2* 3* 4* 5* [ eval "percent_<<FIELD>>"=100*'<<FIELD>>'/total, "<<FIELD>>"='<<FIELD>>'." (".'percent_<<FIELD>>'."%)" ] | fields - percent_* total

where the text "your_search" is your original search where you are using the chart command...

However, the fundamental issue still applies. You cannot show a number AND a percentage as a Y axis value, as as soon as you combine 54 and (81%) the combination is no longer a number, i.e. "54 (81%)" is not a numeric value.

Re: get the chart of count and percentage by, in one column

djoobbani — Tue, 16 Aug 2022 15:57:28 GMT

Hi bowesmana:

So i got this search working for me:

<my search> | chart count by path_template, http_status_code | addtotals fieldname=total
| foreach 2* 3* 4* 5* [ eval "percent_<<FIELD>>"=round(100*'<<FIELD>>'/total), "<<FIELD>>"='<<FIELD>>'." (".'percent_<<FIELD>>'."%)" ]
| fields - percent_* total

As u can see the last column doesn't show the total number (both the title and the numbers) based on the above search. Do you know what is wrong? Thanks!

Re: get the chart of count and percentage by, in one column

djoobbani — Tue, 16 Aug 2022 16:04:31 GMT

This may be related to the issue u mentioned about count turning into a non numeric value, correct?

Is there any other way to calculate the total (both vertically and horizontally)?

Thanks!

Re: get the chart of count and percentage by, in one column

yuanliu — Tue, 16 Aug 2022 19:22:26 GMT

bwosemana's formula depends on the existence of a field named total, which according to your original description would be derived from eventstats sum(count) as total.

<my search> | chart count by path_template, http_status_code | addtotals fieldname=total
| foreach 2* 3* 4* 5* [ eval "percent_<<FIELD>>"=round(100*'<<FIELD>>'/total), "<<FIELD>>"='<<FIELD>>'." (".'percent_<<FIELD>>'."%)" ]
| fields - percent_* total

In this code, any information carried by total would have been lost in chart. I haven't examined the full logic in this search but if you change to

<my search> | chart count AS total by path_template, http_status_code | addtotals fieldname=total | foreach 2* 3* 4* 5* [ eval "percent_<<FIELD>>"=round(100*'<<FIELD>>'/total), "<<FIELD>>"='<<FIELD>>'." (".'percent_<<FIELD>>'."%)" ] | fields - percent_* total

you should get some results. (Although I suspect that the results may not be what you expected.)

Re: get the chart of count and percentage by, in one column

bowesmana — Wed, 17 Aug 2022 01:22:17 GMT

You are removing the total field in the final line

| fields - percent_* total

if you want to retain 'total' then remove total from the above line.

Note that the VALUE column is probably coming from your data somewhere and is some odd value of your http_status_code field.

Re: get the chart of count and percentage by, in one column

djoobbani — Thu, 18 Aug 2022 18:15:02 GMT

Thanks for your help bowesmana. Actually i finally got the everything working with the following SPL:

<basic search> | chart count by path_template, http_status_code | addtotals fieldname=total
| foreach 2* 3* 4* 5* [ eval "percent_<<FIELD>>"=round(100*'<<FIELD>>'/total), "<<FIELD>>"='<<FIELD>>'." (".'percent_<<FIELD>>'."%)" ] | fields - percent_*

Now, i need to make two changes to this query.

1- Do NOT show the total column

2- For cases where the percentage is either 0 OR 100 do not show anything

Do u know how to change the query to accomplish those?

I am really extra appreciative for your help! Thank you very much!

Re: get the chart of count and percentage by, in one column

djoobbani — Thu, 18 Aug 2022 18:38:38 GMT

Sorry in the previous post i showed the incorrect picture.

Here is the correct picture for this query:

| chart count by path_template, http_status_code | addtotals fieldname=total
| foreach 2* 3* 4* 5* [ eval "percent_<<FIELD>>"=round(100*'<<FIELD>>'/total), "<<FIELD>>"='<<FIELD>>'." (".'percent_<<FIELD>>'."%)" ] | fields - percent_*

Re: get the chart of count and percentage by, in one column

bowesmana — Mon, 22 Aug 2022 01:36:05 GMT

Instead of your existing foreach block, replace it with this

| foreach 2* 3* 4* 5* [ eval "<<FIELD>>"=if('<<FIELD>>'=total OR '<<FIELD>>'=0, "", '<<FIELD>>'."(".round(100*'<<FIELD>>'/total)."%)") ]

that will only show a value if the field value is not equal to total or 0.

Add the fields - total to remove the total field at the end