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!
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 TotalThen, 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
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 Totalplease check the real conditions for success and failure.
Ciao.
Giuseppe
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
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 ![]()
Hi @man03359 ,
good for you, see next time!
Ciao and happy splunking
Giuseppe
P.S.: Karma Points are appreciated ![]()
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 TotalThen, 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
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 | |||