How to extract only latest events from particular field.

[prateeksawhney]

Hi Folks,

I need your help in fetching latest event from a particular field.

Sharing you a sample event  and query when I execute for last 15 mins.

Query -> index=Blah sourcetype=blah_blah*

Example event :-

2020-11-02 05:35:00.319, SOURCE="Tullett", COUNTVOL="879", TO_CHAR(SNAPTIME,'MM/DD/YYHH24:MI:SS')="08/31/20 00:59:00"

Initial date on this event seems to be OK which is todays date"2020-11-02 05:35:00.319", but date at the end which is field SNAPTIME_NEW seems to be old "08/31/20 00:59:00". Can you please help me with a query so that I see only latest events in a sorted manner by date in field SNAPTIME_NEW when I execute query for last 15 mins. 

Screenshot attached.

Thanks, 

Prateek

[gcusello]

Hi @prateeksawhney,

it's easy, you have to use the reverse and head commands, something like this:

index=Blah sourcetype=blah_blah*
| reverse
| head 1

Ciao.

Giuseppe 

[prateeksawhney]

@gcusello 

Thanks for your prompt response. I can see latest event on the top with field SNAPTIME.

This command is helping me a bit but not much. 

Actually with this command I can still see events with old date and I do not want them. I need events with just today's date in a sorted manner from field SNAPTIME.

I have used this command - 

index=Blah sourcetype=blah_blah*
| reverse

I am not using head in this as I need all events with today's date.

[gcusello]

HI @prateeksawhney,

using reverse, you sort in desc mode your events (it's the same thing to use "| sort -_time"), so you have the newest event.

If you have today's events you have a todays's latest event, if you have older events, you should review the time period you're using.

ciao.

Giuseppe

[prateeksawhney]

@gcusello 

Time period I am using is only last15 mins.

Can we do something in query that when I execute it for last 15 mins, it shows me data with today's date only.

Particularly in field SNAPTIME and that too with latest event on the top.

[gcusello]

Hi @prateeksawhney,

If you use the last 15 minutes, you have today's events,

if the problem is that you need to sort for SNAPTIME instead timestamp, you have to transform SNAPTIME in epochtime and sort for it in desc mode.

e.g. if SNAPTIME is something like "10/28/2020 10:18:23", you could run something like this:

your_search
| eval SNAPTIME_epoch=strptime(SNAPTIME, "%m/%d/%Y %H:%M:%S")
| sort -SNAPTIME_epoch
| head 1
| ...

Ciao.

Giuseppe

[prateeksawhney]

@gcusello 

Hi,

I have updated my SNAPTIME time format as mentioned by you in your last comment. This is my new format of event in splunk.

2020-10-30 06:40:00.056, SOURCE="Quotes", COUNTVOL="22818", TO_CHAR(SNAPTIME,'MM/DD/YYHH24:MI:SS')="10/19/20 07:00:02"

And I am using this query as mentioned by you.

index=Blah sourcetype=blah*
| eval SNAPTIME_epoch=strptime(SNAPTIME, "%m/%d/%Y %H:%M:%S")
| sort -SNAPTIME_epoch

I am not using head 1 command as I need all the results.

| head 1​

I have updated my field extraction for the new format of SNAPTIME.

But the result I am getting still does not seem to be helping as I can still see old events.  I have attached screenshot for your reference. Please check. Thanks again.

[gcusello]

Hi @prateeksawhney,

in your screenshou I don't see the SNAPTIME field, only the SNAPTIME_NEW, try to use it to generate SNAPTIME_epoch:

index=Blah sourcetype=blah*
| eval SNAPTIME_epoch=strptime(SNAPTIME_NEW, "%m/%d/%Y %H:%M:%S")
| sort -SNAPTIME_epoch

 Ciao.

Giuseppe

[prateeksawhney]

@gcusello 

Sure, updated screenshot attached.

[gcusello]

Hi @prateeksawhney,

please again, there's an error.

ciao.

Giuseppe

[prateeksawhney]

@gcusello 

Apologies and Thanks for highlighting the mistake. I have updated the query but still no success. It is still showing old events.

Screenshot attached.

[gcusello]

Hi @prateeksawhney,

I cannot see the screenshot.

Ciao.

Giuseppe

[prateeksawhney]

@gcusello 

Attaching it again.

[gcusello]

Hi @prateeksawhney,

please show me the screenshot of the logs not of the field.

Ciao.

Giuseppe

[prateeksawhney]

@gcusello 

sure, attaching the screenshot of logs.

[prateeksawhney]

@gcusello 

Were you able to check the same, any response will be highly appreciated.

Thanks again for helping.