topic Re: How do you lookup matching numbers by values regardless of formats? in Splunk Search

How do you lookup matching numbers by values regardless of formats?

xshen_anji — Fri, 29 Mar 2019 13:52:32 GMT

I have a lookup table, mylookup.csv, such as:

Key, Value
3, 30
4, 45
5, 52

I have a CSV source mysource.csv, as:

sourceKey, Otherthings
3.0, 300
4.0, 400
5.0, 500

I do a search like: source=mysource.csv | lookup mylookup.csv Key as sourceKey OUPUT Value
This gives nothing. But if I change my lookup table's Key's format as 3.0, 4.0, etc, it could give results.

My question is if there is a way I can lookup and match number fields by their values, regardless of the the formats, whether there are trailing .0s. My situation is my customer would provide these lookup files with different formats(with or without .0s), I would accommodate all these situations.

Re: How do you lookup matching numbers by values regardless of formats?

harsmarvania57 — Fri, 29 Mar 2019 15:01:58 GMT

Hi,

If you have only one type of format like with or without . (dot) then you can try below query. In below query rex will remove dot and any digit after dot from sourceKey field so it will match with Key field from mylookup.csv file.

| inputlookup mysource.csv
| rex field=sourceKey mode=sed "s/\.\d+//g"
| lookup mylookup.csv Key as sourceKey OUTPUT Value as Value

Re: How do you lookup matching numbers by values regardless of formats?

xshen_anji — Fri, 29 Mar 2019 15:10:42 GMT

Thank you for answer. But my problem is these lookup files's key formats are various, so even I change the source file's format to uniform, I still can't match successfully with different formatted lookup files.

Re: How do you lookup matching numbers by values regardless of formats?

harsmarvania57 — Fri, 29 Mar 2019 15:13:41 GMT

Can you please provide all possible format type from mysource.csv if possible ?

Re: How do you lookup matching numbers by values regardless of formats?

xshen_anji — Fri, 29 Mar 2019 15:17:17 GMT

mylookup.csv's key field can be "3", "3.0", "3.00" etc. the mySource.csv's sourceKey also can be these different format. So even I uniform the source file, still can't match.

Re: How do you lookup matching numbers by values regardless of formats?

harsmarvania57 — Fri, 29 Mar 2019 15:22:32 GMT

Ah so you don't have unique format in any of the lookup file ?

Re: How do you lookup matching numbers by values regardless of formats?

harsmarvania57 — Fri, 29 Mar 2019 15:29:39 GMT

You can try below query

| inputlookup mysource.csv
| rex field=sourceKey mode=sed "s/\.\d+//g"
| join type=left sourceKey [ inputlookup append=t mylookup.csv | rex field=Key mode=sed "s/\.\d+//g" | rename Key as sourceKey]

Re: How do you lookup matching numbers by values regardless of formats?

xshen_anji — Fri, 29 Mar 2019 15:36:27 GMT

Thank you for this. I would try out later. I think it would work. But this seems complicated. I just wonder if there is no lookup or join by values for number fields. Just curious!