topic Re: Why are the kvstore lookups uncharacteristically case-sensitive in field values? in Splunk Search

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

dstaulcu — Tue, 08 May 2018 10:59:02 GMT

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?

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

solarboyz1 — Tue, 29 Sep 2020 19:33:36 GMT

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.

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

solarboyz1 — Tue, 29 Sep 2020 19:33:38 GMT

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

[ Lookup_name ]
external_type = kvstore
collection = kvstore_name
case_sensitive_match =

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

cpetterborg — Tue, 29 Sep 2020 19:34:25 GMT

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.

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

woodcock — Thu, 17 May 2018 19:34:56 GMT

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.

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

dominikatvastli — Mon, 08 Feb 2021 15:55:42 GMT

@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.

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

scelikok — Mon, 08 Feb 2021 18:03:02 GMT

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"

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

dominikatvastli — Mon, 08 Feb 2021 18:39:02 GMT

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

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

woodcock — Mon, 08 Feb 2021 21:03:23 GMT

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).

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

scelikok — Mon, 08 Feb 2021 21:50:55 GMT

@dominikatvastli,

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

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

dominikatvastli — Tue, 09 Feb 2021 07:05:31 GMT

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.

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

bowesmana — Tue, 09 Feb 2021 22:03:54 GMT

@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...

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

bowesmana — Tue, 09 Feb 2021 22:06:41 GMT

@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.

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

tomasmoser — Fri, 09 Jul 2021 13:07:50 GMT

The most hillarious answer I have seen recently 😄

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

_joe — Wed, 08 Jun 2022 15:13:28 GMT

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