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