How to calculate timestamp duration using DQL query

[mehulpatil55]

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

[sinisa_zubic]

Hi @mehulpatil55 

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

[paulthach]

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

[gbaudart]

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.

[gbaudart · Accepted solution]

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

[paulthach]

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