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

[johnward4]

I'm trying to use lookups to first populate on a daily basis for my stores inventory by item_id 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 item_id 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 : (scan_item_id, 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

[woodcock]

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

[johnward4]

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 item_id, unitcount
The hourly transactions uses scan_item_id, qty but qty shows as -1 if there was a sale or 1 for return

[woodcock]

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

[jkat54]

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