Splunk Search

Display average with field values for each eventid.

pin99
New Member

For each eventID I have a, b, c fields on multiple hosts. I need to build report to present daily values of a, b, c field and avg(a) for a day for each host.

Something like select avg(a), b, c from ... group by a,b,c. What would be equivalent of this statement in Splunk search?

Tags (2)
0 Karma

gkanapathy
Splunk Employee
Splunk Employee

Probably the direct translation that you are looking for:

... | stats avg(a), first(a), first(b), first(c) by a,b,c

although if you are grouping by a, b, and c, it doesn't make a lot of sense to also be asking for a, b, and c, as the values will always be identical. (It's similarly pointless in the SQL version.) Note that with Splunk, you must specify first(b) as b for example, rather than just b, because you must say what to do if there happen to be multiple values of b in one of the groups. When you just ask for b in SQL, it's basically either taking the first, last, or some arbitrary value from the group. In the particular case where you are splitting by b (among other fields), there will only be one possible value, so it's unnecessary to ask for it. So you could actually get the exact same information with:

... | stats avg(a) by a,b,c

However, you might actually mean to query for:

... | stats avg(a), first(a) as a, first(b) as b, first(c) as c by x,y,z

which would be the equivalent of the SQL:

select avg(a),a,b,c from ... group by x,y,z

(Again, we have to tell Splunk how to resolve potential multiple values of a, b, or c. Using the function first() is basically what SQL does if you don't specify an aggegration function.)

Then, if you need it split by day:

... | bucket _time span=1d | stats avg(a) first(a) as a first(b) as b first(c) as c by _time,x,y,z

will do it.

Lowell
Super Champion

I'm not sure what a, b, c are, you can't get the average of a and group by it at the same time, I don't think. That doesn't make sense.

If you are searching one day a time, then try one of these:

... | stats avg(a), avg(b), avg(c) by host


... | stats avg(a) by b, c, host

If you are looking for a daily breakdown of the avg(a) over a larger range, then you have to do some trickery do do a "group by" over multiple columns:

... | eval group_field = b." ".c." ".host | timechart span=1d avg(a) by group_field

hexx
Splunk Employee
Splunk Employee

Could you provide a sample event? I'm not sure I understand how your data is structured at the Splunk event-level.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...