topic Re: Help creating search to retrieve the results of the sum of Pb + Pb2 + Pb3 classed by name and town in Splunk Enterprise

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

jip31 — Fri, 08 Apr 2022 15:58:58 GMT

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

something like this

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

could you help please?

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

richgalloway — Fri, 08 Apr 2022 17:01:08 GMT

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

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

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

jip31 — Sat, 09 Apr 2022 07:08:12 GMT

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

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

PickleRick — Sat, 09 Apr 2022 07:22:36 GMT

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

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

jip31 — Sat, 09 Apr 2022 09:50:36 GMT

Perfect PickleRick, thanks