Splunk Enterprise

Using lookup table data and event data to create a chart

Jurala
Explorer

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 
Tags (1)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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  

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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  

View solution in original post

niketnilay
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@niketnilay By the way, great picture!

0 Karma

niketnilay
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

DalJeanis
SplunkTrust
SplunkTrust

@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

niketnilay
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

rvany
Communicator

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

0 Karma

niketnilay
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!