Splunk Search

Can you help me with my xyseries custom sorting query?

shayhibah
Path Finder

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!

Tags (1)
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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

alt text

Happy Splunking!
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma

shayhibah
Path Finder

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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

Happy Splunking!
0 Karma

shayhibah
Path Finder

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma

shayhibah
Path Finder

@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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma

shayhibah
Path Finder

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>
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@shayhibah ,

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

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...