Search to show me events that have timestamps out by a lot

[robnewman666]

Hello,

I have the following search but I want it to show me the delay less and more out by more than 1 hour in a better way:

index=av
| eval index_time=_indextime, event_time=_time
| eval delay=round((_indextime - _time)/60, 0)
| convert ctime(index_time) ctime(event_time)
| where delay<3600 OR delay>3600
| sort - index_time
| table event_time index_time delay

[ITWhisperer]

| eval hourdelay=if(delay>3600,"more than 1 hour", "less than 1 hour")

[richgalloway]

Better how?

The current query converts the delay time from seconds to minutes and then filters out anything less than 3600 minutes (2.5 days).

[scelikok]

Hi @robnewman666,

You can use tstats version

| tstats count where index=av by _indextime _time
| eval delay=abs(_indextime-_time)
| where delay > 3600
| eval index_time=_indextime
| convert ctime(index_time)
| table _time index_time delay

which will run much faster;

[robnewman666]

Thanks, this does look better, so will try tomorrow. Thanks @scelikok! 🙂