topic Re: help on complex stats command in Splunk Enterprise

help on complex stats command

jip31 — Wed, 27 Oct 2021 16:40:43 GMT

hello

I need to calculate a percentage value from 2 differents stats

First I tried to do something like this

index=toto sourcetype=:request web_domain="*" web_status=* | stats dc(web_domain) as nbdomain, count(web_status) as nbdomainko | eval KO=round(nbdomain/nbdomainko*100,1) | table KO

it returns a result but it's wrong because I need to count the web_status by web_domain in order to count the number of web_status by web_domain for being able to calculate my percentage value

| stats dc(web_domain) as nbdomain, count(web_status) as nbdomainko by web_domain

So I try to separate the 2 search with an append command but it returns anything

index=toto sourcetype=request web_domain="*" web_status=* | stats dc(web_domain) as nbdomain | append [ search index=toto sourcetype=:request web_domain="*" web_status=* | stats count(web_status) as nbstatus by web_domain] | eval prcerreur = round(nbdomain/nbstatus*100,1). " %" | table prcerreur

so what is the best way to solve my use case please?

Re: help on complex stats command

somesoni2 — Wed, 27 Oct 2021 17:48:15 GMT

Can you post some dummy/sample data and corresponding output? It would help understand your requirement.

Re: help on complex stats command

jip31 — Wed, 27 Oct 2021 18:18:57 GMT

Sorry its not to send lors on this topic

But i am going to be more precise

I need to calculate a pourcentage from 2 fields

First i have to count the number of différent web_domain = its ok

After i need to count the number of web_status but mandatory by web_domain because the first search count the web_domain so the second search has to also filter the events by web_domain because the pourcentage i want is the availability percentage of all the web_domain = its ok too

But what i dont succeed is to cross the 2 search un ordre to be table to calcul the percentage which consists in divising the result of the first search by the result of the second search....

Re: help on complex stats command

PickleRick — Wed, 27 Oct 2021 19:14:30 GMT

If I understand you correctly, you want to perform two different aggregating calculations and your problem is that with each of them you lose the original data so cannot calculate the other one, right?

Use eventstats. This way you append the results to the original events.

Re: help on complex stats command

jip31 — Wed, 27 Oct 2021 19:17:58 GMT

Yes right

I am going to try..

Re: help on complex stats command

bowesmana — Thu, 28 Oct 2021 01:16:26 GMT

If I understand you correctly the way to do this is

index=toto sourcetype=:request web_domain="*" web_status=* | stats count(web_status) as nbstatus by web_domain | eventstats dc(web_domain) as nbdomain | eval prcerreur = round(nbdomain/nbstatus*100,1). " %" | table prcerreur

This is first collecting all the web_status by web_domain.

After this stats, you still have all the web_domains, so just use eventstats to count the unique webdomains and then do your calculation. This will be the most efficient way as the eventstats, which can be an expensive operation, is calculated on your already aggregated data.

Re: help on complex stats command

jip31 — Thu, 28 Oct 2021 04:36:57 GMT

unfortunately its no good

I am explaining

evenstats calculate the number of web domain in each line

so the percentage is calculated for each line and its not what I need

I need a global percentage which calculate the number of web domain (like its done with eventstats) divising by the total number of web status which is calculated also for each line

and with your code there is another problem

if the number of web domain is for example 3 and the number of status the percentage is 300% instead 33,3%

thats the reason why I am doing this

index=toto sourcetype="request" web_domain="*" web_status=* | stats dc(web_domain) as nbdomain | appendcols [ search index=toto sourcetype="request" web_domain="*" web_status=* | stats count(web_status) as nbstatus ] | eval prcerreur = round(nbdomain/nbstatus*100,1). " %" | table prcerreur

it works except that like explained in the subsearch I need to add a clause "by web_domain"...

Re: help on complex stats command

somesoni2 — Thu, 28 Oct 2021 13:40:17 GMT

How about this?

index=toto sourcetype=:request web_domain="*" web_status=* | stats count(web_status) as nbstatus by web_domain | eventstats dc(web_domain) as nbdomain | stats sum(nbstatus) as nbstatus max(nbdomain) as nbdomain | eval prcerreur = round(nbdomain/nbstatus*100,1). " %" | table prcerreur

Re: help on complex stats command

jip31 — Thu, 28 Oct 2021 15:19:21 GMT

Hi somesoni

Yes it works

But your solution gives the same results that the solution I have proposed that is to say this one even if i dont stats the web_status by domain

So I am a little bit surprised but pearhaps I am mistaken and it's the godd way for doing this!

index=toto sourcetype=request" web_domain="*" web_status=* | stats dc(web_domain) as nbdomain | appendcols [ search index=toto sourcetype=request" web_domain="*" web_status=* | stats count(web_status) as nbstatus ] | eval prcerreur = round(nbdomain/nbstatus*100,2). " %" | table prcerreur

Thank to you