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.
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
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
Thanks. Looks like this nails it!
Try this
metricName="fooMetric" | bin span=1d _time | stats sum(fooCount) as sCount by _time fooKey fooLoc | WHERE sCount > 1