Let's say I have a table that looks like the following:
Date Host Port
1/1/2011 HostA 80
1/2/2011 HostA 80
1/3/2011 HostA 80
1/1/2011 HostB 443
1/2/2011 HostB 443
1/3/2011 HostB 443
How do I filter my table so that only the two most recent events for each host/port combination is displayed?
Thx.
Craig
I may be looking for a similar thing. I want to retrieve all events that match the date/time of the most recent event (because I have an audit script that runs monthly with two fields - Test and Count - and if there is a zero result for a test (the tests may change over time) then it isn't logged:
2012-01-18T22:00:00Z Valid 500
2012-01-18T22:00:00Z Fail-Pwd 3
2012-01-18T22:00:00Z Fail-NoMgr 45
2012-01-18T22:00:00Z Fail-NoExpire 7
2012-01-18T22:00:00Z Pass 1445
2012-02-22T22:25:15Z Valid 500
2012-02-22T22:25:15Z Fail-NoMgr 45
2012-02-22T22:25:15Z Fail-NoExpire 7
2012-02-22T22:25:15Z Pass 1448
Dedup will return a result from last month if there was a zero result this month (in my case, the "Fail-Pwd 3" event), so I use the following:
index="MyIndex" sourcetype="MySourcetype" [metadata index=MyIndex type=sourcetypes | where sourcetype="MySourcetype" | eval earliest = lastTime | fields earliest]
This gives me every event with a timestamp that's the same as the most recent event.
All you need is this on the end of your search:
<your search> | dedup 2 Host Port
Usually dedup only keeps the last row for each value, but you can tell it to keep the last N rows instead.
http://www.splunk.com/base/Documentation/latest/SearchReference/Dedup
It actually sounds like in your comment you're asking an entirely different question which is a bit confusing. And the answer to the second question is just "use the time controls to restrict your search to just that one date"?
Let me see if I get it -- so you want to see 2 events for each host+port combination, unless they're on different days in which case you only want to show the most recent of the two dates?
What about if I only want to see events from the 1/3/2011 date? I've tried dedup 1 host date, but I'm seeing one record from each date rather than all results from the most recent date.
Thanks for your help!!