Automate Windows Virtual Desktop (WVD) User Utilization Reporting for Chargeback Model using Azure Logic Apps
Since the announcement of Windows Virtual Desktop (WVD) in September 2018, I have seen many different scenarios and adoption by a wide range of customers.
Chargeback Model Scenario
One of the scenarios is developing a chargeback model for IT to bill their end user’s usage back to their business unit. The most common approach is a “SaaS based” per user per month (pupm), depending on the user profile type (i.e. light, medium, heavy use).
One of the other scenarios is to calculate the total session duration by each user and charge according based on a rate. For this article, I will not go deep into the rate definition but exploring on how to generate the session duration by user.
Windows Virtual Desktop Logs
Overview
Windows Virtual Desktop uses Azure Monitor for monitoring and alerts like many other Azure services. This allows admin or users with the necessary RBAC to identify issues through a single interface. The activities logs are collected in Log Analytics and there are the following categories:
- Management Activities
- Feed
- Connections
- Host Registration
- Errors
- Checkpoints
You can read up more in this link on the definition of the categories and how to start pushing diagnostics data to your Log Analytics Workspace.
Once your diagnostic data are sent to Log Analytics, you will be able to query those data using Kusto Query Language (KQL). If you are not familiar with KQL, you can use the pre-defined query based on workload category, resource type, solution and topics. For this scenario, you can look for Windows Virtual Desktop under Category to find a list of pre-defined query for common scenarios that users are using. You can read up more in this link for sample WVD query.
Definition of Session Duration in WVD
Log Analytics only reports in these intermediate states for connection activities:
- Started: when a user selects and connects to an app or desktop in the Remote Desktop client.
- Connected: when the user successfully connects to the VM where the app or desktop is hosted.
- Completed: when the user or server disconnects the session the activity took place in.
Below are simple illustration on how session duration is captured for user activity — be it Session Desktop or Remote App. For every activity captured, there is a CorrelationId which is defined as the activity Id.
Session Desktop Scenario
For this illustration, the total session duration time is calculated when the user is successfully connected at T + 2 and disconnects from the session at T + x.
Remote App Scenario
For this illustration, it is similar to the Desktop illustration but there are variation. A user can open more than 1 Remote App concurrently within a single session. Hence, the total session duration time is calculated when the user is successfully connected to the 1st Remote App at T + 2 and disconnects from this session entirely when he/she closes the last Remote App at T + x. This activity log is not dependent on when the first Remote App was closed at T + 8.
We can now proceed with the query after establishing our understanding on how session duration is calculated for every activity.
Query
In the sample query, there is a Session Duration query and you can load the query in the query editor. To get session duration activities, the data is found in WVDConnections table. You can refer to the table reference in this link. Below is the sample generated query:
The above query is to list all users by session duration in the last 24 hours — showing the Connection Type, Username and Duration (seconds) is calculated using the delta between “Connected” and “Completed” state of the activity based on the same CorrelationId. The time range can be defined in the query or the editor itself. Below is a sample query results ran in my demo tenant using a range of last 3 days:
You can add more data points if needed for your reporting by editing the query. Below shows additional information such as Session Host Name that the user connected for the activity, start and end time of the activity.
Here comes the real question for the above chargeback model:
- IT would like to have this report at the end of every month — they will not want to go to Log Analytics to run this query every single time. This report should be delivered in a proper document (i.e. excel) through channels such as email or a common repository.
- On top of the above information, IT would like to know more information about the user such as Department and Employee ID so that they can easily massage the generated data and bill respective departments.
Automation using Azure Logic Apps for Chargeback model reporting
Azure Logic Apps is a cloud service that helps you schedule, automate, and orchestrate tasks, business processes, and workflows when you need to integrate apps, data, systems, and services across enterprises or organizations.
For the above scenario, we can schedule a recurring workflow with the Recurring trigger to query Log Analytics, perform simple data manipulation with Office 365 Connector to add more user related information and send it as a CSV formatted file to an email.
Below is the sample Logic App which I have created for this scenario and I will break each trigger/action.
Recurrence Trigger
Logic Apps need to start with a trigger. For this scenario, we can create a monthly trigger to start at the first day of every month at 00:00H. I indicated for the first run of this Logic Apps for 1 April 2021. Future workloads will run based on the specified schedule.
Run query and list result
Azure Monitor Logs connector is used to run query and list the result. To use this connector, you will need to put in the necessary parameter(s). Let’s focus on the query.
For this scenario, IT would want to get the previous month usage by user. We will need to take care of the custom time range required for this query. The number of days vary each month where you can have either 30, 31 or 28 days.
In order to ensure that the time range can changes based on the number of days, I created two variable LastMonthEndDate and LastMonthStartDate to take care of this dynamism.
Initialize variable
I initialized an Array named Session Array to be used in latter to capture the output from the previous action.
For Each Loop
To process the results, I create a “Foreach” loop to process the value from the “Run query and list results” action. This loop repeats one or more actions on each item in the array.
In order to retrieve user related information, you can use the Office 365 User Connector to access user profiles in the organization. User information is inherited from DirectoryObject located in Azure Active Directory (AAD) and for WVD, the organization identities are synchronized from Active Directory (AD) using AAD sync.
From the result, we can use the UserName as the identifier required for the “Get user profile” action from this connector. If no fields are selected, this action returns a default definition of the identified user from Graph API. If you look at this default definition, Employee Id will be missing from this return value. In the Graph API for User, there are many more properties which you can retrieve. Hence, you can choose to specify the fields that you need from this action.
Once the user profile is retrieved, we can go ahead to append to our Array variable Session Array with the above information from the previous actions.
Create CSV table
Once the ForLoop is competed, we can easily create a CSV formatted table using the Session Array variable and automatically populate the column with the key defined in the array.
Send an email
Finally, we can use the “Send an email” action from Office 365 Outlook connector to send an email to an identified user in the organization and attached the generated CSV table as an attachment.
You can simply test this Logic App by running it and you should get the below result:
Summary
We are able to automate this customized reporting using Azure Logic Apps — exploring the use of different connectors available today. As always, I enjoyed the process to think through the problem statement and find the most practical approach to meet the requirements.
Now, back to regular work and more cat photos.
The opinions and views expressed here are those of my own and do not necessarily state or reflect those of Microsoft Singapore or Microsoft Corporation.
