Using lookup table data and event data to create a chart

[Jurala]

I have a lookup table that has following headers Area, Office (area code and office number). There are many offices under each area and Splunk events include office number but no area code. Is there a way to create a query that could create a chart where there is area code on X axis and Y axis could have purchasePrice (purchasePrice is included on logs)? Is Splunk able to read lookup table area codes and link them to office codes that can be found from events done in application?

Query that I've tried to use:

index=* sourcetype=*| inputlookup regions.csv Area Office | stats avg(purchasePrice) as Price by Area 

[DalJeanis · Accepted solution]

The verb you are looking for is lookup, not inputlookup.

your query that gets Office number and purchasePrice
| fields Office purchasePrice 
| lookup regions.csv Office OUTPUT Area
| stats avg(purchasePrice) by Area  

[niketn]

@DalJeanis. If there is one to one mapping between Area and Office, ideally stats should be done first then lookup so that there are less events to correlate.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Lookup#Optimizing_your_lookup_se...

Also, I had inputlookup in the base search to find only events with Office same as those mentioned in lookup files (to filter results upfront).

[DalJeanis]

@niketnilay By the way, great picture!

[niketn]

Thanks. Surprisingly I am so used to of seeing an icon against my name, that I am not able to recognize my own pic 🙂

[DalJeanis]

@niketnilay - Good point. In that case, we would need to retain the components in order to get a valid average at the Area level.

 your query that gets Office number and purchasePrice
 | fields Office purchasePrice 
 | stats count as mycount sum(purchasePrice) as mysum by Office  
 | lookup regions.csv Office OUTPUT Area
 | stats sum(mycount) as mycount sum(mysum) as mysum by Area
 | eval Price=mysum/mycount
 | table Area Price

[niketn]

I now see what I was missing. I was assuming one Area will have one Office. Which might not be the case.

[rvany]

What is the problem with your search? And is it semantically correct to link area codes to offices codes?

[niketn]

What is the field name for Office in your Splunk events. Following query assumes field name for Office is the same in your Splunk Event and Lookup File. Also, assumes you have created a Lookup Definition for region.csv as region, you can try the following command

index=* sourcetype=* [ inputlookup regions.csv | table Office]
| stats avg(purchasePrice) as Price by Office
| lookup regions Office output Area 

In case your Splunk events has Office number by a different field name (for example office_number), you can try following instead:

index=* sourcetype=* [ inputlookup regions.csv | rename Office as office_number | table office_number ]
| stats avg(purchasePrice) as Price by office_number
| lookup regions Office as office_number output Area 

Refer to Splunk lookup command documentation: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Lookup#Basic_example