Splunk Search

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

evansche
Explorer

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?

Tags (2)
0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee

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.

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

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
Explorer

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...

0 Karma

Simeon
Splunk Employee
Splunk Employee

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.

0 Karma

evansche
Explorer

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

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 ...