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!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...