When clicking on table header want to sort by case insensitive

[simpkins1958]

We have table with a list of users. Some user names are all lower case, some all upper case, some mixed case. We can do the initial sort fine using a macro:

[CaseInsensitiveSort(1)]
args = fieldname
definition = eval lowerFieldValue=lower('$fieldname$') \
| sort lowerFieldValue \
| fields - lowerFieldValue

But when a user clicks on the "User Name" header in the table the sort is no longer case insensitive.

[elliotproebstel · Accepted solution]

Assuming this is going into a table on a dashboard, yes. You should be able to achieve this by pairing fieldformat with a hidden field in your table. Right now, you're creating the field lowerFieldValue, sorting by it, and then removing it from the dataset. Instead, create that field and apply a fieldformat to it where you assign the value of the original field to it. In the table display on the dashboard, you'll need to specify the fields you want to display explicitly, as opposed to removing fields within your SPL.

So let's assume your base search returns events containing a field called username:
base search | eval User=lower(username) | fieldformat User=username

And then you'll edit the XML source directly and after the closing </search> tag line, add this:
<fields>User, somefield, anotherfield<fields>

By explicitly whitelisting the fields you want to display, you retain the hidden field username in the dataset, which allows you to use it as the display format.

Here's some info about fieldformat:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Fieldformat
And here is some useful info about the spec of a simpleXML dashboard, specifically about the options you can put within a table:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Viz/PanelreferenceforSimplifiedXML#table

[Muthu]

This works fine, but <fields></fields> doesn't understand wildcards as fields command in SPL query. Any solution to this ? @elliotproebstel 

[simpkins1958]

Thanks!! This works well.

[elliotproebstel]

Glad I could help!

[richgalloway]

Can you change the query to normalize the "User Name" field to lower case rather than use a temporary field?

[simpkins1958]

No. That is not acceptable to our product manager. He wants the case not to change in the UI.