Splunk Search

How do I calculate USD based on Lookup table?

JYTTEJ
Communicator

All our volume are recorded in multiple local currencies

I need to create a report which show our volume in USD.

I have created a look up table which contain the value: CUR EXCH

I have created following search:

index="summary" REPORT=PSVOL Type=Charge|LOOKUP PRJ_table PRJ OUTPUT PRG|lookup exch_rate CUR OUTPUT EXCH |eval Month= if(date_month="may", 5, if(date_month="june",6,if(date_month="january", 1, if(date_month="february",2, if(date_month="march",3, if(date_month="april",4, if(date_month="july",7, if(date_month="august",8,if(date_month="september",9,if(date_month="october",10, if(date_month="november",11, if(date_month="december",12,0))))))))))))|eval USDAMT=AMT*EXCH|  STATS SUM(AMT) SUM(USDAMT)  BY Month Type PRG CUR EXCH

This give following result:

Month   Type    PRG     CUR     EXCH    SUM(AMT)
1   Charge  XXX USD 1   555.6
1   Charge  YYY BRL 1.6022  666.6
1   Charge  ZZZ CAD 0.97675 777.7
1   Charge  VVV EUR 0.700648    888.8
1   Charge  TTT GBP 0.607312    999.9
2   Charge  XXX USD 1   111.1
2   Charge  YYY BRL 1.6022  222.1

Question: How do I calculate the value in USD?

Tags (1)
1 Solution

JYTTEJ
Communicator

Managed to solve the problem myself!:

index="summary" REPORT=PSVOL Type=Charge|LOOKUP PRJ_table PRJ OUTPUT PRG|lookup exch_rate CUR OUTPUT EXCH |eval Month= if(date_month="may", 5, if(date_month="june",6,if(date_month="january", 1, if(date_month="february",2, if(date_month="march",3, if(date_month="april",4, if(date_month="july",7, if(date_month="august",8,if(date_month="september",9,if(date_month="october",10, if(date_month="november",11, if(date_month="december",12,0))))))))))))|stats sum(AMT) BY PRG CUR EXCH Month|rename sum(AMT) AS TOTAMT |EVAL USD=TOTAMT/EXCH | CHART SUM(USD) by Month PRG

View solution in original post

JYTTEJ
Communicator

Managed to solve the problem myself!:

index="summary" REPORT=PSVOL Type=Charge|LOOKUP PRJ_table PRJ OUTPUT PRG|lookup exch_rate CUR OUTPUT EXCH |eval Month= if(date_month="may", 5, if(date_month="june",6,if(date_month="january", 1, if(date_month="february",2, if(date_month="march",3, if(date_month="april",4, if(date_month="july",7, if(date_month="august",8,if(date_month="september",9,if(date_month="october",10, if(date_month="november",11, if(date_month="december",12,0))))))))))))|stats sum(AMT) BY PRG CUR EXCH Month|rename sum(AMT) AS TOTAMT |EVAL USD=TOTAMT/EXCH | CHART SUM(USD) by Month PRG

JYTTEJ
Communicator

Thanks! The strftime is really great!

0 Karma

mw
Splunk Employee
Splunk Employee

If you're not already, you'll probably want to utilize a temporal lookup for this as well. That way, you can get the exchange rate at the time of the event, rather than always getting the current exchange rate.

dwaddle
SplunkTrust
SplunkTrust

A little unrelated, but you might find this an easier to work with way of getting the numerical month number:

eval month_number=strftime(_time,"%m")

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...