topic Re: How to calculate timestamp duration using DQL query in DQL

How to calculate timestamp duration using DQL query

mehulpatil55 — Tue, 08 Aug 2023 06:30:36 GMT

Hello Team I need to calculate duration between start time and end time of timestamp

Re: How to calculate timestamp duration using DQL query

sinisa_zubic — Tue, 08 Aug 2023 08:02:33 GMT

Can you please elaborate more on your use case? Do you have a record with a start and end timestamp and want to calculate the duration? Or do you want to calculate the duration between two timestamps of two different records?

Best,
Sini

Re: How to calculate timestamp duration using DQL query

paulthach — Thu, 04 Jan 2024 01:34:32 GMT

I am looking for find difference between two timestamp:

I have two events, first events is orderComplete's timestamp follow by agencyComplete's timestamp event. Here the DQL:

fetch logs, from:-4h
| filter dt.security_context=="prod-srv0-isi"
| filter endsWith(log.source,"isi.log")
| filter contains(content, "agencyComplete")
| parse content, "DATA json:ac"
| filter isNotNull(ac[feed][csId])
| fields agencyComplete_Timestamp=timestamp, csNumber=ac[feed][csId]
| join [
fetch logs
| filter dt.security_context=="prod-srv0-isi"
| filter endsWith(log.source,"isi.log")
| filter contains(content, "/orderComplete: input")
| parse content, "DATA json:OC"
| filter isNotNull(OC[order][systems][0][csNumber])
| fields orderCompleteTS=timestamp,csNumber=OC[order][systems][0][csNumber]
], on: csNumber

The result is something like this:

agencyComplete_Timestamp,csNumber,right.csNumber,right.orderCompleteTS
"2024-01-03 20:15:32.634","D065079302","D065079302","2024-01-03 20:12:28.302"
"2024-01-03 19:53:34.111","D065079299","D065079299","2024-01-03 19:49:59.684"

I would difference between these two timestamp.

Thanks

PVThach

Re: How to calculate timestamp duration using DQL query

gbaudart — Thu, 04 Jan 2024 11:12:05 GMT

Hi @paulthach,

I have a solution I think.
I'm testing it on my side, and I'll share it with you today if it works.

Re: How to calculate timestamp duration using DQL query

gbaudart — Thu, 04 Jan 2024 15:39:48 GMT

I'm not very sure about my query, but you can try this:

fetch logs, from:-4h | filter dt.security_context=="prod-srv0-isi" | filter endsWith(log.source,"isi.log") | filter contains(content, "agencyComplete") | parse content, "DATA json:ac" | filter isNotNull(ac[feed][csId]) | fields agencyComplete_Timestamp=timestamp, ac_csNumber=ac[feed][csId] | lookup [ fetch logs | filter contains(content, "/orderComplete: input") | parse content, "DATA json:OC" | fields orderCompleteTS=timestamp,oc_csNumber=OC[order][systems][0][csNumber] ],sourceField: ac_csNumber, lookupField:oc_csNumber, fields:{orderCompleteTS} | fieldsAdd delay= orderCompleteTS - agencyComplete_Timestamp | fields ac_csNumber, delay

Re: How to calculate timestamp duration using DQL query

paulthach — Thu, 04 Jan 2024 16:28:11 GMT

Thanks you gbaudart,

I like your use lookup, lookupField, and sourceField. I was able to come up with similar solution as follows:

fetch logs, from:-24h
| filter dt.security_context=="prod-srv0-isi"
| filter endsWith(log.source,"isi.log")
| filter contains(content, "agencyComplete")
| parse content, "DATA json:ac"
| filter isNotNull(ac[feed][csId])
| fields agencyCompleteTS=timestamp, csNumber=ac[feed][csId]
| join [
fetch logs
| filter dt.security_context=="prod-srv0-isi"
| filter endsWith(log.source,"isi.log")
| filter contains(content, "/orderComplete: input")
| parse content, "DATA json:OC"
| filter isNotNull(OC[order][systems][0][csNumber])
| fields orderCompleteTS=timestamp,csNumber=OC[order][systems][0][csNumber]
], on: csNumber
| fields agencyCompleteTS, orderCompleteTS=right.orderCompleteTS, duration=(unixSecondsFromTimestamp(agencyCompleteTS)-unixSecondsFromTimestamp(right.orderCompleteTS)) ,csNumber