How to filter interval in log?

[quangtran]

I have a search like this:

sourcetype = Grandstream  | stats count by _time phone starttime answer endtime

result:

_time phone starttime answer endtime count

2022-08-09 14:30:42	xxx39xxxx	2022-08-04 14:33:58	2022-08-04 14:34:02	2022-08-04 14:34:02	1
2022-08-09 14:30:42	xxx394xxxx	2022-08-04 14:34:02	2022-08-04 14:34:02	2022-08-04 14:34:02	1
2022-08-09 14:30:42	xxx1394xxx	2022-08-04 14:34:03	2022-08-04 14:34:03	2022-08-04 14:34:09	1
2022-08-09 14:30:42	xxx1382xx	2022-08-09 14:28:52	2022-08-09 14:28:52	2022-08-09 14:29:25	1

But _time and starttime don't match because the log time is pushed wrong

is there a way to filter the starttime field by its time in a week from 0h Friday to 24h Thursday?

thanks 

[PickleRick]

Regardless of an immediate walkaround (parsing with strptime and filtering on that) I'd do two things:

1) Fix timestamp parsing since apparently it doesn't reflect the "real" timestamp from the event

2) Think which of the timestamps is the one that you need as your _time field (you have at least three different timestamps in your events as we can see) and consider parsing the others into indexed fields - In general indexed fields should not be used very often but in this case it might be worthwhile since datetime parsing is a relatively heavy operation and parsing it once on ingest and storing the parsed indexed values in indexed fields could give you a significant performance boost if you're using those fields often.

[quangtran]

thank you for the very detailed answer, but can't fix the real time problem at the moment

[ITWhisperer]

You should parse the starttime so that it is an epoch time, then you can do comparisons / filtering on it

| eval starttime_epoch=strptime(starttime,"%Y-%m-%d %H:%M:%S")

Which Friday /Thursday do you want to filter by?

[quangtran]

hello, I want to filter the period from last Friday to Thursday this week, thanks you

[ITWhisperer · Accepted solution]

| where starttime_epoch >= relative_time(now(), "@w-2d") AND starttime_epoch < relative_time(now(),"@w+5d")

[quangtran]

thanks you very much