topic Re: Sorting column header is based on ASCII and its not based on case senstitve in Splunk Search

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

angelinealex — Tue, 22 May 2018 18:21:39 GMT

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.

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

xpac — Tue, 22 May 2018 19:06:37 GMT

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

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

somesoni2 — Tue, 22 May 2018 19:12:59 GMT

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.

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

niketn — Tue, 22 May 2018 19:52:42 GMT

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

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

angelinealex — Tue, 22 May 2018 20:06:25 GMT

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?

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

angelinealex — Tue, 22 May 2018 20:08:31 GMT

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

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

somesoni2 — Tue, 22 May 2018 21:11:47 GMT

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

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

angelinealex — Wed, 23 May 2018 17:52:03 GMT

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

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

niketn — Wed, 23 May 2018 18:55:53 GMT

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

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!

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

angelinealex — Tue, 29 May 2018 16:06:08 GMT

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