CODEWITHSUNDEEP
azure-data-explorerkql
Sree
Sree

Reputation: 15

how to extract time difference between two requests using KUSTO

I have two Rawdata events where one is a request with one timestamp and the other one is a response with a different time span, is there a kusto function that can extract those two dates from rawdata and do a time difference between both

Upvotes: 1

Views: 11968

Answers (2)

james.garriss
james.garriss

Reputation: 13398

Yoni's answer has show you how to extract the timestamps. Once you have them, you can use the datetime_diff function. Say you want to know how many seconds difference there are between 2 timestamps:

| extend TimeDiff = datetime_diff('second', SigninTime, EventTime)

You could use this info to filter. Say you only want to keep rows where these timestamps are within 20 seconds of each other:

| filter TimeDiff < 20

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/datetime-difffunction

Upvotes: 1

Yoni L.
Yoni L.

Reputation: 25895

Yes, there is. It's somewhat challenging to give you the best option without seeing a sample of your input data, but you may want to look at parse operator: https://learn.microsoft.com/en-us/azure/kusto/query/parseoperator, or the extract() function: https://learn.microsoft.com/en-us/azure/kusto/query/extractfunction

or, alternatively, include a sample input in your question, like the following ones:

print request = "this<>is!!!my-request from 2019-08-14 17:54:36.8892211, the end",
      response = "this is the matching 2019-08-14 17:55:36.0000033 response"
| parse request with * "from " request_datetime:datetime "," *
| parse response with * "matching " response_datetime:datetime " response"
| project diff = response_datetime - request_datetime
// this returns a single table with a single column named 'diff', whose value is '00:00:59.1107822'

or

datatable(event_text:string, correlation_id:long) [
    "this<>is!!!my-request from 2019-08-14 17:54:36.8892211, the end", 1,
    "this is the matching 2019-08-14 17:55:36.0000033 response", 1,
]
| extend dt = extract(@"(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{7})", 1, event_text, typeof(datetime))
| summarize diff = max(dt) - min(dt) by correlation_id
// this returns a single table with 2 columns named 'correlation_id' and 'diff', whose values are are '1' and '00:00:59.1107822'

Upvotes: 1

Related Questions