topic Re: Merge the field value and its count into one field in Splunk Search

How to merge the field value and its count into one field?

man03359 — Mon, 05 Jun 2023 14:20:42 GMT

I am relatively new to Splunk and I am trying to create a field that contains the field value and its count into one merged field,

The pattern looks like this:

31/05/2023 22:40:29 (01) >> Adyen Proxy::Proxy::RaiseValidResponse::Proxy event received -> Result : FAILURE ; Source : PROCESSPAYMENTFAILURE ; Message : Validation failed: Total amount is lower than configured min amount.

Error Message = Validation failed: Total amount is lower than configured min amount.
Register = 01
Store = tkg0452
host = tkg045201.store.tjxcorp.net
source = C:\ProgramData\GlobalSTORE\AdyenLog_5_31_2023.log
sourcetype = GSTR:Adyen:log

I am trying to create one field (e.g. Error and its count )

First need to do the count of current field "Error_Message" and then merge the count with the field value

Re: Merge the field value and its count into one field

gcusello — Fri, 02 Jun 2023 17:20:55 GMT

Hi @man03359,

I suppose that you already extracted the fields, in this case you have to count the occurrences of each error message using sats, and then you can merge both the fields in one filed, something like this:

<your_search> | stats count BY error_message | eval error_message=error_message.": ".count | table error_message

If yu have to extract the error_message field, you can try this:

<your_search> | rex "(?ms)Error\s*Message\s*\=\s*(?<error_message>.*)Register" | stats count BY error_message | eval error_message=error_message.": ".count | table error_message

you can test the regex at https://regex101.com/r/ir5QRy/1

Ciao.

Giuseppe

Re: Merge the field value and its count into one field

man03359 — Mon, 05 Jun 2023 13:19:13 GMT

Yes, I have extracted the field "Error_Message" using regex, current query is :

index=idx-stores-pos sourcetype=GSTR:Adyen:log
| eval Store= substr(host,1,7)
| eval Register= substr(host,8,2)
| rex field=_raw "AdyenPaymentResponse:.+\sResult\s:\s(?<Status>.+)"
| rex field=_raw "RaiseValidResponse:.+\sMessage\s:\s(?<Error_Message>.+)"
| stats
count(eval(Status="Success")) AS Success_Count
count(eval(Status="Failure")) AS Failure_Count
BY Store Register
| eval Total= Success_Count + Failure_Count

I am trying to get the value of field "Error_Message" and its count in separate column like this -

Store	Register	Success_Count	Failure_Count	Total	Error_Message	Error_Count

Re: Merge the field value and its count into one field

isoutamo — Mon, 05 Jun 2023 14:06:55 GMT

You must add all fields into stats line, which you want to use later on. In your case probably you should add it into by like "by Store Register Error_Message" another way could be ass it as "values(Error_Message) as Error_Message" before by clause.

r. Ismo

Re: Merge the field value and its count into one field

man03359 — Mon, 05 Jun 2023 17:08:20 GMT

Hi,

I have tried Store Register Error_Message

index=idx-stores-pos sourcetype=GSTR:Adyen:log
| eval Store= substr(host,1,7)
| search Store="*"
| eval Register= substr(host,8,2)
| rex field=_raw "AdyenPaymentResponse:.+\sResult\s:\s(?<Status>.+)"
| rex field=_raw "RaiseValidResponse:.+\sMessage\s:\s(?<Error_Message>.+)"
| stats
count(eval(Status="Success")) AS Success_Count
count(eval(Status="Failure")) AS Failure_Count
BY Store Register Error_Message
| eval Total= Success_Count + Failure_Count

it doesn't give the output for success_count, failure_count and total

Getting output like this --

Store	Register	Error_Message	Success_Count	Failure_Count	Total
tkg0452	1	108 Shopper cancelled tx	0	0	0
tkg0452	1	PIN_TRIES_EXCEEDED	0	0	0
tkg0452	1	Validation failed: Total amount is lower than configured min amount.	0	0	0
tkg0452	1	WITHDRAWAL_AMOUNT_EXCEEDED	0	0	0
tkg0452	2	108 Shopper cancelled tx	0	0	0
tkg0452	2	219 Shopper cancelled ctls fallback	0	0	0

Re: Merge the field value and its count into one field

isoutamo — Tue, 06 Jun 2023 07:03:07 GMT

It seems that your log sample is not matching to your Status field extraction. Can you add some sample logs inside </> - editor block? Also Store and Register part is somehow unclear as you are pointing to host field which we don't know.