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