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

[djoobbani]

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? 

[bowesmana]

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

[djoobbani]

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

[bowesmana]

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.

[djoobbani]

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!

[djoobbani]

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_*

[bowesmana]

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.

[djoobbani]

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!

[bowesmana]

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

[yuanliu]

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

[djoobbani]

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!