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
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
You can get the minimum of a field using eventstats: http://docs.splunk.com/Documentation/Splunk/5.0.2/SearchReference/eventstats
I would check the "sort" command. You can append your search with the following:
| sort recDate
Hope that helps.
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