Splunk Search

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

johnward4
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

woodcock
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

johnward4
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

woodcock
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

jkat54
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...