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