topic Re: Using lookup table data and event data to create a chart in Splunk Enterprise

Using lookup table data and event data to create a chart

Jurala — Thu, 17 Aug 2017 11:46:26 GMT

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

Re: Using lookup table data and event data to create a chart

niketn — Thu, 17 Aug 2017 12:55:27 GMT

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

Re: Using lookup table data and event data to create a chart

rvany — Thu, 17 Aug 2017 13:13:26 GMT

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

Re: Using lookup table data and event data to create a chart

DalJeanis — Thu, 17 Aug 2017 14:55:45 GMT

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

Re: Using lookup table data and event data to create a chart

niketn — Thu, 17 Aug 2017 15:46:20 GMT

@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_search

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).

Re: Using lookup table data and event data to create a chart

DalJeanis — Thu, 17 Aug 2017 16:28:31 GMT

@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

Re: Using lookup table data and event data to create a chart

DalJeanis — Thu, 17 Aug 2017 16:28:40 GMT

@niketnilay By the way, great picture!

Re: Using lookup table data and event data to create a chart

niketn — Fri, 18 Aug 2017 09:51:50 GMT

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

Re: Using lookup table data and event data to create a chart

niketn — Fri, 18 Aug 2017 09:54:47 GMT

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