Splunk Search

How can i use stats in below mentioned Query?

shankaranantht
New Member

Find below mentioned data

Applicationname |Partners | Servicename | status
DEE | WEEEEE |Money Transfer|Success
DDD |CDDDDD | Money Transfer | Success
DDD |FFFFFFF|Money Transfer |Failed
AAA |WEEEEE |In Payee hand |Success
DEE |WEEEEE |In Payee hand |Success
DDD|CDDDDD |In Payee hand |Success
AAA |WEEEEE|Money Transfer |Success
AAA |WEEEEE |Money Validate |Success
AAA |WEEEEE |Money Receive |Success
AAA |WEEEEE |Cross Verify |Success

I have framed the query like

Sample Query

Source =data.csv
| eval status=if(isnotnull(Error), "Failed", "Success")
| eval Failcount=case(Status="Failed",1) | eval Successcount=case(Status="Success",1)
| stats count(Status) as Total_Transactions count(Failcount) as Failed_Transactions count(Successcount) as Success_Transactions values(Servicename) as Servicename
values(Applicationname) as Applicationname by PartnerId
| table partnerids ApplicationName ServiceName Success_Transactions Failed_Transactions Total_Transactions

I need the result as mentioned in the attachment, can someone help me please

Thanks in advance
alt text

Tags (1)
0 Karma
1 Solution

sundareshr
Legend

This should give you what you are looking for

source=data.csv 
| chart values(Partners) as Partners  list(Servicename) as Service list(eval(if(status="Success", 1, 0))) as Success list(eval(if(status="Failed", 1, 0))) as Fail count(status) as Txns over Applicationname

View solution in original post

sundareshr
Legend

This should give you what you are looking for

source=data.csv 
| chart values(Partners) as Partners  list(Servicename) as Service list(eval(if(status="Success", 1, 0))) as Success list(eval(if(status="Failed", 1, 0))) as Fail count(status) as Txns over Applicationname

shankaranantht
New Member

Thank u very much sundaresh.

There was a small changes need to be done in the query, but i was unable to do it.
I cannot able to get the expected result.

I need the result as mentioned below .

Applicationname partner ServiceName Succescount failcount Total
AAA WEEEEE In Payee hand 12 0 67
Money Transfer 12 0
Money Validate 19 0
Money Receive 12 12

I'm using the below query.

source=data.csv
| eval status=if(isnotnull(Error), "Failed", "Success")
|eval partnerids = coalesce(partnerid,PartnerId)
| chart values(ApplicationName) as ApplicationName values(ServiceName) as ServiceName list(eval(if(status="Success", 1, 0))) as Succalt textess
values(eval(if(status="Failed", 1, 0))) as Fail count(status) as Txns over partnerids

Thanks in advance.

0 Karma

sundareshr
Legend

If your data is as you have specified in the question, your query will not work. The sample data does not have a "Error", partnerid or PartnerId fields. Your eval statements will fail. Now, assuming your actual data does have those fields... what result are you getting when you run your query?

0 Karma

shankaranantht
New Member

I Re-framed the query which u sent and got the result sundareshr .
Thank you very much.

0 Karma

jplumsdaine22
Influencer

shankarananth - I have verified that sundaresh query is is correct based on the sample data you provided. IE if you import that sample data set you provided as a csv, and run the exact query provided by sundaresh you will get the results you are asking for .

EG: ![alt text][http://imgur.com/uQEdwAK]

If its not working for you check that the spelling (including capitals) of field names are correct - a spelling error will give you empty fields

shankaranantht
New Member

Thank u very much sundaresh.

There was a small changes need to be done in the query, but i was unable to do it.
I cannot able to get the expected result.
Note: kindly find the attachment for my expected result.

I'm using the below query.

source=data.csv
| eval status=if(isnotnull(Error), "Failed", "Success")
|eval partnerids = coalesce(partnerid,PartnerId)
| chart values(ApplicationName) as ApplicationName values(ServiceName) as ServiceName list(eval(if(status="Success", 1, 0))) as Succalt textess
values(eval(if(status="Failed", 1, 0))) as Fail count(status) as Txns over partnerids

alt text

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...