Splunk Search

Timechart aggregation with 2 fields for a count over time

seanawilliams
New Member

I have events of this form:

fooKey="abc", fooLoc="5", fooCount="1"
fooKey="def", fooLoc="10", fooCount="1"
fooKey="abc", fooLoc="5", fooCount="1"
fooKey="ghi", fooLoc="15", fooCount="2"
fooKey="ghi", fooLoc="15", fooCount="3"

I want to get back a timechart with 1-day spans to display counts totals for each unique fooKey and fooLoc combination; i.e. to aggregate into

fooKey:"abc", fooLoc: "5" => count 2
fooKey:"def", fooLoc:"10" => count 1
fooKey:"ghi", fooLoc:"15" => count 5

And filter out and don't show the ones where the count is not greater than 1. So far, I have this, but it is not working correctly (when I try to add the where clause):

metricName="fooMetric" | eval fooKeyLoc=fooKey + "#" + fooLoc | timechart span=1d sum(fooCount) as sCount by fooKeyLoc WHERE sCount > 1

I concatenated the 2 fields with the hash, as timechart doesn't allow grouping by more than 1 field.

Many thanks in advance.

Tags (2)
0 Karma
1 Solution

justinatpnnl
Communicator

I never have been able to get the WHERE clause of the timechart command to work as I would expect. My workaround is to use the stats command to narrow my results, which I then pipe to a timechart. For yours it would look something like this:

metricName="fooMetric" | eval fooKeyLoc=fooKey + "#" + fooLoc 
| bucket _time span=1d | stats sum(fooCount) as sCount by _time, fooKeyLoc | search sCount > 1 
| timechart span=1d sum(sCount) as sCount by fooKeyLoc

View solution in original post

0 Karma

justinatpnnl
Communicator

I never have been able to get the WHERE clause of the timechart command to work as I would expect. My workaround is to use the stats command to narrow my results, which I then pipe to a timechart. For yours it would look something like this:

metricName="fooMetric" | eval fooKeyLoc=fooKey + "#" + fooLoc 
| bucket _time span=1d | stats sum(fooCount) as sCount by _time, fooKeyLoc | search sCount > 1 
| timechart span=1d sum(sCount) as sCount by fooKeyLoc
0 Karma

seanawilliams
New Member

Thanks. Looks like this nails it!

0 Karma

sundareshr
Legend

Try this

metricName="fooMetric" | bin span=1d _time | stats sum(fooCount) as sCount by _time fooKey fooLoc | WHERE sCount > 1
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...