Help creating search to retrieve the results of the sum of Pb + Pb2 + Pb3 classed by name and town

[jip31]

hello

At the end of this subsearch I would like to be able to retrieve the results of the sum of Pb + Pb2 + Pb3 classed by name and town

index=abc sourcetype=toto
| search rtt > 200 
| stats avg(rtt) as rtt by name town
| eval Pb=if(rtt>200,1,0) 
| search Pb > 0 
| append
    [ search `index=cde sourcetype=tutu 
    | stats avg(logon) as logon by name town 
    | eval Pb2=if(logon>300,1,0) 
    | search Pb2 > 0 ] 
| append
    [ search index=efg sourcetype=titi
    | stats dc(id) as id by name town
    | eval Pb3=if(id>2,1,0)
     search Pb3 >5]

something like this

| stats sum(Pb1 + Pb2 + Pb3) by name town
 

could you help please?

[richgalloway]

You almost had it!  Use the eval function within stats.

| stats sum(eval(Pb1 + Pb2 + Pb3)) by name town

[jip31]

Hi

I have 2 problems

1) if i use your example, I have the message 

Error in 'stats' command: You must specify a rename for the aggregation specifier on the dynamically evaluated field 'sum(eval(P1 + P2 ))'.

If i am just doing | eval t=(Pb1+Pb2+Pb3) | stats sum(t) by.... , it works

2) When I run the search, the result for "t" field works only if the 3 conditions are true, so my result is always 3

In the t field, I would like to have 0, 1, 2 or 3

Here is my search

`index` 
| search rtt > 200 
| stats avg(rtt) as rtt by name town site 
| eval Pb1=if(rtt>200,1,0) 
| search Pb1 > 0 
| appendcols 
    [ search `index` 
    | stats avg(logon) as logon by name town 
    | eval Pb2=if(logon>30000,1,0) 
    | search Pb2 > 0 ] 
| appendcols 
    [ search `index`  
    | stats dc(id) as id by name town 
    | eval Pb3=if(id>0,1,0) 
    | search Pb3>0
        ] 
| eval t=(Pb1+Pb2+Pb3) 
| stats sum(t) by name town

[PickleRick · Accepted solution]

Appendcols will cheat you here - there's no guarantee that all three searches will give you your partial results in the same order so the additional columns will most probably not match the earlier results.

But apart from that, let's for now assume that we're using append instead of trying to rework the search into a single one. So starting with your initial search

index=abc sourcetype=toto
| search rtt > 200 
| stats avg(rtt) as rtt by name town
| eval Pb=if(rtt>200,1,0) 
| search Pb > 0 
| append
    [ search `index=cde sourcetype=tutu 
    | stats avg(logon) as logon by name town 
    | eval Pb2=if(logon>300,1,0) 
    | search Pb2 > 0 ] 
| append
    [ search index=efg sourcetype=titi
    | stats dc(id) as id by name town
    | eval Pb3=if(id>2,1,0)
     search Pb3 >5]

 We land with some stats having name, town and a field of Pb, Pb2 or Pb3.

So now just create an artificial field from those three:

| eval temp = coalesce(Pb,Pb2,Pb3)

And sum them up

| stats sum(temp) as sum_of_Pbs by name town

[jip31]

Perfect PickleRick, thanks