Group by oldest Date

[anava]

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

[kristian_kolb]

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

[martin_mueller]

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

[tgow]

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

| sort recDate

Hope that helps.

[anava]

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