topic How can I get the total count of payments and total amount of payments? in Splunk Search

How can I get the total count of payments and total amount of payments?

gowthambr — Fri, 08 Sep 2017 04:23:54 GMT

index = elm-retail-rws source="/opt/app/jboss/current/standalone/log/PosMultipaymentProfile.log"

Re: How can I get the total count of payments and total amount of payments?

niketn — Fri, 08 Sep 2017 09:04:23 GMT

@gowthambr, you would need to provide sample events and field name based of which Payment/Amount can be pulled.

Re: How can I get the total count of payments and total amount of payments?

gowthambr — Fri, 08 Sep 2017 10:33:00 GMT

8/31/17
10:14:06.680 PM
22:14:06,680 OtSrkUlG9HcFK6SQ1GP5T2JC INFO * RWS to EWS submitCashPayment Request >>>>>:

<AccountNumber9>*</AccountNumber9>
<StatementCode>1</StatementCode>
<Amount>75.79</Amount>
<MOPClass>VISA16</MOPClass>
<SiteId>476</SiteId>
<WorkStationId>Pos</WorkStationId>
>

Re: How can I get the total count of payments and total amount of payments?

gowthambr — Fri, 08 Sep 2017 10:33:22 GMT

This event is from the webservice call

Re: How can I get the total count of payments and total amount of payments?

niketn — Fri, 08 Sep 2017 11:00:05 GMT

kindly report the above sample event using code button (101010) so that special characters do not escape. Also make sure you anonymize/mask sensitive information.

Do you already have fields extracted from XML like Amount etc? What is the identification (pattern) of Payment Data? Is there any specific root node or other node for Payment information?

Re: How can I get the total count of payments and total amount of payments?

niketn — Fri, 08 Sep 2017 11:09:18 GMT

Since crucial information of event is still pending, you can start off with the following:

index = elm-retail-rws source="/opt/app/jboss/current/standalone/log/PosMultipaymentProfile.log"  "<Amount>"  "</Amount>" NOT 
 "<Amount></Amount>"
| rex field=_raw "<Amount>(?<Amount>[^\>]+)<\/Amount>"
| stats count as Payments sum(Amount) as TotalAmount

You can eventually switch to spath so that field extraction is easy and more statistical data is available. You can also add MOPClass field and then split the TotalAmount by Method of Payment like VISA, Mastercard etc.

PS: In order to identify only the events with Payments I have added NOT "<Amount></Amount>" condition in the search this should be replaced with the search filters which selects only the events with Payment information.

Re: How can I get the total count of payments and total amount of payments?

woodcock — Fri, 08 Sep 2017 23:36:12 GMT

Just add this to your existing simple search:

| rex "<Amount>(?<Amount>[\d\.]+)<\/Amount>"
| stats count sum(Amount) AS GrandTotal

Re: How can I get the total count of payments and total amount of payments?

gowthambr — Tue, 12 Sep 2017 04:47:50 GMT

Hi Niket,
This was very helpful, and MOP was the requirement too, but could not able to find the documentation for mopclass and I am very new to splunk, not getting how to go ahead.
These are the type of transactions- VISA, MasterCard, cash, AX, ECHECK etc. could you please these things as well.
Thank you very much