Splunk Search

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

xshen_anji
New Member

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.

Tags (1)
0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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
0 Karma

xshen_anji
New Member

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.

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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

0 Karma

xshen_anji
New Member

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.

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

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]
0 Karma

xshen_anji
New Member

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!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...