Why are the kvstore lookups uncharacteristically case-sensitive in field values?

[dstaulcu]

Has anyone figured a way to make kv-store lookups NOT case sensitive on field values? If so, how?

We're about to migrate users to a new search head cluster where all large (> 20 MB) public lookups have been converted from CSV to KV. We plan to communicate the KV store change and it's potential impacts to search results, but it seems strange that Splunk has allowed this KV store's deviation in behavior to persist for so long. Splunk guru's -- What are the challenges to normalizing this behavior by default?

[woodcock]

You can create a Lookup definition ( Settings -> Lookups -> Lookup definition -> New ) that points to your KVStore-based lookup and then uncheck the Case sensitive setting. Then use the Lookup definition instead.

[dominikatvastli]

@woodcockThere is no Case sensitive setting in Splunk Web.

Splunk's documentation says, that it can be done via transforms.conf if field name and value are lower case. I have the following configured:

collections.conf

[collection_test]
field.test = string
replicate = true

transforms.conf

[lookup_test]
external_type = kvstore
collection = collection_test
case_sensitive_match = false
fields_list = test

I filled it with some lowercase data:

| makeresults
| eval test = "test_value"
| fields - _time
| outputlookup lookup_test

The search | inputlookup lookup_test WHERE test="TEST_VALUE" should be successful but isn't.

[woodcock]

I never said there was a case-sensitive setting "for Splunk Web" (I don't even know what that might mean).  I was EXTREMELY clear about where the setting is and it does work exactly as I explained.  Lookups should be done through Lookup Definitions, not through files or collections, unless there is a very good reason not to (I cannot think of one).

[bowesmana]

@woodcock 

Unfortunately case sensitivity is not one of the advanced options in the UI for KV store lookup definitions. 

We had a similar issue with KV store and ended up using @cpetterborg solution of ensuring the key data in the store was lower cased at storage and during lookup time.

As the inputlookup 'WHERE' clause is not a full eval statement where, it ended up being easier all round to normalise the data to lower case at all points, but does require changing all lookup statements to lower() the data before lookup, which would probably not solve the OP's question as I expect they have existing logic that would need to change.

Not sure what @dstaulcu did to solve his problem, but I had originally meant to add a Splunk idea around this when we had the problem ourselves, so I added it now 🙂

https://ideas.splunk.com/ideas/EID-I-818

vote for it if you think it needs a solution...

[_joe]

You are correct, it is missing when you switch to kvstore. You can add it in the transforms.conf manually and it works - but any further saving to the lookup definition through the Web UI will overwrite/remove the change.  A little frustrating. 

[lookup_definition_name]

...

case_sensitive_match = 0

[dominikatvastli]

Sorry if that was misleading.

With Splunk Web I meant that I followed your instructions by going to Settings -> Lookups -> Lookup definition -> New . When setting the type to KV Store, the setting Case sensitive match is not available. When choosing file-based or external as type, it is available.

[scelikok]

Hi,

"case_sensitive_match = false" in the transform.conf effects only lookup command. You can use upper() or lower() function for comparison in where command like below;

 inputlookup lookup_test WHERE upper(test)="TEST_VALUE" 

[dominikatvastli]

Thanks for the answer. Unfortunately, it throws the following error: Comparator '=' has an invalid term on the left hand side: CASE(test) 

[scelikok]

@dominikatvastli,

You are right, I was testing with the lookup command.

[cpetterborg]

Though not the optimal solution, you could make sure the data in the kv_store is lower case, then match by using thelower() function on the data you want to find in the kv_store. Like I say, not optimal, but it should work.

[solarboyz1]

Define a lookup for you KV store and set it to case insensitive:

[]
case_sensitive_match = false

case_sensitive_match defaults to true.
If set to false, case insensitive matching will be performed for all fields in a lookup table. Output fields and values in the KV Store used for matching must be lower case.

[solarboyz1]

To clarify, in transforms.conf create a lookup defnition for your kvstore

[ Lookup_name ]
external_type = kvstore
collection = kvstore_name
case_sensitive_match =

[bowesmana]

@solarboyz1 

From 

https://docs.splunk.com/Documentation/Splunk/latest/admin/Transformsconf

case_sensitive_match = <boolean>
* NOTE: This attribute is not valid for KV Store-based lookups.

[tomasmoser]

The most hillarious answer I have seen recently 😄