How to convert date_* fields to epoch time after a join to calculate time difference?

[singhbc]

I have a search that uses "join" which uses two sourcetypes to search the events and then joins them using a common field called ID. It works good to show me that for an ID, a service was called in sourcetype=A with timestamp in date_year (month, hour, minute and second) and ERROR was flagged in sourcetype=B with timestamp _time.

What i need to do now is to subtract the _time from the date_hour, date_minute and date_second to get the difference. What is the best way to do that? Should I convert the date_year, date_month and so on to epoch time first? If yes how?

[somesoni2 · Accepted solution]

You can use strptime function with eval/where to get the corresponding epoch time value from date_* fields.

try something like this

your current search so far | eval timeA=strptime(date_year."-".date_month."-".date_mday." ".date_hour.":".date_minute.":".date_second,"%Y-%b-%d %H:%M:%S") | eval duration=_time-timeA

[singhbc]

date_month is a string, how to convert it to integer value?

[somesoni2]

just updated my search to handle string date_month (replace %m with %b)

[singhbc]

Thanks so lot! This is perfect!

[somesoni2]

Not sure about the requirement here. If you have date_* fields in your sourcetype="A" then you should have a field _time as well, in epoch format. Whether subtraction of two _time (you would have rename the field to something else in order to have both time value from both the sourcetypes) values would not suffice?

[singhbc]

the date_* fields are for the timestamp for when a "service" was called for an ID, and the time is for when the ERROR occurred for the same ID and they are about 10+ seconds apart, after the "join" is done, and after the "join" is done, if i look at the events, it only shows the timestamp for the ERROR along with the event, whereas the `dateshows only in the field list on the left side. and I have confirmed that thedate_` corresponds to the timestamp for the service in sourcetype="A" in a separate standalone search for that service. hope I am able to clarify.