topic Re: Timechart aggregation with 2 fields for a count over time in Splunk Search

Timechart aggregation with 2 fields for a count over time

seanawilliams — Sat, 27 Aug 2016 00:16:34 GMT

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.

Re: Timechart aggregation with 2 fields for a count over time

sundareshr — Sat, 27 Aug 2016 16:32:49 GMT

Try this

metricName="fooMetric" | bin span=1d _time | stats sum(fooCount) as sCount by _time fooKey fooLoc | WHERE sCount > 1

Re: Timechart aggregation with 2 fields for a count over time

justinatpnnl — Sat, 27 Aug 2016 17:05:31 GMT

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

Re: Timechart aggregation with 2 fields for a count over time

seanawilliams — Sun, 28 Aug 2016 00:25:39 GMT

Thanks. Looks like this nails it!