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

[man03359]

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!

[gcusello]

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

[man03359]

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

[man03359]

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 ❤️

[gcusello]

Hi @man03359 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

[gcusello · Accepted solution]

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

[man03359]

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