topic Re: How do I show only the two most recent events in a table? in Splunk Search

How do I show only the two most recent events in a table?

jambajuice — Thu, 20 Jan 2011 12:59:20 GMT

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

Re: How do I show only the two most recent events in a table?

sideview — Thu, 20 Jan 2011 15:04:45 GMT

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

Re: How do I show only the two most recent events in a table?

jambajuice — Fri, 21 Jan 2011 05:27:28 GMT

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!!

Re: How do I show only the two most recent events in a table?

sideview — Fri, 21 Jan 2011 07:20:38 GMT

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?

Re: How do I show only the two most recent events in a table?

sideview — Mon, 24 Jan 2011 02:47:11 GMT

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"?

Re: How do I show only the two most recent events in a table?

FunPolice — Thu, 23 Feb 2012 00:39:46 GMT

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.