Splunk Search
Highlighted

How do you use lookups to update a quantity (int value)?

Communicator

I'm trying to use lookups to first populate on a daily basis for my stores inventory by itemid then I run a separate dbconnect input on an hourly basis that tracks sales or returns -1/1 to a field named qty. I would like to be able to update the lookup tables itemid qty by stores, but I haven't used lookup a ton so tips would be very helpful.

My daily query to populate the stores inventory. There are slight differences in the field name that are in the daily dbconnect input : (unitcount, item_id)

index=example sourcetype="daily:storeinv" 
| eval unitcount=round(unitcount,0)
| stats count by item_id, unitcount, store_no
| table store_no, item_id, unitcount
| sort - store_no
| outputlookup storeinventory.csv

My hourly dbconnect input has field names like : (scanitemid, qty). Below was my attempt to calculate the hourly updated count shown as "qty" against the unitcount from my daily inventory lookup, but I'm not showing any results in field "updated_unitcount"

| inputlookup storeinventory.csv
[search index=example sourcetype="hourly:storeinv" scan_item_id=*
    | eval item_id = scan_item_id
    ]
| stats sum(eval(unitcount+qty)) as updated_unitcount by item_id, store_no
| table store_no, item_id, updated_unitcount
0 Karma
Highlighted

Re: How do you use lookups to update a quantity (int value)?

SplunkTrust
SplunkTrust

Try this:

index=example sourcetype="hourly:storeinv" scan_item_id=*
| lookup storeinventory.csv store_no as store_no scan_item_id as item_id
| stats sum(eval(unitcount+qty)) as updated_unitcount by item_id, store_no 
| table store_no, item_id, updated_unitcount

If that works all you need then is to remove the unused fields and output lookup, minus the table and rename the evaluated sum.

index=example sourcetype="hourly:storeinv" scan_item_id=*
| lookup storeinventory.csv store_no as store_no scan_item_id as item_id
| stats sum(eval(unitcount+qty)) as updated_unitcount by item_id, store_no 
| fields - scan_item_id - qty
| rename updated_unitcount as qty
| outputlookup storeinventory
0 Karma
Highlighted

Re: How do you use lookups to update a quantity (int value)?

Esteemed Legend

I would not do it like this at all. Instead, I would use a Summary Index:
https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Usesummaryindexing

0 Karma
Highlighted

Re: How do you use lookups to update a quantity (int value)?

Communicator

Hi @woodcock I'm familiar with summary indexes but can you please elaborate on your idea for this use case?

The data I'm working with is pulled from a database, the daily store inventory has slightly different field names for the data that's being pulled, also from a database, for my hourly transactions that I'm trying to use to calculate. for example :

The daily inventory uses itemid, unitcount
The hourly transactions uses scan
item_id, qty but qty shows as -1 if there was a sale or 1 for return

0 Karma
Highlighted

Re: How do you use lookups to update a quantity (int value)?

Esteemed Legend

Run your hourly search hourly to populate an hourly SI, then run a daily search on the hourly SI to populate a daily SI.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.