topic Group by oldest Date in Splunk Search

Group by oldest Date

anava — Tue, 02 Apr 2013 18:38:44 GMT

Search:
stats count by filingType, application, recDate

filingType application recDate count

MN-CORP-FILING LACERTE 2013-03-31 1

MN-CORP-FILING LACERTE 2013-04-01 2

I need to group by oldest recDate so results look like this:

filingType application recDate count

MN-CORP-FILING LACERTE 2013-03-31 3

Re: Group by oldest Date

tgow — Tue, 02 Apr 2013 19:17:08 GMT

I would check the "sort" command. You can append your search with the following:

| sort recDate

Hope that helps.

Re: Group by oldest Date

anava — Tue, 02 Apr 2013 19:27:05 GMT

That command does not work. In SQL it would read this way:
group by filingType, application, min(recDate)

I need a way of using MIN in splunk

Re: Group by oldest Date

martin_mueller — Tue, 02 Apr 2013 19:31:22 GMT

You can get the minimum of a field using eventstats: http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/eventstats

Re: Group by oldest Date

kristian_kolb — Tue, 02 Apr 2013 20:14:21 GMT

You should use stats in a slightly different manner. The problem is that you are now grouping by a field that will have different values, namely the recDate.

Instead you should only group by the first two, and just attach the lowest/earliest value of date for every combination of filingType and application, along with the count. If your recDate is in the format you specify you may need to alter that a bit for sorting purposes.

Alternative A (this assumes that events you have came in chronologically, which makes the last retrieved event have the earliest recDate - remember that a search always returns the newest events first);

...| stats c last(recDate) as recDate by filingType, application

Alternative B (which may be just as good, though just a little longer query);

...| eval rd = strptime(recDate, "%Y-%m-%d") | stats c min(rd) as recDate by filingType, application | eval recDate = strftime(recDate, "%Y-%m-%d")

This converts the date string into epoch for each event, which makes it easy to find the min() of that. Then it's converted back to a string for presentation purposes.

EDIT: typos and errors

Hope this helps,

Kristian