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.
Show us sample events along with a mockup of the desired output. Does each event contain all 3 items?
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.
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!
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.