08 Aug 2023 07:30 AM
Hello Team I need to calculate duration between start time and end time of timestamp
Solved! Go to Solution.
08 Aug 2023 09:02 AM
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
04 Jan 2024 01:34 AM
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
04 Jan 2024 11:12 AM
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.
04 Jan 2024 03:39 PM
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
04 Jan 2024 04:28 PM
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