Splunk Search

When clicking on table header want to sort by case insensitive

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

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

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

Contributor

Thanks!! This works well.

0 Karma

Glad I could help!

0 Karma

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, an upvote would be appreciated.

Contributor

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!