Splunk Search

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

dstaulcu
Builder

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?

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

dominikatvastli
Explorer

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

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
0 Karma

dominikatvastli
Explorer

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.

 

0 Karma

scelikok
SplunkTrust
SplunkTrust

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" 
If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

dominikatvastli
Explorer

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

Tags (1)
0 Karma

scelikok
SplunkTrust
SplunkTrust

@dominikatvastli,

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

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

cpetterborg
SplunkTrust
SplunkTrust

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
Builder

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
Builder

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

[ Lookup_name ]
external_type = kvstore
collection = kvstore_name
case_sensitive_match =

bowesmana
SplunkTrust
SplunkTrust

@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
Contributor
The most hillarious answer I have seen recently 😄
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...