I have the logs of WVD. I want to get the average duration of the connections in one day. So far I got this code
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"
I want to get the avg time between all the completed - started time of each user and each connection
Thanks!
you could try something like this:
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