I want to have a colour-coded column chart for the average amount of time that an incident has been open by its severity.
So far, I think I have achieved most of this with the following search query:
index=* | where match(status, "resolved")
| stats earliest(creation_time) as creation_time, earliest(modification_time) as resolve_time, latest(severity) as severity by incident_id
| eval creation_time_epoch = strptime(creation_time, "%Y/%m/%d %H:%M:%S") | eval resolve_time_epoch = strptime(resolve_time, "%Y/%m/%d %H:%M:%S") | eval timedifference_hours = ((resolve_time_epoch - creation_time_epoch) / 60) / 60
| stats avg(timedifference_hours) as timedifference_hours_average by severity | eval timedifference_hours_average = round(timedifference_hours_average, 2)
| table severity, timedifference_hours_average
The problem is that, probably due to a flaw with my search query, the legend is not per severity and, seemingly as a result, the dashboard XML <option name="charting.fieldColors">{"low": 0x3391FF, "medium": 0xF8BE34, "high": 0xDF4D58}</option> is not applying.
Can anyone point me in the right direction?
Thanks.
work around:
| makeresults
| eval _raw="severity,time_diff_avg
high,9.09
low,9.08
medium,19.51"
| multikv forceheader=1
| table severity time_diff_avg
| rex "(?<comment>(?# the logic))"
| xyseries severity severity time_diff_avg
| table severity high medium low
It's the only way to be satisfied with everything you want to do.
sample:
<dashboard>
<label>chart color</label>
<row>
<panel>
<chart>
<search>
<query>| makeresults
| eval _raw="severity,time_diff_avg
high,9.09
low,9.08
medium,19.51"
| multikv forceheader=1
| table severity time_diff_avg
| eval severities="severity"
| xyseries severities severity time_diff_avg</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="charting.axisTitleX.visibility">collapsed</option>
<option name="charting.chart">column</option>
<option name="charting.drilldown">all</option>
<option name="charting.fieldColors">{"low": 0x3391FF, "medium": 0xF8BE34, "high": 0xDF4D58}</option>
</chart>
</panel>
</row>
</dashboard>
Hi @to4kawa ,
Thanks for that. It's very promising.
Is there any way to preserve the following aspects of my method with yours?
How it currently looks using my method:
How it currently looks using your method:
Thanks.