I am working with azure logs and want to get better data for my monitoring. Since I never really worked with sql, kql and other I'm fairly new to it. Basic stuff is alright but currently I need/want to do something I have no idea how to achieve or if its even possible. I will post example data as well down bellow.

I have logs with columns: timestamp, message, operation_Name and operation_Id. With operation_Id I'm able to divide and group logs together. Than using message and timestamp I would like to get (in best case scenario) something like this:

logs example:

result example:

Where whole_time is average time of all FunctionsX from 1st log to last and special_time is average time of all FunctionsX from log with specific message(Request Part_One of Function X) to log with specific message(Request Part_Two of Function X).

Option where it would be 2 queries( one for whole_time and one for special_time) is still great.

I tried to prepare example data (since it is from azure logs there are more columns but I believe for what I need they are irrelevant, but if not I can edit them in).

datatable(timestamp:datetime, message:string, operation_Name:string, operation_Id:string)
[
    "2023-01-31T14:32:31.709377Z"  ,"Executed Function X..."             ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T14:32:31.7091679Z" ,"Random log message..."              ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T14:32:31.3316605Z" ,"Request Part_Two of Function X ..." ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T14:32:30.8697249Z" ,"Request Part_One of Function X ..." ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T14:32:29.3168458Z" ,"Executing Function X..."            ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T12:32:32.709377Z"  ,"Executed Function Y..."             ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T12:32:31.7091679Z" ,"Random log message..."              ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T12:32:31.3316605Z" ,"Request Part_Two of Function Y ..." ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T12:32:30.5697249Z" ,"Request Part_One of Function Y ..." ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T12:32:29.2168458Z" ,"Executing Function Y..."            ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T11:42:31.709377Z"  ,"Executed Function X..."             ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
   ,"2023-01-31T11:42:31.7091679Z" ,"Random log message..."              ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
   ,"2023-01-31T11:42:31.3316605Z" ,"Request Part_Two of Function X ..." ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
   ,"2023-01-31T11:42:30.8697249Z" ,"Request Part_One of Function X ..." ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
   ,"2023-01-31T11:42:29.6168458Z" ,"Executing Function X..."            ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
]

Fiddle

I will appreciate any help since I'm clueless how to achieve something like this or if it is even possible to.

CodePudding user response：

datatable(timestamp:datetime, message:string, operation_Name:string, operation_Id:string)
[
    "2023-01-31T14:32:31.709377Z"  ,"Executed Function X..."             ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T14:32:31.7091679Z" ,"Random log message..."              ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T14:32:31.3316605Z" ,"Request Part_Two of Function X ..." ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T14:32:30.8697249Z" ,"Request Part_One of Function X ..." ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T14:32:29.3168458Z" ,"Executing Function X..."            ,"functionX" ,"e029727cdece47f83e26dfdbf7a913e9"
   ,"2023-01-31T12:32:32.709377Z"  ,"Executed Function Y..."             ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T12:32:31.7091679Z" ,"Random log message..."              ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T12:32:31.3316605Z" ,"Request Part_Two of Function Y ..." ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T12:32:30.5697249Z" ,"Request Part_One of Function Y ..." ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T12:32:29.2168458Z" ,"Executing Function Y..."            ,"functionY" ,"3228ac8dd386d2354cba71b1276fb3a5"
   ,"2023-01-31T11:42:31.709377Z"  ,"Executed Function X..."             ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
   ,"2023-01-31T11:42:31.7091679Z" ,"Random log message..."              ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
   ,"2023-01-31T11:42:31.3316605Z" ,"Request Part_Two of Function X ..." ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
   ,"2023-01-31T11:42:30.8697249Z" ,"Request Part_One of Function X ..." ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
   ,"2023-01-31T11:42:29.6168458Z" ,"Executing Function X..."            ,"functionX" ,"209f9245e29831539cfa5c96f8c39e0c"
]
| summarize whole_time   = max(timestamp) - min(timestamp) 
           ,special_time = take_anyif(timestamp, message startswith_cs "Request Part_Two") - take_anyif(timestamp, message startswith_cs "Request Part_One")
            by operation_Id, operation_Name
| summarize whole_time = avg(whole_time), special_time = avg(special_time) by operation_Name

Fiddle