How do I write a search to get only the first 3 events for each Eventcode from Windows event logs?

[basanthp]

I am running the following search to get events from Windows event logs for the past month. I want to restrict the search to extract only first 3 events for each event code. Any pointers please?

index=xxxx sourcetype=yyyy host=zzzz | table _time,host,EventCode,EventCodeDescription,_raw

Required output:
EventCode1
EventCode1
EventCode1
EventCode2
EventCode2
EventCode2
EventCode3
EventCode3
EventCode3
.
.
.

[sideview · Accepted solution]

this will work.

index=xxxx sourcetype=yyyy host=zzzz |streamstats count as i by EventCode | search i<4 | fields host,EventCode,EventCodeDescription

streamstats goes through your rows and with each distinct EventCode treated separately, it puts a field called "i" on each row whose value is the number of rows that have had that EventCode value on them. ie, for EventCode=12 , the first such row gets i=1, second gets i=2 etc. Then we just let it go through the whole set, and then use search i<4 to throw away everything that wasn't the first three representative events for the given code.

http://docs.splunk.com/Documentation/Splunk/6.3.1/SearchReference/Streamstats

[basanthp]

Thanks for the query. But i get only 3 rows in total and I need 3 rows for each eventcode. Will go through the above link to understand the streamstats function.

[sideview]

Hmm. Well I would verify that there is in fact more than one EventCode for that combination of index source and host, by running this index=xxxx sourcetype=yyyy host=zzzz | stats count by EventCode

If there's more than one EventCode value in the set then the streamstats+search combination in my answer should be working. Here's another related example you can play with yourself that uses Splunk's index=_internal data.

index=_internal | head 10000 | streamstats global=f count as i by sourcetype | search i<4 | sort sourcetype

In the most recent 10,000 events it returns the 3 most recent events for every sourcetype.

[basanthp]

Yes. working fine now. I changed the fields command to table command and i get the expected results. Thanks.