How do you normalize fields to Join two searches?

[tegnatomm]

I have ran across an issue that I've been banging my head against and it will not give.

I have a search that is trying to join to another search — easy enough. However, I seem to have some funky character at the end of some of my data that is messing up the join. For example, I have a search that produces this as the output:

name             |                  value
vwilliams1       |               10000

I have a second search that LOOKS to be returning this output:

name                 |               here
vwilliams1          |              true

When I do a join on the name field it cannot join the two records. I have tried trim, rex sed commands, everything I can think of to get this to work. I have ran the len command and compared filed lengths, which show they are the same. I have even used substr(name,1,10) for both searches and that does not work. If I use substr(name,1,9) for both searches it will join, so it has to be something with that last character.

The only way I have been able to get the join to work is on the second search do this

|eval name= substr(name,1,9)
|strcat name "1" name

This obviously will not work for any other fields that don't have exactly 10 characters and end in a 1 so I cannot really use this solution. So I am back to trying to normalize the data in the second search to match the first search.

I have never came across a situation like this before and have used every trick I can think of to try and normalize this data. Are there any suggestions that people can give me on how to get this to work?

Thanks to all!

[gcusello]

Hi tegnatomm,
to cut the spaces you can use the trim function but probably this isn't the problem.
Anyway, could you share your search? maybe there's an error in field naming.
To test this situation you can try to use the second search as a subsearch in the first and see if you find results.
One thing to check: how many results do you have from the second search? remember that subsearches have the limit of 50,000 results.

Bye.
Giuseppe

[damiensurat]

Hi tegnatomm, Although the case does look the same across both searches, have you tried eval name = lower(name) for both of the search and subsearch and then join on name? eg: yourSearch | eval name=lower(name) | join type=left name [ yourSubsearch | eval name=lower(name) ]

[tegnatomm]

Yes. I have normalized the case by doing eval name=lower(name) on both searches. Still did not join.