Splunk Search

Sorting column header is based on ASCII and its not based on case senstitve

angelinealex
Communicator

Hi,

I have a saved search with the below code snippet to sort irrespective of case.

index=indexname 
| eval sortName = lower(Name)
| sort sortName 
| table Name

But when I click on 'Name' column header, its sorting based on ASCII.
How to make it sort irrespective of case? Is it really possible? Please help.

Thank you.

0 Karma

niketnilay
Legend

@angelinealex, please try out the following Run any where dashboard with Simple XML JS extension. This actually builds up on @somesoni2 's answer. Prefix Sno to Name after sorting using lower case values. Then use Splunk JS Stack to apply custom Cell Render. In the JavaScript code, I am stripping off Serial Number before displaying in the table using regular expression.

First Table is mocks the issue where sorting is Case sensitive.

Second Table is the solution by Somesh Soni to prefix Serial Number before Values to always have them sorted. PS: prinf() function has been used to pad extra zero to keep digits sorted i.e. instead of 1, 10,11,... 2,3,4... have 01,02,03,04...,10,11. Padding can be increased as per total count or rows.

Third Table is one with Custom Cell Renderer using <table> id tableWithAlphabeticalSort, which strips the prefixed serial number using Simple XML JS extension and Splunk JS Stack.

alt text

Following is the Simple XML code for run anywhere dashboard:

<dashboard script="table_with_alphabet_sort.js">
  <label>Table Alphabetically Case Insensitive sort</label>
  <row>
    <panel>
      <table id="tableWithCaseSensitiveSorting">
        <title>Table without Case Sensitive Sorting</title>
        <search>
          <query>| makeresults 
| eval Name="apple,Aeroplane,Bat,bolt" 
| makemv Name delim="," 
| mvexpand Name 
| eval sortName = lower(Name) 
| sort sortName
| table Name sortName</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
    <panel>
      <table id="tableWithoutCustomCellSorting">
        <title>Table without JS for Case Insensitive Sort</title>
        <search>
          <query>| makeresults 
| eval Name="apple,Aeroplane,Bat,bolt" 
| makemv Name delim="," 
| mvexpand Name 
| eval sortName = lower(Name) 
| sort sortName 
| streamstats count as sno
| eval sno=printf("%02d",sno)
| eval Name=sno.".".Name 
| table Name sortName</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
    <panel>
      <table id="tableWithAlphabeticalSort">
        <title>Table with Case Insensitive Sorting</title>
        <search>
          <query>| makeresults 
| eval Name="apple,Aeroplane,Bat,bolt" 
| makemv Name delim="," 
| mvexpand Name 
| eval sortName = lower(Name) 
| sort sortName 
| streamstats count as sno
| eval sno=printf("%02d",sno)
| eval Name=sno.".".Name 
| table Name sortName</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</dashboard>

Following is the code for JavaScript file table_with_alphabet_sort.js to be placed under your app's appserver/static folder:

require([
    'underscore',
    'jquery',
    'splunkjs/mvc',
    'splunkjs/mvc/tableview',
    'splunkjs/mvc/simplexml/ready!'
], function(_, $, mvc, TableView) {

    var CustomRangeRenderer = TableView.BaseCellRenderer.extend({
        canRender: function(cell) {
            // Enable this custom cell renderer for Name field
            return _(['Name']).contains(cell.field);
        },
        render: function($td, cell) {
            // Extract Numeric Part for Sorting and Hide Using CSS
            // Keep Name as Text strName
            var value = cell.value;
            var snoPrefix = value.match(/\d+\./);
            var strName = value.match(/[^\d\.]+/);

            // Create HTML with Numeric sno hidden and Name displayed
            $td.text(strName).addClass('string');
        }
    });

    mvc.Components.get("tableWithAlphabeticalSort").getVisualization(function(tableView) {
        tableView.on('rendered', function() {
            setTimeout(function(){
                // Add custom cell renderer, the table will re-render automatically.
                tableView.addCellRenderer(new CustomRangeRenderer());
            },100);
        });
    });
});

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

angelinealex
Communicator

@niketnilay, Thank you for the detailed example. Currently am stuck up with some other priority work. I will try and update you. Thanks.

0 Karma

niketnilay
Legend

@angelinealex would it be of any use to you if Sorting option was removed from the Name Table column? Or else can you use lower() or upper() case values in the Name field itself?

If not like it was suggested by @xpac, Simple XML JavaScript extension may be your option to apply sorting as per your need.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

angelinealex
Communicator

Requirement is to sort based on Name column only and I dont want loose the case value for Name field. So I cant use lower() or upper() to the Name field.

Can you add some sample code to achieve this using XML Javascript extension?

0 Karma

somesoni2
Revered Legend

How about you add a columns sno which contains the serial number of rows after sorting based on lower(Name) value? This will allow your users to sort Name columns ascending/descending order (by clicking on sno column), like this

 index=indexname 
 | eval sortName = lower(Name)
 | sort sortName | streamstats count as sno
 | table sno Name

angelinealex
Communicator

I cant add new column as that is not part of requirement. Using this saved search to show as report to the customer.

0 Karma

somesoni2
Revered Legend

Splunk sort on string columns are based on ASCII value. You create a new field sortName with all lowercase and to an explicit sort on that field, but the value of field Name is unchanged. Hence when you click on Name columns, it still gets sorted based on it's value.

0 Karma

xpac
SplunkTrust
SplunkTrust

I don' think this is possible without writing a custom Javascript and implementing that into your dashboard...

0 Karma

angelinealex
Communicator

Can you add some sample code to achieve this using custom Javascript?

0 Karma