Splunk Search

Issue while using substr function after chart command

keerthana_k
Communicator

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

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

In the second query you are using "timechart-by" in which the term appear apfter "by" clause will get changed from "column value in a row" to a column (name) itself hence your eval-substring command will not work [as it operations on a column of a row].

To see the trimmed values as column in the timechart, move the substr function before timechart.

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

In the second query you are using "timechart-by" in which the term appear apfter "by" clause will get changed from "column value in a row" to a column (name) itself hence your eval-substring command will not work [as it operations on a column of a row].

To see the trimmed values as column in the timechart, move the substr function before timechart.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...