Transaction Totals

[tkwaller]

Hello

I'm trying to get the search to find a transaction and within that transaction is information like brokerID, listing number, quantity and list price. What I would like to do is for each listingID find the total (Quantity*listPrice) by brokerID. I can't seem to get it to work. I've tried using stats sum() and several other methods all without the results I need. Any Ideas?

This is where I stopped and I know its way off:

index=tt host=*api* "ItemID" | xmlkv | dedup ItemID | stats sum(listingID(Quantity*ListPrice, ) | sort by brokerID |

Thank you for any help you can give me

[sowings · Accepted solution]

When you say "transaction" do you mean "multiple events representing the distinct steps of a transaction" or like "business transaction" which happens to be recorded one per line?

I'm going to assume the latter, and that xmlkv is giving you the fields ItemID listingID Quantity ListPrice and brokerID.

After your basic index=tt host=*api* ItemID | xmlkv | dedup ItemID, we need to calculate the price of that transaction. I'd do this with eval: | eval this_sale=(Quantity * ListPrice). Now, each event has a new field called this_sale, in addition to the other fields from the XML. Next, we need to sum these. You've said you want it broken out by both listing ID and broker ID, so we add these as "by" fields: | stats sum(this_sale) by listingID, brokerID. If you just wanted the total by brokerID, only use that in the by clause. If you want the total sales, leave off the by clause altogether.

Happy Splunking.

[tkwaller]

Sorry I did not explain it as such but your assumption is correct. I knew it had to be something along those lines but I also knew I was missing something. It works exactly like I need it to. Thank you very much!