help on complex stats command

[jip31]

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?

[bowesmana]

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.

[jip31]

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

[somesoni2 · Accepted solution]

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

[jip31]

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

[somesoni2]

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

[jip31]

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

[PickleRick]

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.

[jip31]

Yes right 

I am going to try..