Splunk Search

When clicking on table header want to sort by case insensitive

simpkins1958
Contributor

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.

0 Karma
1 Solution

elliotproebstel
Champion

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

View solution in original post

0 Karma

elliotproebstel
Champion

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

0 Karma

Muthu
Observer

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

0 Karma

simpkins1958
Contributor

Thanks!! This works well.

0 Karma

elliotproebstel
Champion

Glad I could help!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.

simpkins1958
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...