Hi,
We are using Splunk version 5.0.4 in our application. In order to bucket our data and display the buckets in proper order, we use the chart command and then take substr of the field. The following is the query:
index=index_name
| eval SessionDurationAvg= case(SessionDurationAvg < 60, "A< 1min", SessionDurationAvg >= 60 AND SessionDurationAvg < 900, "B1min - 15min", SessionDurationAvg >= 900 AND SessionDurationAvg < 1800, "C15min - 30min",SessionDurationAvg >= 1800 AND SessionDurationAvg < 2700, "D30min - 45min", SessionDurationAvg >= 2700 AND SessionDurationAvg < 3600, "E45min - 1hr", SessionDurationAvg >= 3600, "F> 1hr")
| stats count as Count by _time ServerName DeliveryServiceName ProviderName TrafficType ClientISP ClientNetSpeed Resolution Service_Type UserAgent Genre SessionDurationAvg ClientIP
| lookup bnigeoip clientip as ClientIP output client_city as City
| fields - Count
| chart dc(ClientIP) as "Viewers" over SessionDurationAvg
| eval SessionDurationAvg = substr(SessionDurationAvg, 2, len(SessionDurationAvg))
| sort by "Viewers" desc limit=10
The above query works fine. However, when the SessionDuration field is used in the "by" part of the query, the substr function does not work. The query is pasted below:
index=index_name
| eval SessionDurationAvg= case(SessionDurationAvg < 60, "A< 1min", SessionDurationAvg >= 60 AND SessionDurationAvg < 900, "B1min - 15min", SessionDurationAvg >= 900 AND SessionDurationAvg < 1800, "C15min - 30min",SessionDurationAvg >= 1800 AND SessionDurationAvg < 2700, "D30min - 45min", SessionDurationAvg >= 2700 AND SessionDurationAvg < 3600, "E45min - 1hr", SessionDurationAvg >= 3600, "F> 1hr")
| stats count as Count by _time ServerName DeliveryServiceName ProviderName TrafficType ClientISP ClientNetSpeed Resolution Service_Type UserAgent Genre SessionDurationAvg ClientIP | lookup bnigeoip clientip as ClientIP output client_city as City
| fields - Count
| timechart dc(ClientIP) as "Viewers" by SessionDurationAvg
| eval SessionDurationAvg = substr(SessionDurationAvg, 2, len(SessionDurationAvg))
How can I get it to work so that the SessionDurationAvg field is trimmed when the field is used in "by" clause also?
Thanks
Keerthana
... View more