Splunk multiply many fields in one search and compare against another field

[Tylerdygert]

Hello,

I have data that comes in via JSON format that looks like this:

name: Item1
pricePerOne:10

name: Item2
pricePerOne: 12

name: Item3
pricePerOne: 120

I need to run a search that takes an amount of Item1 and an amount of Item2 and compares that total to Item3.

For example, in order to make 1 of Item3 I need 5 of Item1 and 3 of Item2. I want to compare the total pricePerOne of the multiple items to the pricePerOne of just 1 Item3.

Since I cant just do Item1*5 and Item2*3 since they arent numbers I have to somehow tell splunk to find the pricePerOne for each item and multiply those separately and then take a total.

How would I accomplish this in a search? I have tried using multiple where/eval clauses and using subsearch commands but that doesnt seem to work.

[woodcock]

Show us sample events along with a mockup of the desired output. Does each event contain all 3 items?

[Tylerdygert]

Here are 3 sample events:
Note: these events are being pulled from a game database. This is test data being used to work with the MLTK for a project.
chooseKey: 0
count: 0
grade: 0
keyType: 0
mainCategory: 25
mainKey: 9065
name: Milk
pricePerOne: 10600
subCategory: 8
subKey: 0
totalTradeCount: 67879068

chooseKey: 0
count: 14411
grade: 0
keyType: 0
mainCategory: 25
mainKey: 7921
name: Chicken Meat
pricePerOne: 960
subCategory: 6
subKey: 0
totalTradeCount: 199878658

chooseKey: 0
count: 94950
grade: 3
keyType: 0
mainCategory: 35
mainKey: 9631
name: Knight Combat Rations
pricePerOne: 29800
subCategory: 4
subKey: 0
totalTradeCount: 13579277

Every event has all of these fields.

I need to be able to multiply the pricePerOne field by different numbers for all different items and take totals of that.

For example, the Knight Combat Rations can require 8 milk and 4 chicken meat to craft. So I need to know what 8 milk and 4 chicken meat cost and to compare that to 1 knight combat ration. But, other items might require different amounts of milk and chicken meat to craft or completely different items.

[aberkow]

I think you're on the right track with wanting to do eval and if/where clauses, but first you need to parse that JSON field. The best command for that is spath https://docs.splunk.com/Documentation/Splunk/7.3.2/SearchReference/Spath#Basic_examples, so the syntax could be something like

index=whatever...
| spath field1 output=name
| spath field2 output=pricePerOne
| eval pricePerOne=case(name="Item1", pricePerOne*5, name="Item2", pricePerOne*3)

This should handle extraction and more neatly multiplying the pricePerOne field if you want it to always be multiplied by a constant. Since I don't know the exact structure of your JSON you might need to play around a bit with the spath command before you get the output how you want it, but I think this is a path that might work for you.

Let me know if this helps!

[Tylerdygert]

My data is already parsed at the heavy forwarder.

This is an example of one event currently:
chooseKey: 0
count: 4008
grade: 0
keyType: 0
mainCategory: 25
mainKey: 9065
name: Milk
pricePerOne: 10700
subCategory: 8
subKey: 0
totalTradeCount: 67771182

I need to somehow tell my formula to look for the pricePerOne value for each individual item i need while excluding all the other items (there are hundreds of items in total) and then take a total value. Spath doesn't really seem to be that answer looking at the documentation.