Splunk Search

Replace where values may not exist; makemv, multiply across mvarrays, and then sum the products

New Member

So this is going to be a little...odd. I realize I'm asking a very circumstance-specific and idiosyncratic question; but I've tried working on this for a few hours no with absolutely no luck.

The field in Question is an array. That array can be anywhere from 0 arrays; to potentially infinite arrays. A sample of the array is below:


Basically, each of the assigned items has a pre-defined "weight". Not all items with an assigned weight may appear in the field(array). For example, in the above example, if the field contains "`lifeinvpainkillers'", I would want that replaced with "1".

The eventual goal for the above would be to transform the above into:


Which I would then attempt to multiple out as follows:


And then finally sum the product into:


The issues I'm running into, in no particular order, are as follows:
1. Properly allowing for the fact that this field can be as simple as 2 expanded arrays; or as complex as 100 expanded arrays.
2. Properly replacing the text wherein the text I want to replace doesn't actually exist within the field. I would assume "fillnull" would be a potential solution; but then that would mess with the ordering of all future mv'd arrays.
3. Properly recalculating the amounts as shown above when the replacement is finished; and the numbers are where I'd like them to be.

Ultimately, I expect this to be a largely futile effort. Splunk isn't really the best system to attempt to do this in; but I figured I'd give it a shot. Any help would be greatly appreciated because I'm out of ideas.

As an example, this is an example of a much more complex array that would still need to be properly calculated by use of the same formula.

0 Karma

Re: Replace where values may not exist; makemv, multiply across mvarrays, and then sum the products



This is what I would do:

  • Create a CSV lookup where given an item name it'll return its value
  • Extract item and value from array
  • Expand each item-value pairs to events
  • Obtain weight from lookup
  • Multiply and sum

For instance, give the following query a try. You will need to create your lookup first:

| stats count
| fields - count
| eval array = " [[`life_inv_tbacon`,2],[`life_inv_lockpick`,20],[`life_inv_redgull`,13],[`life_inv_ziptie`,20],[`life_inv_debitcard`,1],[`life_inv_blindfold`,5],[`life_inv_defib`,1],[`life_inv_bloodbag`,5],[`life_inv_painkillers`,10]]"
| rex field=array max_match=0 "(?<item>`\w+`),(?<value>\d+)"
| eval itemvalue = mvzip(item, value)
| fields itemvalue
| mvexpand itemvalue
| rex field=itemvalue "(?<item>`\w+`),(?<value>\d+)"
| fields - itemvalue
| lookup itemWeight item AS item OUTPUT weight AS weight
| eval multiply = value * weight
| eventstats sum(multiply) as total

Let me know if that's what you are looking for.