Challenge with multi-value fields: extraction and sums within an event

[evansche]

I am looking at eCommerce ordering events often which comprise multiple lineitems. I want to sum a couple of repeated fields within each event. I
m having no trouble delineating events in Splunk, just trouble with MV fields within XML within the body of events.

Here's an example of what two line-items may look like, within the body of one event:

     <Fulfill count='2'  contentProductId='134485742992' name='24113453685731' globalProductCode='63353-99'>
      <Price singlePrice='12.0' totalPrice='24.0' />
      <File size='15000' md5='166859bce8275ec186a08d0a582d4d3' name='bla2.resource' url='http://x.y.com' />
      <Attributes>
        <Attribute id='OriginatorOrderItemNumber' value='1' />
      </Attributes>
    </Fulfill>
    <Fulfill count='1' contentProductId='157283999916' name='0112997387722' globalProductCode='11901-96'>
      <Price singlePrice='6.5' totalPrice='6.5' />
      <File size='772344' md5='972df68f629b2267c63559cdf7926507' name='bla3.resource' url='http://y.z.com' />
      <Attributes>
        <Attribute id='OriginatorOrderItemNumber' value='2' />
      </Attributes>
    </Fulfill>

I want to sum the "count" fields, (are these MV?) 2 + 1 = 3
and I want to sum the "totalPrice" fields" 24.0 + 6.5 = 30.5

I think I know what I'm doing to trim off the values' single-quotes, e.g.:

eval mycount = trim(count,"'")

But I've had no luck whatsoever with the multi-value commands for eval, such as mvindex and mvcount.

Again, I'd be happy with simple sums.

Help?

[gkanapathy · Accepted solution]

the stats sum() operation will automatically sum up and include multivalue fields within an event. However, I suspect you want to sum up only within a single event. If you have a small fixed number of multivalues, you can simply use mvindex(count,0) + mvindex(count,1) + .... but otherwise you might have to do something like stats sum(count), sum(totalPrice) by _cd. (_cd is a field whose value will be unique within a result set. The actual value is not useful, but it gives a way of splitting up events). It may be much better for you to use stats sum(count) sum(totalPrice) by orderNumber assuming there is some order number in your data that you can group by.

[evansche]

Hmm -- even after I take care of the single-quotes with eval and trim, I still see stats sum(trimmedcount) == 1 for the above event, where I should see 3. I believe I need to seriously study setting up the proper MV field via fields.conf. I must be failing there...

[Simeon]

You probably need to use the xpath command:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Xpath

See example 1 in the above reference link.

[evansche]

I ended up with more luck with xpath than hacking in VM fields via fields.conf. Still seeing issues with multiple xpaths and multiple MV fields, per http://splunk-base.splunk.com/answers/5797/xml-parsing-with-xpath