How to put a function on the lookup field from a lookup table?

[xiangtaner]

Hi,

My event results have a field "name" and it has lower case values (e.g. 'mike_lee'). But in my lookup table, the name is mixed of uppercase and lowercase (e.g. 'Mike_Lee'). So when I use lookup, can I apply a upper or lower function on the "name" field in the lookup table? I tried the following but it does not work. Could any experts please help how to solve this?

...| lookup mylookup.csv lower(name) as name | OUTPUT city

Thanks and Regards,

Wayne

[somesoni2 · Accepted solution]

You should contact your Splunk admin to get the change suggested by @MuS. OR if you've permission, you can update the lookup table file itself with all lowercase values for this field, using a query like this

| inputlookup mylookup.csv | eval name=lower(name) | outputlookup mylookup.csv 

Besides that, you can use a more expensive method using 'join' to achieve the same

your base search | join name type=inner [ | inputlookup mylookup.csv | eval name=lower(name) | table name city]

[xiangtaner]

Thanks, it seems that currently this is the feasible solution. Hope Splunk will allow adding functions to lookup fields in the lookup command in the future.

[MuS]

Hi xiangtaner,

you can set in transforms.conf http://docs.splunk.com/Documentation/Splunk/6.2.1/Admin/Transformsconf the case_sensitive_match option to false (it's true by default)

If set to false, case insensitive matching will be performed for all fields in a lookup table

or use an eval before the lookup :

your base search here | eval field=lower(field) | lookup .....

Hope this helps ...

cheers, MuS

[xiangtaner]

Hi MuS,

Thanks for your answers.

I am not Splunk admin, so I can't set transforms.conf.

For your second proposed solution, it seems that you are applying an eval on the field 'name' from the base research results but actually it is already in lower cases. The real problem is that the value of the 'name' field in the lookup table which is a mix of upper cases and lower cases. And it is not allowed to put a function on the field 'name' in the lookup table, i.e. base search | lookup mylookup lower(name) as name OUTPUT city will result in error message "Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table".

Please further advise.

Thanks,

Wayne