Splunk Search

How to use two stats commands with different where clauses?

ahogbin
Communicator

Hello,

I am trying to use the stats command with 2 different where clauses with the end result being to use the 2 values to provide the total of scenario 1 displayed as a number and a percentage of scenario 2.

For example I have a total of 50 referrals (where clause 1) and a total of 100 quotes (where clause 2). The table would display 50 in one column and 50% in the other (reflecting half of the total quotes were referred.

My stats component of the search is

...| where acc="Inc" AND Stage = "Quote" AND processStatus="ManualRatingRequired" | stats count AS "Referrals" | appendcols [where acc="Inc" AND Stage = "Quote" | stats count AS tQuotes]

The first part is working and returns the expected number but the second (appendcols) returns 0. If I switch it around then the first part returns the expected total, but not the number of referrals.

I have tried various methods, but none seem to be achieving what I am trying to achieve.

Any pointers will be greatly appreciated and will go along way to saving my sanity.

Cheers,

Alastair

Tags (3)
0 Karma
1 Solution

sundareshr
Legend

Try this, without a sub-search

where acc="Inc" AND Stage = "Quote"  | stats count AS Quotes count(eval(processStatus="ManualRatingRequired")) as Referrals | eval perc=round(Referrals/Quotes*100, 1)."%"

View solution in original post

0 Karma

sundareshr
Legend

Try this, without a sub-search

where acc="Inc" AND Stage = "Quote"  | stats count AS Quotes count(eval(processStatus="ManualRatingRequired")) as Referrals | eval perc=round(Referrals/Quotes*100, 1)."%"
0 Karma

ahogbin
Communicator

Perfect.. thank you so much

0 Karma

ahogbin
Communicator

One other issue I am trying to figure out in relation to the above is that each quote number can repeat multiple times which in turn is producing skewed results.

If I run a search with out the duplicates being removed I get a a total of 344 over a given time period with 69 referrals yet if I remove duplicates I end up with 101 unique events with 33 referrals over the same time period.

This obviously returns very different % values.

Is there away of amending the above to return a distinct count of the total number of quotes (duplicates removed) and then the distinct count of the referred quotes (again with duplicated removed).

Cheers,

Alastair

0 Karma

ahogbin
Communicator

I have managed to get part way there with

where acc="Inc" AND Stage = "NewBusiness" | stats dc(quoteNumber) AS Quotes  count(eval(processStatus="ManualRatingRequired")) as Referrals | eval perc=round(Referrals/Quotes*100, 1)."%"

But the referrals are still being counted more than once. If I set count to dc for referrals then I , obviously, end up with only one event.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...