How to generate a baseline on a timechart using a value from a lookup table

[beetlegeuse]

I have a search that captures a specific product code, calculates the total number of units attributed to the product code that were sold over the timeframe assigned to the search (say, seven days), and presents the data as a timechart. The search also references a lookup table to provide a "human friendly" name that matches the product code, along with a baseline value which reflects a specific number of units expected to be sold each day.

The search looks like this...

sourcetype=foo index=bar item="productA" 
| lookup product_table.csv productCode AS item
| timechart span=1d sum(product_count) as volume by item
| eval baseline = max(dailyBaseline)

...and the lookup table looks like this:

productCode	name	dailyBaseline
productA	Widget	5000
productB	Thingamajig	10000

I would like the ensuing chart to show the baseline I've defined in the lookup table. If I replace "max(dailyBaseline)" in the eval statement with a static value such as 5000, it works fine. I can't figure out how to get the "dailyBaseline" value from the lookup table to work, though.

[yuanliu]

Do you mean something like this?

sourcetype=foo index=bar item="productA" 
| lookup product_table.csv productCode AS item
| timechart span=1d sum(product_count) as volume by name
| lookup product_table.csv name output dailyBaseline

[bowesmana]

Here's one way you can do it by adding this after your timechart command

| foreach * [ eval productCode="<<FIELD>>" | lookup baseline.csv productCode OUTPUT name dailyBaseline | eval {name}=<<FIELD>>, base_{name}=dailyBaseline | fields - productCode name dailyBaseline "<<FIELD>>" ]

after a split by clause the column name is the productCode, so the foreach * will iterate through the columns and create a field called dailyBaseline_productX for each found value.

There will be other ways

Edit: Added friendly name mapping

[bowesmana · Accepted solution]

The above assumes that your lookup file contents show is not the one where you are looking up against product_table.csv but if it's the same table, what are you trying to get from your lookup in the current command?

If you only have one lookup then what you want is

sourcetype=foo index=bar item="productA" 
| lookup product_table.csv productCode AS item
| timechart span=1d max(dailyBaseline) as baseline sum(product_count) as volume by name

[ITWhisperer]

Given that your search is restricting your items to just one product (code), you could do the lookup after the timechart

sourcetype=foo index=bar item="productA" 
| timechart span=1d sum(product_count) as volume by item
| eval item="productA"
| lookup product_table.csv productCode AS item output dailyBaseline
| fields - item