How to find stats percentage of a field and timechart by week?

[sgundeti]

HI ,

I am using below command to find the percentage stats over time but I am not seeing required chart. Here is my search format.

..my search earliest =-1mon | timechart span=1w count by | eventstats sum(count) as total | eval percent=(100*count/total) | strcat percent "%" percent

I am seeing time(1 week span) on x-axis and count on y-axis but I wanted to see percentage on y-axis.

I understand that I am calculating percentage after my reporting command but I have to use the count over in calculating percentage. I don't know who to achieve this.

Also I am not seeing percentage I calculated in charting table when I use timechart, and If I use stats by myField as charting command I am seeing percentage in my table.

If I use as below search format I am not seeing report at all; I am not sure if I can use stats and timechart in single search query.

earliest =-1mon | stats count by | eventstats sum(count) as total | eval percent=(100*count/total) | strcat percent "%" percent | timechart span=1w percent by

I guess above command has malfunction , not finding way to fix this.

I tried with suggestion by "aweitzman" from below link but never formed a chart for me :

http://answers.splunk.com/answers/168646/timechart-wtih-eval-cant-recognize-fields.html

Is there a way for fix this??

Any Help would be highly appreciated.

Thanks in advance.

[martin_mueller · Accepted solution]

I'm still not sure what you want to sum up and what percentages you need, so here's a few options:

base search | timechart count by useragent | addtotals fieldname=_Total | foreach * [eval <<FIELD>> = '<<FIELD>>' * 100 / _Total] | fields - _Total

That'll calculate the total for each row and then the percentage for each browser of that row total.

base search | eventstats count by useragent | eval fraction = 100 / count | timechart sum(fraction) by useragent

That'll calculate the total for each browser and then the percentage for each time bucket of that browser total.

base search | eventstats count | eval fraction = 100 / count | timechart sum(fraction) by useragent

That'll calculate the grand total and then the percentage for each time bucket and browser for that grand total.

Give all of those a whirl and see if one happens to be what you need. Note, expect small rounding errors in the fraction approaches. If those are an issue there may be other ways to calculate the same thing that might not include rounding errors.
Also remember, adding a percentage sign to those numbers will turn them into a string and make charting fail.

[martin_mueller]

Ah I see - yeah, Splunk 5 doesn't have foreach. For that purpose I have written the scale command available here: http://apps.splunk.com/app/1397

It's been a while since I've used it, but I believe the search I posted earlier should look like this using the scale command instead of foreach:

base search | timechart count by useragent | addtotals fieldname=_Total | scale field=_Total pattern="^[^_]" | scale scale=100 pattern="^[^_]" round=2 inverse=t

The first part with timechart and addtotals is the same. The first scale command divides every field not starting with an underscore (so not _time for example) by the _Total field, the second scale command multiplies (inverse=t) those by 100 and rounds to two decimal places for easy readability.

In fact, that's the third example I listed in the documentation of the Scale Command app at http://apps.splunk.com/app/1397/#/documentation 🙂

[sgundeti]

Wonderful! It works like champ!! Thank a lot Martin.
I am little confused in understanding with your command. Can you explain with little more detail on how does it is calculating the percentage for each of those fields.

[martin_mueller]

Okay... the first search I posted in the answer should do just that.

[sgundeti]

I am using splunk 5.0.5 version, I think foreach is not available there, do I have any alternative for foreach?

[martin_mueller]

Have you tried using the count as it is and setting the chart to a stacked bar chart with scaling to 100%? That might just be exactly what you're looking for.

[sgundeti]

Yes, you are correct and I tried doing so.I Can see percentage on y-axis, but when I do mouse over on each useragent, still I am seeing the count. Percentage would be more interesting for me.

[sgundeti]

thankyou Martin,

Here is my exact search query and I have masked few values here

index=mobil* earliest = -6mon | eval useragent = case(
useragent LIKE "ABC%", "ABC",
useragent == "ABC-android-app", "ABC-android",
useragent LIKE "%UNAVAILABLE%", "AB-android",
useragent LIKE "Rest%", "AB-win",

useragent LIKE "ABCdroid%", "ABCDroid",
useragent LIKE "ABCdroid%", "ABCDroid",
useragent LIKE "Java/1.7%", "qwert",
useragent LIKE "%", useragent)
| stats count by useragent | eventstats sum(count) as total | eval percent=(count/total)*100

OUTPUT :

useragent count percent total

ABCDroid 121742 16.185415 752171
ABC 416198 55.332896 752171
ABC-android 200405 26.643542 752171
ABC-win 840 0.111677 752171
Mozilla/5.0 544 0.072324 752171
NativeHost 4689 0.623395 752171
qwert 7308 0.971588 752171

[sgundeti]

Continue..

Now, I wanted to change this chart with respect to time for over last 6months, with percentage on y-axis and _time on x-axis .

If I replace "stats" command with "timechart" in above query I can see column chart with count on y-axis and _Time for each useragent on x-axis .

Can I get the percent (instead of the count on y-axis) in the result?

Hope my question is direct and simple this time, and no more confusion.

Apologize for not being so clear on articulating my requirement.

[sgundeti]

I am trying to add the count by each browser and then counting the total events as in "total" and finding the the percentage for each useragent, and I wanted to plot this with respect to time, for last 6months, week by week by what percentage useragent is trending.

Hope this gives your more info.

Sorry If I make more complicated to make you understand.

[martin_mueller]

Okay, so your (simplified) results after the timechart look like this?

_time   IE  Chrome  Firefox
...     12       34        56

If that's the case then the eventstats sum(count) as total is going to do nothing due to not seeing a field called count.

Are you trying to add up one row into a Total field for each row or are you trying to add up the entire table into one grand total?

[sgundeti]

Oops!! Sorry , Its missing!! Yes actual field is there which is categorical field, for example(user browser agent)

[martin_mueller]

count by - is there an actual field name behind that? I can't see it.