Splunk Search

Distinct count a field only when other field meets certain criteria

SY715
Explorer

Now I have a table like below.

ID, Result, SerNum, Place
1, success, AAAAA, XXXXX
2, success, BBBBB, YYYYY
3, failure, CCCCC, YYYYY
4, success, CCCCC, YYYYY

Now I want to get below results for each place with one search, then what will be the search strings?

  • Distinct count of SerNum which failed (XXXXX: 0, YYYYY:1)
  • Success rate (XXXXX: 1/1= 100%, YYYYY: 2/3=66.6%)

Table of Result should be like below.

Place, Failed SerNum, Success Rate
XXXXX, 0, 100%
YYYYY, 1, 66.6%

Please advise.

Tags (1)

sideview
SplunkTrust
SplunkTrust

Try this.

<your search terms>
| chart dc(SerNum) as failedSerNum over Place by Result
| fillnull success failure value=0
| eval SuccessRate=success/(success+failure)
| eval SuccessRate=SuccessRate+"%"
| table Place FailedSerNum SuccessRate

The command doing the work here is the chart command. If you deleted everything after the chart command, what you'd see it's output to be would be:

Result       success   failure
XXXX          1               0
YYYY          2              1

SY715
Explorer

Thank you for your answer, but in this case what I really want for SuccessRate is rate of transations, not of cards.
When the table is changed as below(Result of ID4 became failure), your search strings get "YYYYY:50%" as SuccessRate, but it should be "YYYYY:66.6%".

ID, Result, SerNum, Place
1, success, AAAAA, XXXXX
2, success, BBBBB, YYYYY
3, failure, CCCCC, YYYYY
4, failure, CCCCC, YYYYY

Do you still have solution about this? I am really sorry, my former example was too bad.

0 Karma

sideview
SplunkTrust
SplunkTrust

OK. If I understand right, then you would just change the dc(SerNum) part, to "count".

<your search terms>
| chart count over Place by Result
| fillnull success failure value=0
| eval SuccessRate=success/(success+failure)
| eval SuccessRate=SuccessRate+"%"
| table Place FailedSerNum SuccessRate

PS I also saw that you voted down my answer which seems weird. On this site downvotes are more reserved for poorly written, incomplete or misleading answers.

Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...