Why is the where clause case sensitive against field values when part of an inputlookup search?

[dstaulcu]

It appears that the where clause is sensitive to the case of field values when invoked as part of an inputlookup command. For example, in the following search, when the actual host field value is "hostname", the search will return 0 results.

 | inputlookup <lookup_name> WHERE host="HostName"

This case sensitive behavior is inconsistent with the case insensitive behavior of | search or | where commands against field values. With use of these commands/strategies, the following searches will return the expected number of results.

| inputlookup <lookup_name> | where host="HostName"

| inputlookup <lookup_name> | search host="HostName"

I'd like to be able to use the where clause for efficiency reasons. Can anyone think of a good reason the where clause that is part of inputlookup is case sensitive against field values? And if not, can the behavior be modified by an admin or is the behavior hard-coded? Seems like a bug to me. Curious what the community thinks/observes in their own environment.

Thanks

[renjith_nair · Accepted solution]

Hi @dstaulcu ,

You can change the case sensitive option in inputlookup in http://docs.splunk.com/Documentation/Splunk/6.4.1/admin/transformsconf#Lookup_tables

case_sensitive_match = <bool>
* NOTE: This attribute is not valid for KV Store-based lookups.
* If set to false, case insensitive matching will be performed for all
  fields in a lookup table
* Defaults to true (case sensitive matching)

Some users do not want their searches to match values of different a case. Also, you have an option to convert the cases in Splunk and then match using upper and lower

[dstaulcu]

Thanks! Glad to know that csv-based lookup where clause sensitivity is within my control. It would be nice to add that same level of support to kv-based lookups to enable a consistent experience across lookup table storage strategies and to simplify lookup table type migrations.

This situation was odd to me because it seemed like behavior had changed over time. It turns out that someone had changed a large and apparently case insensitive lookup table file I had been using for a long time from CSV to KV store file type.... which introduced data quality issues with some of my reports/dashboards. With this knowledge of changes in behavior I can make adjustments.