Splunk Search

group by different fields based on some other field

pveeramani
Explorer

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)
Tags (1)
0 Karma

Lowell
Super Champion

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
Explorer

the case worked out great, thanks everyone.

0 Karma

Stephen_Sorkin
Splunk Employee
Splunk Employee

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
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...