Splunk Search

Use multiple inputs to a lookup table to return a xref value

markwymer
Path Finder

Hi all,

I have a lookup table of Currency exchange rates per day per currency code e.g. (cutdown!)

Date,USD,JPY,GBP
2017-05-12,1.0876,123.82,0.84588
2017-05-11,1.086,123.69,0.84485
2017-05-10,1.0882,123.84,0.83985

My logged events contain fields for the Currency Codes (could be one of many currency codes but only one Currency Code per event) and a value for the transaction for example:-

CurrCode=JPY
value=200.00

I have created a new field from the timestamp to create a lookup date using:-

convert timeformat="%Y-%m-%d" ctime(_time) AS LU_Date

So, my query is - how can I use the CurrCode value and the LU_Date value to return the exchange rate? My ultimate goal is to then multiply the value in the event by the exchange rate to get a 'base' value across all currencies.

Thanks for any help.
Mark.

Tags (2)
0 Karma

dineshraj9
Builder

Modify the lookup content this way -

Date,CurrCode,Value
2017-05-12,USD,1.0876
2017-05-12,JPY,123.82
2017-05-12,GBP,0.84588
2017-05-11,USD,1.086
2017-05-11,JPY,123.69
2017-05-11,GBP,0.84485
2017-05-10,USD,1.0882
2017-05-10,JPY,123.84
2017-05-10,GBP,0.83985

Then you can get value from the lookup this way -

<your search> | lookup Date as LU_Date,CurrCode as CurrCode OUTPUT Value
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...