Splunk Search

How do I sum the price of a product for repeating XML fields in a single event?

Tom_Oliveri
New Member

Here is a sample section of the XML Data I am attempting to sum:

    <Product> 
      <ProductItem>1</ProductItem>  
      <ProdCode>SampleProductCode</ProdCode>  
      <ProdPrice>400.00</ProdPrice>  
    </Product>  
   <Product> 
      <ProductItem>2</ProductItem>  
      <ProdCode>SampleProductCode</ProdCode>  
      <ProdPrice>400.00</ProdPrice>  
    </Product>  

When I use xmlkv | stats sum(ProdPrice) as Total the value returned is 400.00 and not 800.00. There can be n number of Product sections in a single XML event record.

0 Karma

niketn
Legend

@Tom_Oliveri, will the xml have single product (same product by product code) repeated multiple times or can one xml data have multiple products (codes)? What is the root xml node or which node/s wraps <Product> node?

Please try the following after providing your base search and xml path to the Product node.

<yourBaseSearch>
|  spath
|  rename "<yourXMLPathForProductNode>.Product.*" as "*"
|  eval data=mvzip(ProdCode,ProdPrice)
|  fields - _raw _time ProdCode ProdPrice ProductItem
|  mvexpand data
|  eval data=split(data,",")
|  eval ProdCode=mvindex(data,0), ProdPrice=mvindex(data,1)
|  fields - data
|  stats sum(ProdPrice) as Total by ProdCode
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Tom_Oliveri
New Member

I would like to list each ProductPrice by Product Item.

ProdItem|ProdPrice
1|400.00
2|400.00

Spath and xmllv seem to only pull the last value for the field

0 Karma

adonio
Ultra Champion

Hello there,

try the following query:

index=* source="products.txt" sourcetype="products_xml" 
| spath output=price path=Product.ProdPrice
| spath output=item path=Product.ProductItem

from here take it to any stats you would like
here is a screenshot:
alt text

hope it helps

0 Karma

Tom_Oliveri
New Member

I would like to list each ProductPrice by Product Item.

ProdItem|ProdPrice
1|400.00
2|400.00

Spath and xmllv seem to only pull the last value for the field

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