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
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
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
Run your hourly search hourly to populate an hourly SI, then run a daily search on the hourly SI to populate a daily SI.
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