I have been searching about this for the last couple of days. I don't think Splunk have this feature but I just want to make sure if I was right. So I have this search:
index="monthlycdr" | eval "Call Duration"=replace('Call Duration',"\"","") | convert dur2sec("Call Duration") as "Call Duration" | timechart span="1mon" avg("Call Duration") as "TotalCD"
Which give me this result:
But when I covert my search to 00:00:00 format it doesn't show the trend. Here is the new search:
index="monthlycdr" | eval "Call Duration"=replace('Call Duration',"\"","") | convert dur2sec("Call Duration") as "Call Duration" | timechart avg("Call Duration") as "TotalCD"
| eval "TotalCD"=tostring($TotalCD$,"duration") | eval TotalCD=replace(TotalCD,"(\d+):(\d+):(\d+).(\d+)","\1:\2:\3")
Which give me this result:
I want the second search to have trend just like the first search. But I believe I can not do this due to a string conversion. Am I right that Splunk wont be able to do this, at least for now?
@tamduong16, you are right about Single Value result that it has to be numeric in order to be able to show the sparkline
and trend
. So, in order to tackle the scenario there could be following round-about approach:
PS: In order to demo I have used run anywhere search example from Splunk's _internal
index with date_second field to mimic duration(actually it is not, so ignore the data interpretation ;)).
Step 1) Create single value trend-indicator with your existing timechart command with avg()
of duration. Use round(TotalCD,0)
to round off seconds.
Step 2) Set the token tokDuration
as string duration i.e. HH:MM:SS
inside <done>
Search Event Handler.
<done>
<eval token="tokDuration">tostring($result.TotalCD$,"duration")</eval>
</done>
Step 3) Use the token tokDuration
as underLabel using Single Value Simple XML Chart configuration option i.e.
<option name="underLabel">$tokDuration$</option>
PS: This step will give you numerical seconds as Single Value Result and string duration in HH:MM:SS as Single Value Under Label. If this suffices your needs you would not require next step for CSS override. 🙂
Step 4) Use CSS to hide numeric duration
which is the Single value result. Apply CSS to Single Value Under Label to change its font size, weight, color etc. Use translate to shift Under Label to the position of Single Value Result. (this might take some hit and trial with actual position in your dashboard).
PS: We have created single value with <single id="single1">
to apply CSS override only to one single value not all. The Single Value id and CSS Selector would need to be changed as per Use Case.
<html depends="$alwaysHideCSS$">
<style>
#single1 .single-result{
visibility:hidden;
}
#single1 .under-label{
font-size: 200% !important;
font-weight: bold !important;
transform: translate(-40px,-30px);
fill:#333333 !important;
}
</style>
</html>
Following is the complete run anywhere dashboard example code for testing:
<dashboard>
<label>Duration as HH:MM:SS in Single Value</label>
<row>
<panel>
<html depends="$alwaysHideCSS$">
<style>
#single1 .single-result{
visibility:hidden;
}
#single1 .under-label{
font-size: 200% !important;
font-weight: bold !important;
transform: translate(-40px,-30px);
fill:#333333 !important;
}
</style>
</html>
<single id="single1">
<search>
<query>index=_internal sourcetype=splunkd log_level!="INFO"
| timechart avg(date_second) as "TotalCD"
| eval TotalCD=round(TotalCD,0)</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
<done>
<eval token="tokDuration">tostring($result.TotalCD$,"duration")</eval>
</done>
</search>
<option name="colorBy">value</option>
<option name="colorMode">none</option>
<option name="drilldown">none</option>
<option name="numberPrecision">0</option>
<option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
<option name="rangeValues">[0,30,70,100]</option>
<option name="showSparkline">1</option>
<option name="showTrendIndicator">1</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
<option name="trendColorInterpretation">standard</option>
<option name="trendDisplayMode">absolute</option>
<option name="underLabel">$tokDuration$</option>
<option name="unitPosition">after</option>
<option name="useColors">0</option>
<option name="useThousandSeparators">1</option>
</single>
</panel>
</row>
</dashboard>
@tamduong16, you are right about Single Value result that it has to be numeric in order to be able to show the sparkline
and trend
. So, in order to tackle the scenario there could be following round-about approach:
PS: In order to demo I have used run anywhere search example from Splunk's _internal
index with date_second field to mimic duration(actually it is not, so ignore the data interpretation ;)).
Step 1) Create single value trend-indicator with your existing timechart command with avg()
of duration. Use round(TotalCD,0)
to round off seconds.
Step 2) Set the token tokDuration
as string duration i.e. HH:MM:SS
inside <done>
Search Event Handler.
<done>
<eval token="tokDuration">tostring($result.TotalCD$,"duration")</eval>
</done>
Step 3) Use the token tokDuration
as underLabel using Single Value Simple XML Chart configuration option i.e.
<option name="underLabel">$tokDuration$</option>
PS: This step will give you numerical seconds as Single Value Result and string duration in HH:MM:SS as Single Value Under Label. If this suffices your needs you would not require next step for CSS override. 🙂
Step 4) Use CSS to hide numeric duration
which is the Single value result. Apply CSS to Single Value Under Label to change its font size, weight, color etc. Use translate to shift Under Label to the position of Single Value Result. (this might take some hit and trial with actual position in your dashboard).
PS: We have created single value with <single id="single1">
to apply CSS override only to one single value not all. The Single Value id and CSS Selector would need to be changed as per Use Case.
<html depends="$alwaysHideCSS$">
<style>
#single1 .single-result{
visibility:hidden;
}
#single1 .under-label{
font-size: 200% !important;
font-weight: bold !important;
transform: translate(-40px,-30px);
fill:#333333 !important;
}
</style>
</html>
Following is the complete run anywhere dashboard example code for testing:
<dashboard>
<label>Duration as HH:MM:SS in Single Value</label>
<row>
<panel>
<html depends="$alwaysHideCSS$">
<style>
#single1 .single-result{
visibility:hidden;
}
#single1 .under-label{
font-size: 200% !important;
font-weight: bold !important;
transform: translate(-40px,-30px);
fill:#333333 !important;
}
</style>
</html>
<single id="single1">
<search>
<query>index=_internal sourcetype=splunkd log_level!="INFO"
| timechart avg(date_second) as "TotalCD"
| eval TotalCD=round(TotalCD,0)</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
<done>
<eval token="tokDuration">tostring($result.TotalCD$,"duration")</eval>
</done>
</search>
<option name="colorBy">value</option>
<option name="colorMode">none</option>
<option name="drilldown">none</option>
<option name="numberPrecision">0</option>
<option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
<option name="rangeValues">[0,30,70,100]</option>
<option name="showSparkline">1</option>
<option name="showTrendIndicator">1</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
<option name="trendColorInterpretation">standard</option>
<option name="trendDisplayMode">absolute</option>
<option name="underLabel">$tokDuration$</option>
<option name="unitPosition">after</option>
<option name="useColors">0</option>
<option name="useThousandSeparators">1</option>
</single>
</panel>
</row>
</dashboard>
@niketnilay, Thanks for the answer it works great. Is there anyway I could get the trend number to be in the hh:mm:ss as well?
On similar lines to above Single value expects the trend also to be numeric. So you will need a workaround for that too. However, it is a bit complicated.
Since trend is based on a Compared To
value, which is by default auto
. Implying compare last value
with second last value
to get the trend. If you are not changing that you can use the following approach:
1) Make your Single Value Search query as Base Search id="baseSearch"
2) Post process another search to get only last two results from base search timechart results and then pick 2nd last using | tail 2 | head 1
. PS: This is where change might be required if you are not comparing last result with 2nd last for trending. Also I have handled NULL data with fillnull in base search. However, you should consider handling no results scenario by defaulting the token tokTrendDiff
to 00:00:00
or else it ill remain unset.
3) Hide Delta value in Trend using CSS selector #single1 .single-value-delta .delta-label
4) Add tokTrendDiff
to Single Value Simple XML configuration underLabel
5) Change the alignment of Single Value Result and Delta Value Trend through CSS transform
Please find the updated result:
Refer to the complete run anywhere dashboard example below:
<dashboard>
<label>Duration as HH:MM:SS in Single Value as Result and Trend</label>
<row>
<panel>
<html depends="$alwaysHideCSS$">
<style>
#single1 .single-result, #single1 .single-value-delta .delta-label{
visibility:hidden;
}
#single1 .under-label{
font-size: 200% !important;
font-weight: bold !important;
transform: translate(-20px,-20px);
fill:#333333 !important;
}
</style>
</html>
<search base="baseSearch">
<query>
| tail 2
| head 1
</query>
<done>
<eval token="tokTrendDiff">tostring($result.TotalCD$,"duration")</eval>
</done>
</search>
<single id="single1">
<search id="baseSearch">
<query>index=_internal sourcetype=splunkd log_level!="INFO"
| timechart avg(date_second) as "TotalCD"
| fillnull value=0 TotalCD
| eval TotalCD=round(TotalCD,0)</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
<done>
<eval token="tokDuration">tostring($result.TotalCD$,"duration")</eval>
</done>
</search>
<option name="colorBy">value</option>
<option name="colorMode">none</option>
<option name="drilldown">none</option>
<option name="numberPrecision">0</option>
<option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
<option name="rangeValues">[0,30,70,100]</option>
<option name="showSparkline">1</option>
<option name="showTrendIndicator">1</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
<option name="trendColorInterpretation">standard</option>
<option name="trendDisplayMode">absolute</option>
<option name="underLabel">$tokDuration$ ( $tokTrendDiff$ )</option>
<option name="unitPosition">after</option>
<option name="useColors">0</option>
<option name="useThousandSeparators">1</option>
</single>
</panel>
</row>
</dashboard>
I'm not sure if this will work, but have you tried using fieldformat
to format the display value instead of applying a straight eval
to it? Here's some info on fieldformat
:
http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Fieldformat