Splunk Search

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 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
0 Karma

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

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 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

0 Karma

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!