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  

niketn
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

niketn
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

niketn
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

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