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.
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.
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.
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 🙂
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.
Okay... the first search I posted in the answer should do just that.
I am using splunk 5.0.5 version, I think foreach is not available there, do I have any alternative for foreach?
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.
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.
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
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.
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.
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?
Oops!! Sorry , Its missing!! Yes actual field is there which is categorical field, for example(user browser agent)
count by
- is there an actual field name behind that? I can't see it.