group by different fields based on some other field

[pveeramani]

We have different log lines of different types. Each type holds different field names. Because of this when I use stats I want to group by all these fields that may not be on other log lines.

(index=hosts) startminutesago="10" (TypeA="1" OR TypeR="1" OR TypeU="1" OR TypeB="1") |stats avg(exectime) by field1, field3, field2, host, pname

This will not work since field mapping might be like this where some fields are not there in certain log lines. But certain fields like host, pname will be there in most log lines.

TypeA = field1
TypeR = field2
TypeU = field3
TypeB = field1

So is there a way to do this and the result being

fields | host|pname|avg(execTime)

[Lowell]

The two most obvious solutions include:

1.) Simply give a default value to all your group-by fields that way individual results are not lost simply because of a missing field.

.... | fillnull value="" field1 field2 field3 | stats avg(exectime) by field1, field2, field3, host, pname

2.) Combine your group fields into fewer fields using an eval or rename operation. With 3 fields, the easiest way is to use the coalesce() function:

... | eval group_field=coalesce(field1, field2, field3) | stats avg(exectime) by group_field, host, pname

---

If you want to get more fancy some some reason, a slightly more "sophisticated" approach would be to use a the case() eval function. This would pick which field to use based on the type of your event. The advantage of of this kind of approach is that it could be extended to support even more complicated logic. For example, say that a TypeU event contain both a field1 and field3 but you only want it to use field3. Or say you have some different type of event that needs to use two fields (i.e. field4+field5) to get group by value that you want. This slightly more explicit approach would give you full control in any of these scenarios:

Here's an example:

... | eval field=case(TypeA==1 OR TypeB==1, field1,  TypeR==1, field2, TypeU==1, field3,  TypeX==1, field4+field5) | stats avg(exectime) by field, host, pname

[pveeramani]

the case worked out great, thanks everyone.

[Stephen_Sorkin]

There are a couple ways of doing this. First you can combine field1, field2, field3 into a single field using strcat or eval:

(index=hosts) startminutesago="10" (TypeA="1" OR TypeR="1" OR TypeU="1" OR TypeB="1") | strcat field1 field2 field3 fields | stats avg(exectime) by fields, host, pname

Alternately you could use fillnull to stuff a blank value into the fields:

(index=hosts) startminutesago="10" (TypeA="1" OR TypeR="1" OR TypeU="1" OR TypeB="1") | fillnull value=NULL field1 field2 field3 | stats avg(exectime) by field1, field3, field2, host, pname