Kusto - получить среднее значение метки времени

Question

У меня есть логи WVD. Я хочу получить среднюю продолжительность подключений за один день. Пока я получил этот код

datatable(Timestamp:datetime, User:string, State:string)
[ 
    datetime(10/23/2019, 7:02:02.527 AM), 'user1', 'Started', , 
    datetime(10/23/2019, 7:02:09.244 AM), 'user1', 'Connected', 
    datetime(10/23/2019, 7:14:21.156 AM), 'user1', 'Completed', 
    datetime(10/23/2019, 7:29:27.195 AM), 'user1', 'Started', 
    datetime(10/23/2019, 7:29:30.544 AM), 'user1', 'Connected', 
    datetime(10/23/2019, 7:45:35.438 AM), 'user1', 'Completed', 
]
| where State == "Started" or State == "Completed"
| where datetime_part("dayOfYear",TimeGenerated) == datetime_part("dayOfYear",todatetime('2020-06-25 01:02:03.7654321'))
| summarize arg_max(TimeGenerated, *) by State, UserName
| order by UserName asc, TimeGenerated asc 
| extend duration = iff(UserName == prev(UserName), TimeGenerated - prev(TimeGenerated), 0s)
| summarize avg(duration) by UserName, State
| where State == "Completed"

Я хочу получить среднее время между завершением - временем начала каждого пользователя и каждого подключения

Спасибо!

Answer 1

Вы можете попробовать что-то вроде этого:

datatable(Timestamp:datetime, User:string, State:string)
[ 
    datetime(10/23/2019, 7:02:02.527 AM), 'user1', 'Started', 
    datetime(10/23/2019, 7:02:09.244 AM), 'user1', 'Connected', 
    datetime(10/23/2019, 7:14:21.156 AM), 'user1', 'Completed', 
    datetime(10/23/2019, 7:29:27.195 AM), 'user1', 'Started', 
    datetime(10/23/2019, 7:29:30.544 AM), 'user1', 'Connected', 
    datetime(10/23/2019, 7:45:35.438 AM), 'user1', 'Completed', 
]
| where State == "Started" or State == "Completed"
| where startofday(Timestamp) == datetime(2019-10-23)
| order by User asc, Timestamp asc 
| extend duration = iff(User == prev(User) and State == 'Completed' and prev(State) == "Started", Timestamp - prev(Timestamp), timespan(null))
| where isnotnull(duration)
| summarize avg(duration) by User // you can remove the 'by User' if you don't need it. it wasn't completely clear from your question