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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...