topic Re: Need to modify the search query to get the count of fields separately in Splunk Search

How to modify the search query to get the count of fields separately?

man03359 — Wed, 31 May 2023 14:04:13 GMT

Hi,

I am relatively new to Splunk. I am trying to achieve the output as -

Store	Register	Success_Count	Failure_Count	Total

I am using the below search query -

index=idx-stores-pos sourcetype=GSTR:Adyen:log Success OR Failure
| eval Store= substr(host,1,7) --------- extracting store and register from the host
| eval Register= substr(host,8,2)
| rex field=_raw "AdyenPaymentResponse:.+\sResult\s:\s(?<Status>.+)"
| stats count as Status_Count by Status
| eventstats sum(Status_Count) AS Total

it is giving me this result -

Status	Status_Count	Total
Failure	23	597
Success	574	597

Please help!

Re: Need to modify the search query to get the count of fields separately

gcusello — Wed, 31 May 2023 09:29:46 GMT

Hi @man03359,

please tri something like this:

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

please check the real conditions for success and failure.

Ciao.

Giuseppe

Re: Need to modify the search query to get the count of fields separately

man03359 — Wed, 31 May 2023 10:06:51 GMT

Thank you so much for the help 🙂 The above search query is giving the output like which is correct, but the register value should be in separate rows as well -

Store	Register	Success_Count	Failure_Count
tkg0452	02 04	10	1
tkg0453	02 04	14	0
tkg0459	03 04 06	8	0
tkg0477	01 03	7	0
tkg0479	02 03 05	12	0
tkg0486	03 04 05 06 07	15	0

where as I am expecting like --

Store	Register	Success_Count	Failure_Count	Total
tkg0452	2
tkg0452	4

Re: Need to modify the search query to get the count of fields separately

man03359 — Wed, 31 May 2023 10:11:22 GMT

I modified my query to -

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

which is giving me result -

Store	Register	Success_Count	Failure_Count	Total
		0	35	35
		893	0	893

not sure why Store and Register values are blank

Re: Need to modify the search query to get the count of fields separately

gcusello — Wed, 31 May 2023 10:19:32 GMT

HI @man03359,

there are two issues:

you have to use Store instead Status in the BY clause:

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

Then, please check the host value and if Store and Register fields are correctly extracted: you can check this running the search in Verbose mode and viewing the interesting fields in the events tab.

Ciao.

Giuseppe

Re: Need to modify the search query to get the count of fields separately

man03359 — Wed, 31 May 2023 10:20:19 GMT

I tried this query and its working 🙂 🙂

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

THANKS A LOT ❤️

Re: Need to modify the search query to get the count of fields separately

gcusello — Wed, 31 May 2023 10:25:50 GMT

Hi @man03359 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉