Timechart aggregation with 2 fields for a count over time

[seanawilliams]

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.

[justinatpnnl · Accepted solution]

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

[seanawilliams]

Thanks. Looks like this nails it!

[sundareshr]

Try this

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