I want the results of the following query to be sorted by orders I declare.
For some reason, it does not work so I might missing something:
my_query | eval _time = time| bucket _time span=1d | stats count by _time, app_risk | eval risk_order=case(app_risk=="Unknown",0, app_risk=="Very Low",1, app_risk=="Low",2, app_risk=="Medium",3, app_risk=="High",4, app_risk=="Critical",5) | sort -risk_order | xyseries _time,risk_order,count | rename "0" as "Unknown" "1" as "Very Low" "2" as "Low" "3" as "Medium" "4" as "High" "5" as "Critical"
Anyone?
Thanks!
@shayhibah ,
Created dummy series using
index=_* earliest=-15m|eval app_risk=case(sourcetype="splunkd","Very Low",sourcetype="audittrail","Medium",sourcetype="kvstore","Low",sourcetype="splunkd_access","Critical",sourcetype="splunk_web_access","High")|search app_risk=*
|bucket span=5m _time|chart count over _time by app_risk| fields _time,Critical,High,Medium,Low,"Very Low"
Plotted on to a dashboard panel
<dashboard>
<label>testing</label>
<row>
<panel>
<chart>
<search>
<query>index=_* earliest=-15m|eval app_risk=case(sourcetype="splunkd","Very Low",sourcetype="audittrail","Medium",sourcetype="kvstore","Low",sourcetype="splunkd_access","Critical",sourcetype="splunk_web_access","High")|search app_risk=*
|bucket span=5m _time|chart count over _time by app_risk| fields _time,Critical,High,Medium,Low,"Very Low"</query>
<earliest>-15m</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.visibility">visible</option>
<option name="charting.axisTitleY.visibility">visible</option>
<option name="charting.axisTitleY2.visibility">visible</option>
<option name="charting.axisX.scale">linear</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</option>
<option name="charting.chart">column</option>
<option name="charting.chart.bubbleMaximumSize">50</option>
<option name="charting.chart.bubbleMinimumSize">10</option>
<option name="charting.chart.bubbleSizeBy">area</option>
<option name="charting.chart.nullValueMode">gaps</option>
<option name="charting.chart.showDataLabels">none</option>
<option name="charting.chart.sliceCollapsingThreshold">0.01</option>
<option name="charting.chart.stackMode">stacked</option>
<option name="charting.chart.style">minimal</option>
<option name="charting.drilldown">none</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
<option name="charting.legend.placement">right</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
</chart>
</panel>
</row>
</dashboard>
And attached the dashboard I see
When you do an xyseries, the sorting could be done on first column which is _time in this case. risk_order or app_risk will be considered as column names and the count under them as values. For e.g.
xyseries _time,risk_order,count
will display as
_time 1 2 3 4 5
So if you need to sort by the column names, then you could mention them in fields
. For e.g. if you have defined number of app_risk
, then try
my_query | eval _time = time| bucket _time span=1d | stats count by _time, app_risk
| xyseries _time,app_risk,count | fields _time,Critical,High,Medium,Low,"Very Low",Unknown
@renjith.nair
Thanks for your response.
What I am trying to do is to show all the data in stacked column but the order of the data should be by their risk - top will be critical and bottom will be unknown.
I tried to do what you have suggested but it does not work.
@shayhibah ,okie got it, what about
my_query | eval _time = time| bucket _time span=1d
|chart count over _time by app_risk| fields _time,Critical,High,Medium,Low,"Very Low",Unknown
Also make sure your time format of time
is in epoch and if not convert it using strftime while assigning to _time
didnt help.. the xyseries command change the order of the columns
@shayhibah , you don't need to use xyseries. The above search should serve your requirement.
@renjith.nair
When I run the query on search bar it looks good.
When I create a panel - the order is changed.
Do you know why? Maybe its because I used custom colors for the app_risk values?
The current query is:
my_query | eval _time = time| bucket _time span=1d
| chart count over _time by app_risk| fields _time,Critical,High,Medium,Low,"Very Low",Unknown
@shayhibah , updated the answer as there are no option for adding images in comments
unfortunately it still does not work.
I copied your query into the search bar - it looks fine.
When I change the query in my dashboard source code - the visualization is different.
Here is my code behind:
<chart>
<search>
<query>index=_* earliest=-15m|eval app_risk=case(sourcetype="splunkd","Very Low",sourcetype="audittrail","Medium",sourcetype="kvstore","Low",sourcetype="splunkd_access","Critical",sourcetype="splunk_web_access","High")|search app_risk=*
|bucket span=5m _time|chart count over _time by app_risk| fields _time,Critical,High,Medium,Low,"Very Low"
<earliest>$general_overview_time_picker.earliest$</earliest>
<latest>$general_overview_time_picker.latest$</latest>
</search>
<option name="charting.axisTitleX.visibility">collapsed</option>
<option name="charting.axisTitleY.text">Logs</option>
<option name="charting.axisY.abbreviation">auto</option>
<option name="charting.chart.stackMode">stacked</option>
<option name="charting.legend.labels">[Unknown,"Very Low",Low,Medium,High,Critical]</option>
<option name="charting.legend.placement">bottom</option>
<option name="charting.seriesColors">[#A6A6A6,#6FA0F9,#89C73A,#FFE614,#FF8B1A,#E55D5D]</option>
<option name="refresh.display">progressbar</option>
<option name="trellis.enabled">0</option>
<option name="trellis.size">small</option>
</chart>
@shayhibah ,
It's interesting 🙂 . can you just copy the full xml to your system and try. In b/w which version of splunk>?