topic Re: Can you help me with my xyseries custom sorting query? in Splunk Search

Can you help me with my xyseries custom sorting query?

shayhibah — Thu, 13 Sep 2018 05:49:20 GMT

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!

Re: Can you help me with my xyseries custom sorting query?

renjith_nair — Thu, 13 Sep 2018 09:59:46 GMT

@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

Re: Can you help me with my xyseries custom sorting query?

renjith_nair — Tue, 29 Sep 2020 21:14:58 GMT

@shayhibah ,

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

Re: Can you help me with my xyseries custom sorting query?

shayhibah — Thu, 13 Sep 2018 09:59:48 GMT

@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.

Re: Can you help me with my xyseries custom sorting query?

renjith_nair — Thu, 13 Sep 2018 09:59:49 GMT

@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

Re: Can you help me with my xyseries custom sorting query?

shayhibah — Thu, 13 Sep 2018 09:59:50 GMT

didnt help.. the xyseries command change the order of the columns

Re: Can you help me with my xyseries custom sorting query?

renjith_nair — Thu, 13 Sep 2018 09:59:51 GMT

@shayhibah , you don't need to use xyseries. The above search should serve your requirement.

Re: Can you help me with my xyseries custom sorting query?

shayhibah — Tue, 29 Sep 2020 21:14:16 GMT

@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

Re: Can you help me with my xyseries custom sorting query?

renjith_nair — Thu, 13 Sep 2018 10:02:06 GMT

@shayhibah , updated the answer as there are no option for adding images in comments

Re: Can you help me with my xyseries custom sorting query?

shayhibah — Thu, 13 Sep 2018 11:59:03 GMT

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>

Re: Can you help me with my xyseries custom sorting query?

renjith_nair — Thu, 13 Sep 2018 12:54:31 GMT

@shayhibah ,

It's interesting 🙂 . can you just copy the full xml to your system and try. In b/w which version of splunk>?