I have a CSV file uploaded as a lookup. I am using the userID from my search with the lookup, but for some reason, the lookup is not enriching all of the search data. It will work for some search results and not others. I have checked the UserID's of those that are not being found and those that are and they are all part of the CSV. Has anyone had a similar problem and know how to fix?
The CSV is comprised of the following fields:
UserID,Name,LastName,FirstName,City,Address,PostalCode,JobTitle,Center,Department
Hi, I found a solution thanks to Iguinn. The lookup is case sensitive, so I changed my lookup csv data to lowercase and added the following command to my search which set any uppercase UserID events to lowercase too. This allowed the lookup to return data for all events.
|eval UserID=lower(UserID)
Hope this helps.
You can also do this for your lookup, to make the match case INsensitive. But you can't do it from the user interface, you have to edit the configuration file directly. Add this to the stanza in transforms.conf
case_sensitive_match=false
Then you don't need to make the keys lower-case, etc. It does add a small amount of overhead to your search (but then so does the eval
command). You can also match your lookup CIDR-aware, etc. when it tries to match. Take a look at the documentation here.
Thanks again Iguinn
Hi, I found a solution thanks to Iguinn. The lookup is case sensitive, so I changed my lookup csv data to lowercase and added the following command to my search which set any uppercase UserID events to lowercase too. This allowed the lookup to return data for all events.
|eval UserID=lower(UserID)
Hope this helps.
Lookups are case-sensitive by default. Could this be the problem?
Thanks Iguinn, I did not know that about the lookups.
My UserID is made up of a letter and 4 numbers eg: X1234 or x1234. In my events the letter can be uppercase or lowercase and in some cases it can have both.
I changed the lookup UserID letter to lowercase and this improved the results dramatically, but there is still some of the data not being looked up.
Could this be due to the events that return a UserID with both uppercase and lowercase letters? if so do you have any suggestions?
Since you already updated the lookup to use lowercase UserID, just update the user id field in the events to be in lowercase before the lookup. Kinda like this
your base search | eval UserID=lower(UserID) | lookup yourlookup.csv UserID ....