# docs.smsagent.blog Hey, I'm Trevor and this is the home of some of my documentation and solution guides around Microsoft Intune and Microsoft Azure. > Trevor Jones | smsagent.blog
# Delivery Optimization Report This report displays Delivery Optimization data from Microsoft's [Windows Update for Business Reports](https://learn.microsoft.com/en-us/windows/deployment/update/wufb-reports-overview) solution in Power BI. Download the Power BI template from [here](https://github.com/SMSAgentSoftware/MEM/tree/main/WUfB%20Delivery%20Optimization%20Report). Open opening the template, you'll be prompted for your **Log Analytics Workspace Id** and the **Data retention period**, which can also simply be the number of days of historical data you want to include in the report (up to the data retention period).
If it's the first time you've connected to the log analytics workspace with Power BI, you'll be prompted for credentials. Choose **Organizational account**, sign in with your organizational account and click **Connect**.
The account you sign in with should have at least the **Log Analytics Reader** role in the log analytics workspace. {% hint style="info" %} If you publish the report to the Power BI service I recommend adding a service principal to authenticate after publishing. {% endhint %} ## Understanding the data Delivery Optimization data is reported to Microsoft by telemetry events on the device. For example, a device downloads the latest Windows Updates after "patch Tuesday" and DO statistics for the downloads will be sent to MS via telemetry. The data for each device, grouped by content type, is added to the **UCDOStatus** table in the Log Analytics workspace. Content types include Quality Updates, Driver Updates, Office Updates etc. This table contains both current and historic data for each device. Generally, for each active device, a new row will be added to this table each day summarizing data received for each content type in the previous 28 days. This means if you query for the latest date for a device you should get data for all content types where DO has been used in the previous 28 days. {% hint style="warning" %} Data gaps and dips have been observed in the **UCDOStatus** table! Sometimes several days of data can be missing. This doesn't appear to affect the **UCDOAggregatedStatus** data though. {% endhint %} The **UCDOAggregatedStatus** table takes the device data and aggregates it for each content type each day. Therefore each row in this table will contain aggregated data from all active devices that have send telemetry for DO in the last 28 days grouped by date and content type. You can read more about the Delivery Optimization data in the [Microsoft documentation](https://learn.microsoft.com/en-us/windows/deployment/update/wufb-reports-do). ## Report pages Here's a quick walkthrough of the report pages. ### Content distribution (aggregated)
This page displays the data volumes, content types and efficiency percentages from the daily aggregated data in the **UCDOAggregatedStatus** table. Select the most recent data to view current data. ### Content distribution (general)
This page displays the data volumes, content types and efficiency percentages summarized by querying the device data in the **OCDOStatus** table. The most recent date *should* give current data but sometimes this is not complete, so do check against other dates to be sure. {% hint style="info" %} I've chosen to add TiB, GiB and MiB values separately to the report. Different units make more sense in different contexts. I personally don't like combining values with different units of measurement! {% endhint %} ### Content distribution (grouped)
This page is similar to the previous one but it allows grouping of devices by country, city, ISP or group Id - whichever may make the most sense for you and your download mode configuration. Unfortunately group Ids are encoded values and won't make much sense unless you [decode and map](https://learn.microsoft.com/en-us/windows/deployment/update/wufb-reports-do#mapping-groupid) them. ### Device configuration and data
This page displays configuration information for devices, such as peering status and download mode. You can view some of the raw data for devices from the **UCDOStatus** table. ### Daily aggregate (all content types)
On this page you can see the aggregated data from the **UCDOAggregatedStatus** table by day, with summarized values for all content types. ### Daily aggregate (by content type)
Here you can view the daily aggregated data and filter by content type. In the example you can see that there were higher content values for **Feature and Flight Updates** at the beginning of the month than at the end of the month due to a previous feature update rollout. {% hint style="info" %} The **Bandwidth optimization** and **Device count** charts only show true values if a **single** content type is selected. {% endhint %} # Windows Update for Business Custom Reporting In this guide I'll walkthrough a custom solution I created for reporting with Windows Update for Business using Azure automation, Log Analytics and Power BI. {% hint style="warning" %} Note: this solution uses the [Azure Monitor Http Data Collecter API](https://learn.microsoft.com/en-us/azure/azure-monitor/logs/data-collector-api) which has been deprecated and will no longer be functional as of **2026-09-14**. v2 of this solution which no longer makes use of the deprecated API is being worked on. {% endhint %} When we first moved from deploying software updates with ConfigMgr to using Windows Update for Business with Intune, we naturally depended on Microsoft's Update Compliance (now WUfB Reports) solution for reporting. However, for us, I found it didn't really meet our reporting needs, so I set out to develop my own reporting solution. It was a bit of work to get everything figured out, but the resulting report is much more satisfying to us and displays most of the data we want to see in a user friendly way. Particular benefits include: * *A reduction in (and control over) the data latency, meaning fresher data in the report* * *The ability to report on other kinds of updates, including drivers, .Net framework updates, .Net core updates and many others* * *Superior (in my opinion* :relaxed:*) reporting views and filtering options by reporting with Power BI* ## What kind of data can be viewed? Over 19 report pages, you can view a generous amount of data (view screenshots [here](/microsoft-endpoint-manager-reporting/windows-update-for-business-custom-reporting/power-bi-report-walkthrough)), including: * ***Compliance charts** for the latest CUs, including the monthly security, non-security preview and out of band updates* * ***Installation states** for the latest monthly security update, such as installed, pending reboot, started, failed, quality updates paused, deferred x days etc* * *Compliance against the **previous** and **previous -1** monthly updates* * ***Trend** charts for the above for viewing compliance over time* * ***Compliance table** for viewing compliance counts against individual updates* * ***A filterable devices list** for viewing compliance data for a device or group of devices* * ***Feature update adoption** trend chart* * ***Windows upgrade readiness** for viewing devices capable or not capable of Windows 11 upgrade, including safeguard holds and other blocking reasons* * ***Successful feature update** installations* * ***Failed feature update** installations, including error codes, their descriptions (where known) and SetupDiag results (where applicable)* * ***OS version, edition, SKU and support status** charts, including support start and end dates and days remaining for each release* * ***Cumulative update release history** including update type, build number, KB and release date* * *Windows Update for Business **MDM policy** settings* * *Windows Update **policy state**, including deferral periods and devices that are currently paused for feature or quality updates* * *An overview of **installation states for all types of updates**, including top failing updates* * *A filterable list of devices that have **failed to install an update** with the error codes and descriptions (where available)* * *A filterable list of devices that are **out-of-date for the latest monthly security update**, to aid in troubleshooting* There are several filters in each of the report pages to allow focused reporting on specific updates, OS versions, devices, data freshness etc. ## How does the solution work? **Intune Proactive remediations** is used to run a client-side script on a regular cadence, gathering and sending various software update-related data to a log analytics workspace in Azure. **Azure automation runbooks** are used to summarize compliance, trend the data, and gather supplementary data from various Microsoft public sources. A **Power BI report** connects to the log analytics workspace and presents the data. {% hint style="info" %} There is no dependency on or connection to the Microsoft provided reporting solution, however there is no reason not to use both if you wish. If you use the same log analytics workspace for both solutions, you could use data from both solutions in your own customized reports. {% endhint %} ## Considerations My solution may not be suitable for all, and there are some things to consider if you want to implement it: * The solution comes with no support, so you must assume any risk. Since I use the solution myself I will be maintaining it, however. * Intune proactive remediations are required. You could also use local scheduled tasks as an alternative. * Azure resources are required, so you'll need to be able to create them and have some basic familiarity with them. * Power BI licenses are required as well as some familiarity with creating Power BI reports * The solution uses the log analytics data collector and query APIs and is subject to their service limits. For most, this should not be an issue, but in very large environments it could be. * The primary key of the log analytics workspace is stored in the client-side script, which is a security concern for some as it is cached on disk (albeit only accessible to administrators). There are ways around that though, including [this ](https://www.systanddeploy.com/2022/05/removing-automatically-proactive.html)from Damien van Robaeys which deletes the script from the client after it has run. * The solution supports the English language. If any of your devices have a different OS display language than English, some minor adjustments may need to be made for all parts of the report to work correctly (I have documented the required changes). * The report does not include Delivery Optimization data; I use a separate solution for that as its a thing in itself. Please check out the [Known issues](/microsoft-endpoint-manager-reporting/windows-update-for-business-custom-reporting/known-issues-limitations) section for more details. # Power BI Report Walkthrough Here I've documented each of the pages in the Power BI report to explain the kind of data available to you. ## Filters The report pages contain several filters. Some common ones are: * **Last Sync Time** can be used to filter out devices that haven't synced with Intune in X days. You may want to use this to filter out any potentially stale or aged data. * You can also filter by **OS version and support status**, for example, to only include OS versions that are currently in support. * The **Update name** filter lets you focus on a specific update or updates. {% hint style="info" %} **Summarization time** in the report pages usually means the time the data was last summarized or updated for compliance calculations by the Azure automation runbooks. {% endhint %} ## Compliance - Latest CUs
The biggest donut chart displays the installation status of the *latest monthly security quality updates*, aka the 'B' release, aka the patch Tuesday updates. You have a table with the various installation states and device counts, and there are also compliance charts for the *latest monthly security updates*, the *latest non-security preview updates* and the *latest out-of-band updates*. Note that since these are cumulative, if a device has installed any more recent CU, it will be considered compliant to any earlier CU whether it actually installed it or not. ## Compliance - Previous CUs
This page provides an overview of compliance against previous and previous less one updates, for the *monthly security*, *non-security preview* and any *out-of-band* updates. Again, since updates are cumulative, if a device has installed any more recent CU, it will be considered compliant to any earlier CU, including for out-of-band updates. Use the filters to focus on specific updates for specific OS releases. ## Trend - Latest Security CU
This chart show the trend of installation states over time for the latest security monthly quality updates. Again use the filters to focus on something in particular, for example in the view below I am filtering on the latest update for Windows 10 21H2 and devices that are pending a restart for the update.
## Trend - Recent CUs
This trend chart is similar but it displays *compliance* states rather than *installation* states, and allows you to select other update types, such as an out-of-band update, or a previous update. ## Compliance Table - Recent CUs
Here you can view device compliance counts per update. Use the filters to focus on specific update types, or a particular release date for example. ## Devices - Recent CUs
Here you can view data for individual devices, using the filters to focus on what devices you want to see. For each device, you can see the current OS and build number, its patch age, whether its in support, the installation state for the latest monthly security update, compliance states for all recent CUs etc. The table can be easily exported to Excel if required. ## Trends - Feature Updates
Here you can view the trend over time for feature update adoption, as well as Windows 11 adoption. Hover over any time point to see the device counts per release.
## Windows Upgrade Readiness
Here you can see how many and which devices are capable of upgrading to a Windows 11 release, or are blocked by a compatibility blocker or a safeguard hold. {% hint style="info" %} This particular page will need updating periodically to accommodate new Windows 11 releases when they are generally available. {% endhint %} ## Feature Update Successes
Here you can track successful feature update installations, including which OS release they were coming from, which OS release they were going to, and when they upgraded. ## Feature Update Failures
This page focuses on any devices that have failed to install a feature update. It provides the error code and extended error code (where applicable), a friendly description of the error code (where they are documented) as well as details reported by the SetupDiag utility - for OS versions since Windows 10 2004 that automatically run this if there is an issue.
## OS Version Charts
Here you can view charts for the OS support status of your devices, W10 vs W11, OS editions and OS release counts. In the matrix, you can view the device counts by supported/unsupported, version, release and edition.
## OS Support Status
Here you can view each Windows release and edition together with its current support status, start and end dates and how many days of support are remaining. ## CU Release History
This page shows each cumulative update released for each Windows release over time including the build number, KB number and release date. Use the filters to narrow down particular updates. ## MDM WUfB Policy
This page shows you which WUfB policies have been set for each device using Microsoft Intune. For example, you can see what a feature or quality update deadline has been set to, what the grace period is, how long updates are deferred for, whether updates are scheduled or paused, etc. Because of the large number of policy settings, it is best to export the data table to Excel for detailed filtering and analysis. ## WU Policy State
This shows the current policy state for Windows update per device. This can be useful for identifying devices that have paused quality or feature updates, the start and end times of the pause, if the *TargetReleaseVersion* or *TargetProductVersion* policies are being used to upgrade or lock a device to a particular release, whether Windows update is including or excluding driver updates etc. ## Updates Installation Summary
Here you can view installation states for all kinds of Windows updates, including drivers. You can view counts per update for the different installation states, such as Installation Success, Installation Started, Download Started etc. You can also view common error codes and the updates that are affected. Use the filters to find what you are looking for. {% hint style="warning" %} This information comes from the Windows event log and will only be accurate as long as Windows update is used to install the update. Where updates have been installed in other ways that may not log to the event log, the data here may be inaccurate in those cases. {% endhint %} ## Failed Update Installations
This page focuses on updates that failed to install, for various kinds of updates. You can see the top 10 failing updates and their failure counts, as well as which type of update has the most failures. In the example here, that is clearly driver updates! Use the filters to find which devices are affected, by update, update type, installation status, error code etc. Where available, error code descriptions are provided.
{% hint style="warning" %} As for the previous page, this information comes from the Windows event log and will only be accurate as long as Windows update is used to install the update. Where updates have been installed in other ways that may not log to the event log, the data here may be inaccurate in those cases. {% endhint %} ## Lates CU Out-of-date Summary
This page and the next page are intended to help with troubleshooting of devices that have not (or not yet) installed the latest monthly security update. The filters on both pages are synced with each other, so if you filter one page, you will filter the other. For example, is the OS now out of support? There might not be any recent patches for it. Is the patch level older than 62 days? In that case, updates might be regularly failing due to an underlying issue with Windows Update or with the component store. Does the device simply need to be restarted to complete the update? Has the device synced with Intune recently? Are updates paused? Does it have WUfB policy applied? Use the filters to narrow down based on these questions. ## Latest CU Out-of-date Devices
Linked with the previous page, this one shows the affected devices. Where error codes have been reported, you can filter on those. Use the *CurrentPatchLevelAgeInDays* column to identify 'persistent offenders' - ie devices missing multiple security updates due to an underlying issue or the lack of an update policy. # Known issues / limitations This page documents some known issues and limitations with this solution. Please be aware of these before you implement it. ## Language Some parts of the report work best where the OS display language is English, ie pages that report on update status from the Windows event log. Where the OS display language differs from English, the names of some updates as well as the installation states can be localized. To handle these language differences properly, some minor code updates are needed. I have included the ability to handle the German and Italian languages in the report, and other languages can be added by following the instructions [here](/microsoft-endpoint-manager-reporting/windows-update-for-business-custom-reporting/adding-additional-language-support). ## Azure service limits Both Power BI and the Azure automation runbooks use the Log Analytics query API, and the runbooks and the client-side script also use the data collector API. Use of these Microsoft APIs is subject to their service limits, which are documented [here](https://learn.microsoft.com/en-us/azure/azure-monitor/service-limits#log-analytics-workspaces). For very large environments it may be possible to hit these service limits which could result in missing data in the report. As a reference though, an environment of \~9000 reporting devices with clients sending SU data at least twice a day, and a 4-hour summarization schedule, everything is well within these service limits. ## New Windows releases When new feature updates are released for Windows, the **Windows Upgrade Readiness** page in the report will need updating with the new release to provide compatibility data for that release. Check the [change log](/microsoft-endpoint-manager-reporting/windows-update-for-business-custom-reporting/change-log) for this solution for when an updated Power BI report template is available containing the new release. ## Incorrect installation statuses Some of the report pages (ie from **Updates Installation Summary** and on) make use of update installation data reported in the Windows event log. In some instances, updates may have been installed manually instead of using Windows Update, in which case the current installation status may not be reported to the event log. Some feature update installations may be wrongly reported as a failure because the last event logged for the update indicated a failure, but the feature update has since been successfully installed without this being reported to the event log. An example is the error code **0x80242016**, which translates to **The state of the update after its post-reboot operation has completed is unexpected**. In many cases that I have observed, the feature update did install successfully in spite of this being the last reported status in the event log. ## Data blips I have observed occasional data 'blips', ie one or two time points here and there that didn't report the full amount of data. This will be visible in the trend charts - the screenshot below is a good example of this.
I have been unable to conclusively determine the cause of this, but since the Azure automation runbooks are reporting the full data set, it is likely an issue with the backend Microsoft service that ingests data posted to the data collector API. The good news is that, if this happens, you can purge those timepoints from the log analytics tables following my instructions [here](https://smsagent.blog/2022/01/06/purging-table-data-from-a-log-analytics-workspace/). The table you need to purge will depend on which trend chart the data blip occurs in: | Power BI Report page | Log Analytics Table name | | -------------------------- | --------------------------------- | | Trend - Latest Security CU | SU\_CUComplianceTrendLatest\_CL | | Trend - Recent CUs | SU\_CUComplianceTrendExtended\_CL | | Trend - Feature Updates | SU\_FUComplianceTrend\_CL | Use the **PostedTime\_t** column for the column parameter. Using the example above, I would provide these exact parameters to purge that single anomalous timepoint from the chart in the **Trend - Recent CUs** report page: ```powershell # Purge parameters $table = "SU_CUComplianceTrendExtended_CL" $column = "PostedTime_t" $operator = "between" $values = @("2022-11-28T22:30:44Z","2022-11-28T22:30:44Z") ``` # Change log A history of changes
DateDescription
2024-04-09Published v1.4 of the Power BI template which removes outdated OS versions in the Readiness page and focuses readiness just on Windows 11 23H2.
2024-03-25Updated the devices KQL query in the summarizer runbook to better handle tables where some of the columns may not be present, or not yet be present, due to an incomplete data set in the source tables. Also update the Power BI template with similar changes to its queries.
2023-11-16Improved the parsing of the latest regular update release date in the summarizer runbook.
2023-10-20Updated W10 update history URL in the New-UpdateHistoryTable function in the summarizer runbook as MS broke the existing shortlink URL. You must update this runbook for it and the report to continue working correctly.
2023-05-12Updated the automation runbook with minor bug fixes for the Get-WUErrorCodes and Get-WindowsSetupErrorCodes functions; for the former handling the removal of duplicated error codes, for the latter excluding a couple of headers added to the web page since initial release that caused non-terminating errors. The Power BI template has also been updated to v1.2, updating the WUErrorCodes query to handle the possibility of duplicate error codes.
2023-02-10Fixed a bug in the Proactive remediations script that caused the most recent feature update upgrade date to not always be reported.
2023-02-08Updated the WU error code functions in the summarizer runbook to reflect changes made to the layout of the source web pages. Since the layouts were changes these functions were producing non-critical errors.
2022-12-20Updated the Power BI template to version 1.1 which fixes an issue where the WU_PolicyUpdate table query may fail if all the required columns are not (or not yet) present in the source log analytics table.
2022-12-01Improved the way the OS Upgrade date is detected in the client-side script for feature updates
2022-11-29Initial public release
# Deploy the solution This section provides instructions for deploying the solution ## Process overview At a high-level, these steps need to be performed: * Create the Azure resources if needed (Log Analytics workspace, Azure automation account) * Configure the Azure resources * Configure the client-side script * Deploy the client-side script * Configure the Azure automation runbooks * Deploy the runbooks * Configure the Power BI report # Create Azure Resources In this step, we'll create the required resources in Azure If you don't already have the following resources to use, create them now. {% hint style="info" %} If you are already using **Update Compliance** or **Windows Update for Business reports** you may wish to use the same Log Analytics workspace so you could join your custom data with existing data and vice-versa if desired. {% endhint %} {% hint style="info" %} If possible, keep these resources together in the same resource group. {% endhint %} ## Log Analytics Workspace Create a [Log Analytics workspace](https://learn.microsoft.com/en-us/azure/azure-monitor/logs/quick-create-workspace?tabs=azure-portal) in the subscription and region of your choice. ## Azure Automation Account Create an [Azure Automation account](https://learn.microsoft.com/en-us/azure/automation/automation-create-standalone-account?tabs=azureportal) in the same subscription and region. Be sure to enable a **system-managed identity** and allow **public network access**. # Configure Azure Resources In this step, we'll configure the Azure Automation account with the necessary permissions to access the Log Analytics workspace, and configure the data retention period of the workspace. ## Enable a managed identity If you have not already done so, enable a system-managed identity for the automation account. * In the automation account, go to **Account Settings > Identity** * In the **System assigned** tab, set the **Status** to **On**. {% hint style="info" %} You could also use a user-managed identity if you prefer. {% endhint %} ## Enable Log Analytics workspace access for the managed identity To allow the automation account to read and write data to the log analytics workspace, assign it the appropriate permission. * In the Log Analytics workspace, go to the **Access control (IAM)** blade. * Click **Add > Add role assignment**
* Select the **Log Analytics Contributor** role and click **Next** * ```
``` * Next to **Assign access to**, select **Managed identity**, then click **Select members**
* Under **Managed identity**, select **Automation Account**
* Select the automation account from the list and click **Select.** * Click **Review + assign** twice to finish. ## Log Analytics data retention By default, a log analytics workspace in the Pay as you go tier includes 31 days of free data retention. If you wish, you can increase this, but there are some things to consider if doing so: * You will be charged for data retention beyond the free 31 days * The retention period affects how much historic data you will be able to see in your software updates report, for example in the trend charts. * Longer retention times means your report queries will need to process more data and return more rows and increases the risk of hitting API service limits. To change the data retention period: * In the Log Analytics workspace, go to **General > Usage and estimated costs** * Click **Data retention** at the top * Set the desired retention period and click **OK**.
{% hint style="info" %} If you are willing to pay for it, a 60-day retention period is recommended. {% endhint %} # Deploy the client-side script In this step, we'll deploy the PowerShell script that runs on your workstations to gather software update related data and send this to your log analytics workspace. The client-side script executes on a schedule to regularly gather the latest software updates related data from your workstations and then send this to the log analytics workspace via the data collector API. The script can send both delta and full updates; my personal recommendation is to send a full update at least once a day. The script must be run in **administrative** context. Intune proactive remediations is an ideal mechanism to deploy this script and run it on a schedule. If you don't have use of this, the local task scheduler is an alternative. ## Configure the script Download the script from here: {% embed url="" %} Then configure the parameters at the top of the script.
### $FullInventorySchedule This defines the minimum frequency in **days** that a full data set should be uploaded. Recommended value is 1. ### $DeltaInventorySchedule This defines the minimum frequency in **hours** that a new delta data set can be uploaded. The actual frequency that your script will run is defined by the schedule in Intune; this is more of a safeguard to prevent data inadvertently being sent too frequently. ### $WorkspaceID The ID of your log analytics workspace. You can get this from the workspace itself in the **Overview** blade:
### $PrimaryKey The primary key of your log analytics workspace. You can get this from the workspace itself under **Settings > Agents management:**
{% hint style="warning" %} If you regenerate your keys, remember to update them in the script. {% endhint %} ### $ParentDirectoryName Enter a name, such as your company name, to be used to create a directory containing the exported update info, and a registry key containing script execution data. ## Deploy the script Here we'll use proactive remediations in Intune to deploy the script. * In the Intune portal, go to **Reports > Endpoint Analytics > Proactive remediations** * Click **Create script package** * Enter a name as a minimum, click **Next**
* For the **Detection script file**, browse for and add the script. There is no need to add a remediation script. * Be sure to set **Run script in 64-bit PowerShell** to **Yes**. * Click **Next**, add a scope tag if desired * Click **Next** and assign the script to your target workstations * For the **Schedule**, set a frequency that reflects how fresh you want your data to be. For example, every 4 hours typically means you'll get data updates sent twice a day for workstations online during normal business hours.
{% hint style="warning" %} Bear in mind that the frequency affects how much data will be sent to the log analytics workspace and how much data the kusto queries need to process. If you have a very large number of workstations avoid sending a full data set too frequently. Find out what works for you. {% endhint %} * Once your assignments and schedule are set, click **Next** and **Create** to finish.
## What does the script do? The script first checks whether a delta or full 'inventory' is required, based on the parameters you have set in the script. The script then gathers software update-related data from various places on the client, including WMI, the registry, the Windows Update agent and the Windows event log. If a full inventory is required, it converts the inventory data to json format and caches it to the local disk at %ProgramData%\\\\SoftwareUpdateReporting. If a delta is required, it calculates the delta by comparing it with the most recent full inventory and generates a delta file. The file is then posted directly to the log analytics workspace via the data collector API. The script also logs statistics to the registry at HKLM:\Software\\\\SoftwareUpdateReporting. # Deploy the Azure automation runbooks In this step we'll deploy the Azure automation runbooks that are used to gather additional Windows update data from Microsoft, summarize update data for compliance reporting, and build trend data. There are two runbooks to deploy in your Azure automation account - the summarizer and the trend processor. {% hint style="warning" %} Before running the Azure automation runbooks for the first time, make sure you already have some data from your workstations in your log analytics workspace, otherwise the runbooks will fail. Check the **Logs > Custom logs** section in your workspace for the presence of the **SU\_\*** custom logs. {% endhint %} ## Deploy the summarizer runbook The software updates summarizer runbook does the following: * Gathers additional Windows update-related information from public Microsoft sources to aid in the compliance calculations and error reporting * Summarizes the latest available software updates data from your workstations by calculating compliance against the most recent cumulative updates * Determines the current support status of your workstations * Posts the resulting summary data back to the log analytics workspace for the Power BI report to consume ### Download Download the runbook from here: {% embed url="" %} ### Set variables Open the runbook script for editing and set 4 variables at the top of the script:
**$ResourceGroupName** Enter the name of the resource group containing your log analytics workspace. **$WorkspaceName** Enter the name of the log analytics workspace. **$WorkspaceID** Enter the Workspace ID of your log analytics workspace. **$PrimaryKey** Enter the primary key for your log analytics workspace. {% hint style="warning" %} If you rotate / regenerate the workspace keys, remember to update the runbook. {% endhint %} {% hint style="success" %} If you prefer, you can use [encrypted variables](https://learn.microsoft.com/en-us/azure/automation/shared-resources/variables?tabs=azure-powershell) to store these values rather than adding them in clear text to the runbook script. {% endhint %} ### First run edits For the first execution of this runbook, you should **comment out** a couple of lines in the script to allow the runbook to retrieve Windows update and Windows setup error codes from Microsoft. On lines **894**, **895** and **937**, add a hash (#) to the beginning of the lines to comment them out, eg: (the code region is collapsed in this example)
After the first run, you can remove these hashes to allow the code section to execute only on the appropriate days. The reason this code only runs on day 7 or day 21 of the month is simply to prevent the obtained data from ageing out of the workspace. The data rarely changes so there is no need to run this code with every execution of the runbook. {% hint style="warning" %} Be sure to **uncomment** those lines again after the first run! {% endhint %} ### Create the runbook * In your Azure automation account, go to **Process Automation > Runbooks**. * Click **Import a runbook** * Use the option **Browse for file** against **Upload a runbook file** * Click the folder icon to browse for the runbook file * For the **Runbook type**, select **PowerShell** * For the **Runtime version**, select **5.1** * Enter a description if desired
Click **Import**, then **Publish** to publish the runbook. ### Start the Runbook {% hint style="warning" %} The first execution of this runbook will result in the error below. This is an expected error, so do not worry! The reason is that the log analytics query is referencing tables that have only just been created seconds ago by the script, and it takes time for the data to be ingested into the workspace. After the first execution, wait at least 5-10 minutes then execute the runbook again. There should be no further errors once the new data has been ingested. {% endhint %}
Execute the runbook for the first time by clicking **Start**. In the job view that appears, click the **All Logs** tab. Once the runbook has completed, you'll see results similar to below which includes the query statistics.
### Schedule the runbook Now you can schedule the runbook to execute at regular intervals. The interval you choose depends on: * How often you want compliance data to be refreshed * How many devices are reporting in (ie volume of data) In an environment with close to 9000 devices, I run this every 4 hours. The beauty is that you are in control here. For example, immediately after so-called 'patch Tuesday' you could run this more frequently to provide more timely updates on device compliance. Later in the month, you could reduce the frequency when a majority of devices are updated and data freshness may be less important. * In the automation runbook, click **Link to schedule** * Select the **Schedule (Link a schedule to your runbook)** option in the next pane * Click **Add a schedule** * Enter your schedule parameters. In the example below, I am creating a schedule starting at 02:00 and repeating indefinitely every 4 hours.
* Click **Create**, then **OK** to set the schedule. ## Deploy the trend processor runbook The trend processor summarizes current data for cumulative and feature update compliance and posts this data into separate tables in the log analytics workspace. This is done to offload the processing of trend data from the Power BI report queries as it can consume significant resources over longer time periods. ### Download Download the trend processor runbook from here. {% embed url="" %} ### Set variables As you did for the first runbook, open this runbook for editing and set 4 variables at the top of the script:
**$ResourceGroupName** Enter the name of the resource group containing your log analytics workspace. **$WorkspaceName** Enter the name of the log analytics workspace. **$WorkspaceID** Enter the workspaceID of your log analytics workspace. **$PrimaryKey** Enter the primary key for your log analytics workspace. {% hint style="warning" %} If you rotate / regenerate the workspace keys, remember to update the runbook. {% endhint %} {% hint style="success" %} If you prefer, you can use [encrypted variables](https://learn.microsoft.com/en-us/azure/automation/shared-resources/variables?tabs=azure-powershell) to store these values rather than adding them in clear text in the runbook script. {% endhint %} ### Create the runbook * In your Azure automation account, go to **Process Automation > Runbooks**. * Click **Import a runbook** * Use the option **Browse for file** against **Upload a runbook file** * Click the folder icon to browse for the runbook file * For the **Runbook type**, select **PowerShell** * For the **Runtime version**, select **5.1** * Enter a description if desired
Click **Import**, then **Publish** to publish the runbook. ### Start the runbook {% hint style="warning" %} The timing for executing this runbook is important as it depends on the data created by the previous runbook. Wait at least 5-10 minutes after the first runbook has completed before starting this one to allow time for the data to be ingested into the workspace. {% endhint %} Execute the runbook for the first time by clicking **Start**. In the job view that appears, click the **All Logs** tab. Once the runbook has completed, you'll see results similar to below which includes the query statistics.
### Schedule the runbook Now you can schedule the runbook to execute at regular intervals. The interval you choose depends on: * How often you want trend data points * How frequently you are running the summarizer runbook {% hint style="warning" %} As already mentioned, the timing for executing this runbook is important as it depends on data that has been created by the summarizer runbook. You should ideally schedule this to run at least 5-10 minutes after the summarizer runbook has completed to allow time for data ingestion. Schedule this to run after every execution of the summarizer runbook. {% endhint %} I run this every 4 hours, and 30 minutes after the start time of the summarizer runbook to allow ample time for data ingestion to complete. You could run it *less* frequently, but not *more* frequently than the schedule of the summarizer runbook otherwise you'll not be adding any new data. * In the automation runbook, click **Link to schedule** * Select the **Schedule (Link a schedule to your runbook)** option in the next pane * Click **Add a schedule** * Enter your schedule parameters. In the example below, I am creating a schedule starting at 02:30 and repeating indefinitely every 4 hours.
Click **Create**, then **OK** to set the schedule. # Configure the Power BI report In this step, we'll create a Power BI report from a template {% hint style="warning" %} Before configuring the Power BI report, make sure you have data from your workstations in your log analytics workspace, and make sure the automation runbooks have run at least once. Without data, the kusto queries in the Power BI report will fail. {% endhint %} ## Download Download the Power BI report template from here: {% embed url="" %} ## Configure the report Using Power BI desktop, open the report template. Upon opening, you'll be prompted for the **workspace ID** of your log analytics workspace, and the **data retention period**. The latter is used to determine how far back in time the report queries will search for data. Click **Load**.
If it's the first time you've connected to the log analytics workspace with Power BI, you'll be prompted for credentials. Choose **Organizational account**, sign in with your organizational account, and optionally select the level to apply to, or just accept the default. Click **Connect**.
{% hint style="warning" %} The account you sign in with should have at least the **Log Analytics Reader** role in the log analytics workspace. {% endhint %} After successful authentication, your report will run the kusto queries and load the data. If you have only just started collecting data from your workstations, some pages like the trend pages will not contain much data yet so will look better over time.
## Publishing to the Power BI service If you wish to publish your report to the Power BI service and enable scheduled refresh, you will need a kind of service account that can be used to connect to the log analytics workspace. It should be an **Azure AD account** (either native or synced), should have at least the **Log Analytics Reader** role in the log analytics workspace, and the password should be infrequently changed. Once you've published your report, locate the dataset and choose the **Settings** option.
Under **Data source credentials**, click **Edit credentials**.
In the **Configure** window, choose **OAuth2** as the authentication method, and **Organizational** for the privacy level. Click **Sign in**, and sign in with your service account credentials.
You can then schedule a refresh and it will use these credentials to access the log analytics workspace to update the data. {% hint style="info" %} Ideally your refresh schedule should be synchronized with the schedule of your automation runbooks. Schedule a refresh maybe 10-15 minutes after each execution of the runbooks so that your report refreshes with the latest data as soon as its available. {% endhint %} # Adding additional language support In this section, I will describe how to add support for OS display languages other than English, German or Italian. ## Update the Client-side script The client-side script detects cumulative updates for Windows by searching for a match against a specific string. In English, that string is '**Cumulative Update for**'. In other display languages, this string may be different. There are a couple of ways you can find this string. On a workstation, search in the **System** event log for the following event and find one that mentions the installation of a Cumulative Update for Windows. This will be reported differently for other languages, for example in German the string is reported as '**Kumulatives Update für**' and in Italian '**Aggiornamento cumulativo di**'.
Alternatively you can search the log analytics workspace using a query like the following: ```kusto SU_UpdateLog_CL | where UpdateName_s contains 'umulati' | distinct UpdateName_s,UpdateType_s ``` In the results of that query, I can locate the string used in German, for example:
{% hint style="warning" %} You do not need the entire update name, just the equivalent of "**Cumulative Update for**" in that language. {% endhint %} In the client-side script, you need to make updates in **two** places. Search for the variable **$CUNameArray**. The first is found on line **194** within the **Get-AvailableUpdatesOnline** function. Add your language string to this array.
The second is found on line **644** in the **UpdateLog** code region. Do the same thing and add your language string to this array.
{% hint style="success" %} Don't forget to redeploy the script to your workstations after making these changes! {% endhint %} ## Update the Summarizer automation runbook The **Software\_Updates\_Reporting\_Summarizer** runbook determines some installation states (ie **started** and **failed**) for the current monthly security quality update by checking for the status as reported in the Windows event log. In English, these installation states are '**Installation started**' and '**Installation failed**' but in other languages, they may be different. The best way to find what they are reported as in other languages is to run the following query against the log analytics workspace: ```kusto SU_UpdateLog_CL | distinct KeyWord1_s,KeyWord2_s ``` Make sure the **time range** of your query is sufficiently long enough to return lots of data. In the query results, I can clearly see these installation states reported in other languages as well as English:
For example, **Installation** in Italian is **Installazione**, and **Started** is **Avviato**. In the Azure automation runbook, locate the **Language arrays** section within the **CalculateBStatus** code region, beginning line **1736**. In these arrays, add the language equivalents of **Installation**, **Failure** and **Started**, as shown below:
{% hint style="success" %} Don't forget to save and publish your runbook when finished! {% endhint %} # Table schema reference It has been noted that in smaller environments a full set of data may not be available. This may cause issues with the automation runbooks and the Power BI report due to required columns not being present in the Log Analytics workspace tables. Recent updates to the runbooks and the report have improved the handling of potentially missing columns to avoid them throwing errors. However, it is not possible to handle every potentially missing column. Therefore I have published a reference document containing the schema for each table used in this solution. This can be used to check your own tables and add any columns that may be missing. {% hint style="info" %} Note that not every column is required or will be populated in every environment. {% endhint %} You can find the table schema reference here: To add any missing columns, in your Log Analytics workspace, go to **Tables**. Open the context menu for the table and click **Edit schema**. Add the missing column with the correct data type and save your changes. # Automating Data Exports from Microsoft Graph In this guide we will look at some options for automating the export of data from Microsoft Graph into locations such as an Azure storage account or a Log analytics workspace. If you or your organisation uses Power BI for data visualisation you probably find yourself frustrated with the lack of support for Microsoft Graph in Power BI, making it difficult to create custom reports from Intune data. It's not impossible to make use of the Web connector in Power BI to get data from Microsoft Graph but this isn't [supported or recommended by Microsoft](https://docs.microsoft.com/en-us/power-query/connecting-to-graph). With a little creativity it's still possible to use Power BI with Microsoft Graph however. You need some kind of intermediary process that can export data from Microsoft Graph into a format and location that Power BI is happy to use as a data source. If you use Microsoft Azure, you can make use of its automation services to export Graph data on a schedule using, for example, a Function app or Logic apps. My preferred method for this is to use an Azure automation account as its easy to use, inexpensive and secure. Automation accounts now support managed identities, meaning you can access Microsoft Graph and Azure resources without requiring credentials in code, or even using a Runas account. A benefit of using PowerShell to export data from Microsoft Graph is that you can process or transform your data before sending it on to a storage location. For example, some Graph entities such as for device installation status can return multiple entries per device. You may wish to clean this data and keep only the latest entry for a device, ensuring you only have one entry per device and your deviceId column contains only unique values. In order to create one-to-one relationships between tables in Power BI it is essential to have only unique values in the columns used to create relationships. Or you may wish to export a single table built with data from multiple Graph entities, for example. Your automation service could export data into an Azure storage account in CSV format which can be easily consumed by Power BI. Alternatively, you could make use of Azure Monitor logs. Your Intune data can be sent to custom logs in a Log Analytics workspace and your log queries can be used in Power BI. Both options support scheduled data refresh to keep your reports up-to-date. Setting up this automation requires some familiarity with Azure administration as well as understanding how to query Microsoft Graph with PowerShell. In this guide, I'll walk through how to set up the Azure resources to create the framework for your custom reporting, as well as provide some example PowerShell runbooks that demonstrate how to export data from MS Graph and send it to Azure storage or a Log Analytics workspace. # Azure Automation account In this section we'll create and configure an Azure automation account, grant it permissions to access Microsoft Graph and set up a PowerShell runbook. # Create / configure an Azure automation account ## Video guide Here is a video guide which I created for the Patch My PC report which covers the steps outlined below and for setting the API permissions. {% embed url="" %} ## Create an Azure automation account If you don't have one already, create an [Automation account](https://docs.microsoft.com/en-us/azure/automation/automation-create-standalone-account) in Azure. {% hint style="info" %} If you wish to use a **Run as account** to access Microsoft Graph and Azure resources be sure to select that option, however I strongly recommend using a **Managed Identity** instead. {% endhint %} ## Enable a Managed Identity In the Azure portal, locate your automation account. Under **Identity**, enable a **System assigned** identity. The identity will bear the same name as your automation account and will be visible as a service principal in Azure Enterprise applications. ![](/files/-M_RFBTlnLlW0a9uBNfX) You do not need to assign a role in Azure to this identity. # Grant API permissions ## Managed identity We need to grant API permissions to the service principal object in Azure. For a managed identity, this can only be done with PowerShell at the time of writing. Run the following PowerShell code to grant API permissions. It requires the **AzureAD** PowerShell module and **Global administrator** permissions. Set the following variables in the script: * **TenantID**. This is the tenant ID for your tenant. * **GraphAppId**. You do not need to change this. * **DisplayNameofMSI**. The display name of your managed identity, which is the same as the name of your automation account. * **Permissions**. Here you can list which permissions you want to grant. You can reference the [MS Docs](https://docs.microsoft.com/en-us/graph/permissions-reference) to find the permissions you need. In this example, I have granted some Intune device management permissions ('DeviceManagement\*') as well as some other Azure AD permissions. The permissions you assign here determine what data you can access in Microsoft Graph and you can run this again later to add additional permissions if required. {% hint style="info" %} Note these are **application** permissions not **delegated** permissions {% endhint %} ``` $TenantID="a84894e7-1234-5678-abcd-320d0334b399" $GraphAppId = "00000003-0000-0000-c000-000000000000" $DisplayNameOfMSI="" -Tenant "" ``` Once you have developed or finished debugging your script, simply change the Authentication section back to the original code and update the runbook in the automation account. ## Logging output While you are developing or testing your script locally, you can use, for example, **Write-Host**, **Write-Output**, **Write-Warning**, **Write-Verbose** to return output to the PowerShell host. When executing the runbook, however, you can't use Write-Host. You can use Write-Output but the recommended way is to use Write-Verbose and set the **$VerbosePreference** variable to **Continue** so that the output will display. Bear in mind that using Write-Output inside a function that returns to a variable will send the output to that variable and not to the output stream. More details on using output in a runbook and general troubleshooting advice can be found in the articles below. {% embed url="" %} {% embed url="" %} # Power BI Here we'll look at how to connect Power BI to your Azure storage account or Log analytics workspace. # Connect Power BI to an Azure storage account data source Coming soon... # Connect Power BI to an Azure log analytics workspace as a data source Coming soon... # Managed Devices Report This guide demonstrates how to regularly export data from Microsoft Graph using Azure automation, dump data to Azure blob storage and create a PowerBI report from the data. ## Overview In this guide we will export data from Microsoft Graph for Intune managed devices and create a PowerBI report using the exported data as a datasource. Benefits of this approach include: * Use PowerBI to create your own custom reports from any Microsoft Graph data * Reports support using a scheduled refresh in the PowerBI service for keeping data up-to-date * Everything is in the cloud - there is no requirement for on-prem resources or a data gateway * Use a managed identity or a Run as account for simplified, secure no-credential authentication * Graph data can be manipulated using PowerShell before export allowing customization of the final data set I've also included a sample PowerBI template that works with the automation runbook provided in this guide. ## High-Level Process 1. Create / configure an Azure automation account 2. Grant API permissions to the managed identity or Run as account 3. Create / configure an Azure storage account 4. Create the Automation runbook 5. Create the PowerBI report ## Requirements * Some familiarity with Azure administration * Some familiarity creating PowerBI reports * PowerShell knowledge * Appropriate permissions to create / configure the Azure resources # Create / configure an Azure automation account ## Create an Azure automation account If you don't have one already, create an [Automation account](https://docs.microsoft.com/en-us/azure/automation/automation-create-standalone-account) in Azure. {% hint style="info" %} If you wish to use a **Run as account** to access Microsoft Graph and Azure resources be sure to select that option, however I strongly recommend using a **Managed Identity** instead. {% endhint %} ## Enable a Managed Identity In the Azure portal, locate your automation account. Under **Identity**, enable a **System assigned** identity. The identity will bear the same name as your automation account and will be visible as a service principal in Azure Enterprise applications. ![](/files/-M_RFBTlnLlW0a9uBNfX) You do not need to assign a role in Azure to this identity. # Grant API permissions Here we will grant Graph API permissions to the managed identity or Run as account so it can access data from MS Graph. ## Managed identity We need to grant API permissions to the service principal object in Azure. For a managed identity, this can only be done with PowerShell at the time of writing. Run the following PowerShell code to grant API permissions. You need the **AzureAD** module and **Global administrator** permissions. Set the following variables in the script: * **TenantID**. This is the tenant ID for your tenant. * **GraphAppId**. You do not need to change this. * **DisplayNameofMSI**. The display name of your managed identity, which is the same as the name of your automation account. * **Permissions**. Here you can list which permissions you want to grant. You can reference the [MS Docs](https://docs.microsoft.com/en-us/graph/permissions-reference) to find the permissions you need. In this example, I have granted some Intune device management permissions ('DeviceManagement\*') as well as some other Azure AD permissions. {% hint style="info" %} Note these are **application** permissions not **delegated** permissions {% endhint %} ``` $TenantID="a84894e7-1234-5678-abcd-320d0334b399" $GraphAppId = "00000003-0000-0000-c000-000000000000" $DisplayNameOfMSI=" Microsoft Graph > Application permissions** * Select the permissions you require and click **Add permissions** * Reference permissions from the [MS Docs](https://docs.microsoft.com/en-us/graph/permissions-reference) * Intune permissions start with **DeviceManagement\*** * Be sure to **Grant admin consent** for those permissions for your tenant ![](/files/-M_RM2YHe9nx-Hs6Asyu) # Create / configure an Azure storage account Here we will create and configure a storage account in Azure granting the automation account the necessary permissions and creating a container for the report data. ## Create a storage account If you don't have one already create a [storage account](https://docs.microsoft.com/en-us/azure/storage/common/storage-account-create?tabs=azure-portal) in Azure. ## Create a container Under **Containers**, create a container which we will use to store the data files exported from MS Graph. I've called mine **intune-powerbi**. Set the **Public access level** to **private**. ![](/files/-M_RNXBXocKSBHe1Dld9) ## Create a custom role Here we will create a custom role at the storage account level which has just the permissions we need to upload data to the container. We will assign this role to the managed identity or run as account. * In the storage account, top-level, open the **Access Control (IAM)** pane * On the **Roles** tab, locate the role **Storage Blob Data Contributor** * Click the 3 dots on the right of the role and choose **Clone** * Give the role a name. I've used **Storage Account Reader and Blob Contributor** ![](/files/-M_ROpYgyDqg---0R4g1) * On the **Permissions** tab, add or remove the cloned permissions as required. I've added two permissions that are required by this solution and removed a couple that aren't. All permissions are found under **Microsoft.Storage**. * Add **Microsoft.Storage/storageAccounts/read** * Add **Microsoft.Storage/storageAccounts/listkeys/action** ![](/files/-M_RP_ec2Svw0oywmCNC) * On the **Assignable scopes** tab, leave the default scope which should be the storage account itself * Click **Review + create** ## Assign the role Back in the **Access Control (IAM)** pane: * Click **Add role assignment** under **Grant access to this resource** * Locate the role you just created, select it and click **Next** {% hint style="warning" %} Note even though you see Managed identity as an option here, you should not use it because at the time of writing an automation account is not yet a supported service for a storage account {% endhint %} * Select **user, group, or service principal** and click **Select members** * Search for the managed identity or run as account and select it * Click **Next**, **next** and **Review + assign** # Create an Azure automation runbook Here we will create a runbook in the automation account that will retrieve data from Microsoft Graph using the REST API and export the data to the Azure storage account. ## Import Modules We will need a couple of PowerShell modules added to the automation account for the runbook to use. * In the Azure portal in the automation account, go to the **Modules gallery** pane * Search for and import the following modules: * **Az.Accounts** * **Az.Storage** * **MSAL.PS** (only required if using a Run as account) ## Create a Runbook * Click on the **Runbooks** pane and choose **Create a runbook** * Enter a name for the runbook, select **PowerShell** for the runbook type and click **Create** * Copy the PowerShell code below into the runbook and edit it as described below * If you wish to test the runbook before publishing to make sure it works, use the **Test pane** * Alternatively, **Publish** the runbook, then click **Start** from the runbook menu. This option will give you the full output of the script. * When ready, **Publish** the runbook ### Export-MSGraphManagedDeviceData This PowerShell script can be used as-is as the source for your runbook or as simply as an example that you can modify to your requirements. {% hint style="info" %} To use the provided **Managed Devices PowerBI template**, use this code as-is because the template is configured to use the fields that this script exports, as well as the container and data file names. {% endhint %} Populate the following parameters at the top of the script: * **$ResourceGroup**. This is the name of the resource group that hosts your storage account in Azure * **$StorageAccount**. This is the name of the storage account to which you will export data * **$Container**. The name of the container to use in the storage account #### Managed Identity vs Run as account The script is configured to run using a managed identity, but code is also included to use a Run as account instead. To use a Run as account, in the **Authentication** section of the script, simply uncomment the code blocks that start with **#Run as account,** and comment out the sections that start with **#Managed Identity**. #### What the Runbook does {% tabs %} {% tab title="Authenticate" %} First we authenticate to MS Graph and obtain an access token to make our REST API calls with. We also authenticate to Azure AD in order to send data to the storage account. {% endtab %} {% tab title="Get Graph data" %} We query MS Graph to get a list of managed devices in Intune and page through the results to get the full data set. {% endtab %} {% tab title="Organize the data" %} We then separate out the managed devices by OS creating arrays for iOS, Android and Windows devices. Not every property returned by MS Graph is useful to us so we have a list of properties to exclude from the results for each OS. We also add a few properties of our own, in some cases simply expanding out nested results into their own fields and in others we add some calculated values of our own, like days since last sync, for example. {% endtab %} {% tab title="Upload the data" %} Last, we export the results locally into CSV files, one for each OS, then upload these to our storage account. Each CSV file will be placed in its own folder in the storage account container, for example: * Container > Folder > File * intune-powerbi > WindowsDevices > WindowsDevices.csv {% endtab %} {% endtabs %} ``` ########################################################################### ## Azure automation runbook PowerShell script to export device data from ## ## Microsoft Intune / Endpoint Manager and dump it to Azure Blob storage ## ## where it can be used as a datasource for Power BI. ## ########################################################################### ## Module Requirements ## # Az.Accounts # Az.Storage # MSAL.PS (if using Run as account) # Set some variables $ProgressPreference = 'SilentlyContinue' $ResourceGroup = "" # Reource group that hosts the storage account $StorageAccount = "" # Storage account name $Container = "intune-powerbi" # Container name #################### ## AUTHENTICATION ## #################### ## Get MS Graph access token # Managed Identity $url = $env:IDENTITY_ENDPOINT $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" $headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) $headers.Add("Metadata", "True") $body = @{resource='https://graph.microsoft.com/' } $accessToken = (Invoke-RestMethod $url -Method 'POST' -Headers $headers -ContentType 'application/x-www-form-urlencoded' -Body $body ).access_token $authHeader = @{ 'Authorization' = "Bearer $accessToken" } # Run as account # Requires MSAL.PS module <# $connectionName = "AzureRunAsConnection" $servicePrincipalConnection = Get-AutomationConnection -Name $connectionName $Cert = Get-Item Cert:\LocalMachine\Root\$($servicePrincipalConnection.CertificateThumbprint) $MsalToken = Get-MsalToken -ClientID $servicePrincipalConnection.ApplicationId -ClientCertificate $Cert -TenantId $servicePrincipalConnection.TenantId -Scopes 'https://graph.microsoft.com/.default' $authHeader = @{ 'Authorization' = $MsalToken.CreateAuthorizationHeader() } #> ## Connect to Azure AD # Mmanaged Identity Connect-AzAccount -Identity # Run as account #Connect-AzAccount -ServicePrincipal -Tenant $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint ######################### ## GET DATA FROM GRAPH ## ######################### $URI = "https://graph.microsoft.com/beta/deviceManagement/manageddevices" $Response = Invoke-WebRequest -Uri $URI -Method Get -Headers $authHeader -UseBasicParsing $JsonResponse = $Response.Content | ConvertFrom-Json $DeviceData = $JsonResponse.value If ($JsonResponse.'@odata.nextLink') { do { $URI = $JsonResponse.'@odata.nextLink' $Response = Invoke-WebRequest -Uri $URI -Method Get -Headers $authHeader -UseBasicParsing $JsonResponse = $Response.Content | ConvertFrom-Json $DeviceData += $JsonResponse.value } until ($null -eq $JsonResponse.'@odata.nextLink') } ############################################# ## ORGANISE THE DATA INTO USEABLE DATASETS ## ############################################# # Seperate by OS $WindowsDevices = $DeviceData | where {$_.operatingSystem -eq "Windows"} $iOSDevices = $DeviceData | where {$_.operatingSystem -eq "iOS"} $AndroidDevices = $DeviceData | where {$_.operatingSystem -eq "Android"} $UnknownDevices = $DeviceData | where {$_.operatingSystem -ne "Android" -and $_.operatingSystem -ne "iOS" -and $_.operatingSystem -ne "Windows"} # Set property exclusion lists. These properties will not be included in the final datasets. $AndroidExcludedProperties = @( 'activationLockBypassCode', 'remoteAssistanceSessionUrl', 'remoteAssistanceSessionErrorDetails', 'configurationManagerClientEnabledFeatures', 'deviceHealthAttestationState', 'totalStorageSpaceInBytes', 'freeStorageSpaceInBytes', 'requireUserEnrollmentApproval', 'iccid', 'udid', 'roleScopeTagIds', 'windowsActiveMalwareCount', 'windowsRemediatedMalwareCount', 'configurationManagerClientHealthState', 'configurationManagerClientInformation', 'ethernetMacAddress', 'physicalMemoryInBytes', 'processorArchitecture', 'specificationVersion', 'skuFamily', 'skuNumber', 'managementFeatures', 'hardwareInformation', 'deviceActionResults', 'chromeOSDeviceInfo', 'retireAfterDateTime', 'preferMdmOverGroupPolicyAppliedDateTime', 'autopilotEnrolled', 'managedDeviceId', 'managedDeviceODataType', 'managedDeviceReferenceUrl', 'usersLoggedOn', 'partnerReportedThreatState', 'chassisType' ) $iOSExcludedProperties = @( 'activationLockBypassCode', 'remoteAssistanceSessionUrl', 'remoteAssistanceSessionErrorDetails', 'configurationManagerClientEnabledFeatures', 'deviceHealthAttestationState', 'requireUserEnrollmentApproval', 'iccid', 'udid', 'roleScopeTagIds', 'windowsActiveMalwareCount', 'windowsRemediatedMalwareCount', 'configurationManagerClientHealthState', 'configurationManagerClientInformation', 'ethernetMacAddress', 'physicalMemoryInBytes', 'processorArchitecture', 'specificationVersion', 'skuFamily', 'skuNumber', 'managementFeatures', 'hardwareInformation', 'deviceActionResults', 'chromeOSDeviceInfo', 'retireAfterDateTime', 'preferMdmOverGroupPolicyAppliedDateTime', 'autopilotEnrolled', 'managedDeviceId', 'managedDeviceODataType', 'managedDeviceReferenceUrl', 'usersLoggedOn', 'partnerReportedThreatState', 'chassisType', 'freeStorageSpaceInBytes', 'totalStorageSpaceInBytes' ) $WindowsExcludedProperties = @( 'activationLockBypassCode' 'chassisType' 'jailBroken' 'remoteAssistanceSessionUrl' 'remoteAssistanceSessionErrorDetails' 'phoneNumber' 'androidSecurityPatchLevel' 'deviceHealthAttestationState' 'subscriberCarrier' 'meid' 'requireUserEnrollmentApproval' 'iccid' 'udid' 'roleScopeTagIds' 'configurationManagerClientInformation' 'ethernetMacAddress' 'physicalMemoryInBytes' 'processorArchitecture' 'specificationVersion' 'managementFeatures' 'hardwareInformation' 'deviceActionResults' 'usersLoggedOn' 'chromeOSDeviceInfo' 'totalStorageSpaceInBytes' 'freeStorageSpaceInBytes' 'configurationManagerClientEnabledFeatures' 'configurationManagerClientHealthState' 'managedDeviceId' 'managedDeviceODataType' 'managedDeviceReferenceUrl' ) # Remove the unwanted properties and add some new ones $AndroidDevices = $AndroidDevices | Select-Object -Property * -ExcludeProperty $AndroidExcludedProperties $iOSDevices = $iOSDevices | Select-Object -Property *,` @{l="freeStorageSpaceInGB";e={[math]::Round(($_.freeStorageSpaceInBytes / 1GB),2)}},` @{l="totalStorageSpaceInGB";e={[math]::Round(($_.totalStorageSpaceInBytes / 1GB),2)}} ` -ExcludeProperty $iOSExcludedProperties $WindowsDevices = $WindowsDevices | Select-Object -Property *,` @{l="freeStorageSpaceInGB";e={[math]::Round(($_.freeStorageSpaceInBytes / 1GB),2)}},` @{l="totalStorageSpaceInGB";e={[math]::Round(($_.totalStorageSpaceInBytes / 1GB),2)}}, ` @{l="daysSinceLastSync";e={[math]::Round(((Get-Date) - ($_.lastSyncDateTime | Get-Date -ErrorAction SilentlyContinue)).TotalDays,0)}}, ` @{l="enabledCoMgmtWorkloads_inventory";e={$_.configurationManagerClientEnabledFeatures.inventory}}, ` @{l="enabledCoMgmtWorkloads_modernApps";e={$_.configurationManagerClientEnabledFeatures.modernApps}}, ` @{l="enabledCoMgmtWorkloads_resourceAccess";e={$_.configurationManagerClientEnabledFeatures.resourceAccess}}, ` @{l="enabledCoMgmtWorkloads_deviceConfiguration";e={$_.configurationManagerClientEnabledFeatures.deviceConfiguration}}, ` @{l="enabledCoMgmtWorkloads_compliancePolicy";e={$_.configurationManagerClientEnabledFeatures.compliancePolicy}}, ` @{l="enabledCoMgmtWorkloads_windowsUpdateForBusiness";e={$_.configurationManagerClientEnabledFeatures.windowsUpdateForBusiness}}, ` @{l="enabledCoMgmtWorkloads_endpointProtection";e={$_.configurationManagerClientEnabledFeatures.endpointProtection}}, ` @{l="enabledCoMgmtWorkloads_officeApps";e={$_.configurationManagerClientEnabledFeatures.officeApps}}, ` @{l="MEMCMClient_state";e={$_.configurationManagerClientHealthState.state}}, ` @{l="MEMCMClient_errorCode";e={$_.configurationManagerClientHealthState.errorCode}}, ` @{l="MEMCMClient_lastSyncDateTime";e={$_.configurationManagerClientHealthState.lastSyncDateTime}}, ` @{l="MEMCMClient_daysSinceLastSync";e={[math]::Round(((Get-Date) - ($_.configurationManagerClientHealthState.lastSyncDateTime | Get-Date -ErrorAction SilentlyContinue)).TotalDays,0)}} ` -ExcludeProperty $WindowsExcludedProperties # Export the data to CSV format $androiddevices | export-csv -Path $env:temp\AndroidDevices.csv -Force -NoTypeInformation $iOSDevices | export-csv -Path $env:temp\iOSDevices.csv -Force -NoTypeInformation $WindowsDevices | export-csv -Path $env:temp\WindowsDevices.csv -Force -NoTypeInformation ########################################### ## UPLOAD DATASETS TO AZURE BLOB STORAGE ## ########################################### $StorageAccount = Get-AzStorageAccount -Name $StorageAccount -ResourceGroupName $ResourceGroup "AndroidDevices","iOSDevices","WindowsDevices" | foreach { Set-AzStorageBlobContent -File "$env:temp\$_.csv" -Container $Container -Blob $_/$_.csv -Context $StorageAccount.Context -Force } ``` ## Schedule the Runbook Schedule the Runbook to execute at regular intervals to keep the data updated. * In the Azure portal, in the automation account, open the Runbook you created * Click **Link to schedule** * In the **Schedule** section, select an existing schedule or create a new one ![](/files/-M_SHJw26sAMuYhaSEaI) # Create a PowerBI report Here we will look at how to connect PowerBI to our storage account and import our data ## Connect to Azure Blob storage * Open **PowerBI desktop** and choose **Get Data** * In the **Azure** section, choose **Azure Blob Storage** ![](/files/-M_SJj_fyEJnfeLwO7oI) * In the next window, enter the **Account name** of the storage account ![](/files/-M_SJwFkXBj3NYEEvZkw) * Next you will be prompted for the **Account key**. You'll find this in the storage account under **Access Keys**. ![](/files/-M_SKTX2L8Ge0L5zOxsw) {% hint style="warning" %} If you regenerate your storage account keys, you will need to update your PowerBI report {% endhint %} * Click **Connect** * In the **Navigator** window, select the container that holds the exported CSV files. You'll see the files in the container on the right * Click **Transform Data** ![](/files/-M_SMDtJzi_rK6SIOpwo) * In the **Power Query Editor** window, you'll see the 3 files listed. **Rename** the query for one of the files, for example **Android\_Devices**, then click on the **Binary** link in the **Content** column for the **AndroidDevices.csv**. This will transform the data from the CSV file into the query. ![](/files/-M_SMvACxpMN-mhSinui) ![](/files/-M_SNkefIEQCGuVHALNy) * Staying in the **Query Editor**, right-click the blank space on the left under **Queries \[1],** choose **New Query > Recent sources**, and select the storage account source you just used. This will bring up the **Navigator** window again. Repeat the above steps, selecting the container and transforming the content until you have all 3 queries added: ![](/files/-M_SPJ6_G917MeqYEluX) * Finally, in the **Home** menu, click **Close and Apply**. The data will then be imported from the CSV files into the report. ![](/files/-M_SQ5CTAog9jmi-cp1W) From here, you can build out your report as you wish. ![](/files/-M_SS7g9iGzSoZSQrgXr) ## Publish the report Once your report has been created, you can publish it to the PowerBI service by clicking **Publish** on the **Home** menu. Once published, you can schedule the data to refresh periodically to keep the report up to date. Do not refresh more frequently than the schedule of your automation runbook. You do not need to use a data gateway as the storage account datasource is also a cloud service. # MEM Managed Device Report template Here we will open the sample PowerBI template and create our own report from it ## Overview I have created a sample PowerBI template for MEM managed devices that you can download and use. To use it, you **must** have first exported data from MS Graph into an Azure storage account using the provided automation account runbook in the previous sections. The template is configured to use exactly the properties that this runbook exports as well as the 3 exported files for each OS. ## Screenshots ![](/files/-M_SWsa1VD3HzvfiagKV) ![](/files/-M_SX7LaCc_62xqrGZT7) ![](/files/-M_SXLH8kBktSSihDYH0) ![](/files/-M_SX_skBsxHYA_Ui0IL) ![](/files/-M_SXpLszyFUMzroPROP) ![](/files/-M_SY1x0FFf-Rx4AVNhP) ## Download Download the PowerBI template [here](https://github.com/SMSAgentSoftware/ConfigMgr/raw/master/PowerBI%20Templates/Intune%20Managed%20Devices%20Report.pbit). ## Configure the datasource * Open the PowerBI template * Upon opening, you will be prompted for the **storage account name** and the **container URL** ![](/files/-M_SUxPZ0kGcUjB7q35-) * You can retrieve the **container URL** from the storage account as follows: * In the Azure portal, in the storage account, navigate to the container where the 3 exported CSV reports are * Under **Settings**, click **Properties** * Copy the **URL** and paste it as the **Container URL** in the report * **Important!** Add a **forward-slash** to the **end** of the URL, eg * Click **Load** * You will be prompted for the **Account key**. Retrieve this from the storage account under **Access Keys**. ![](/files/-M_SWAQtLAU1fn3CIPe9) The data should then be imported into the report. # Bonus! Unhealthy MEMCM Clients email report ## Bonus! As another example of how you can export data from MS Graph, below is an Azure automation Runbook you can use to generate an email report of unhealthy co-managed MEMCM clients. Specifically, it will export a list of MEMCM co-managed devices that have synced with Intune in the last 7 days but have not synced with a MEMCM management point in the last 7 days. It will then send you the list as a CSV attachment in an email. ## Unhealthy MEMCM Clients runbook ### Requirements * Your automation account must be using a managed identity * The managed identity must be granted the appropriate permissions to Microsoft Graph as described in this guide * Outlook 365 with direct send or smtp relay configured ``` ################################################################################ ## Azure automation runbook PowerShell script to export a list of unhealthy ## ## MEMCM clients from Microsoft Intune / Endpoint Manager and send it the CSV ## ## report as an email attachment. ## ################################################################################ # Set some variables $ProgressPreference = 'SilentlyContinue' $EmailParams = @{ To = 'recipient@contoso.com' From = 'azureautomation@contoso.onmicrosoft.com' Smtpserver = 'contoso-com.mail.protection.outlook.com' Port = 25 } # Obtain an access token for MS Graph as a Managed Identity $url = $env:IDENTITY_ENDPOINT $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" $headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) $headers.Add("Metadata", "True") $body = @{resource='https://graph.microsoft.com/' } $accessToken = (Invoke-RestMethod $url -Method 'POST' -Headers $headers -ContentType 'application/x-www-form-urlencoded' -Body $body ).access_token $authHeader = @{ 'Authorization' = "Bearer $accessToken" } # Download data from MS Graph $URI = "https://graph.microsoft.com/beta/deviceManagement/manageddevices?`$filter=StartsWith(operatingSystem,'Windows')&`$select=deviceName,enrolledDateTime,lastSyncDateTime,managementAgent,deviceEnrollmentType,userPrincipalName,model,serialNumber,userDisplayName,configurationManagerClientEnabledFeatures,configurationManagerClientHealthState" $Response = Invoke-WebRequest -Uri $URI -Method Get -Headers $authHeader -UseBasicParsing $JsonResponse = $Response.Content | ConvertFrom-Json $DeviceData = $JsonResponse.value If ($JsonResponse.'@odata.nextLink') { do { $URI = $JsonResponse.'@odata.nextLink' $Response = Invoke-WebRequest -Uri $URI -Method Get -Headers $authHeader -UseBasicParsing $JsonResponse = $Response.Content | ConvertFrom-Json $DeviceData += $JsonResponse.value } until ($null -eq $JsonResponse.'@odata.nextLink') } # Organise the data as we want it displayed $Devices = New-Object System.Collections.ArrayList foreach ($item in $DeviceData) { try { [void]$Devices.Add( [PSCustomObject]@{ deviceName = $item.deviceName enrolledDateTime = $item.enrolledDateTime daysEnrolled = [math]::Round(((Get-Date) - ($item.enrolledDateTime | Get-Date -ErrorAction SilentlyContinue)).TotalDays,0) lastSyncDateTime = $item.lastSyncDateTime daysSinceLastSync = [math]::Round(((Get-Date) - ($item.lastSyncDateTime | Get-Date -ErrorAction SilentlyContinue)).TotalDays,0) managementAgent = $item.managementAgent deviceEnrollmentType = $item.deviceEnrollmentType userPrincipalName = $item.userPrincipalName model = $item.model serialNumber = $item.serialNumber userDisplayName = $item.userDisplayName memcmEnabledFeature_inventory = $item.configurationManagerClientEnabledFeatures.inventory memcmEnabledFeature_modernApps = $item.configurationManagerClientEnabledFeatures.modernApps memcmEnabledFeature_resourceAccess = $item.configurationManagerClientEnabledFeatures.resourceAccess memcmEnabledFeature_deviceConfiguration = $item.configurationManagerClientEnabledFeatures.deviceConfiguration memcmEnabledFeature_compliancePolicy = $item.configurationManagerClientEnabledFeatures.compliancePolicy memcmEnabledFeature_windowsUpdateForBusiness = $item.configurationManagerClientEnabledFeatures.windowsUpdateForBusiness memcmEnabledFeature_endpointProtection = $item.configurationManagerClientEnabledFeatures.endpointProtection memcmEnabledFeature_officeApps = $item.configurationManagerClientEnabledFeatures.officeApps memcmClientHealth_state = $item.configurationManagerClientHealthState.state memcmClientHealth_errorCode = $item.configurationManagerClientHealthState.errorCode memcmClientHealth_lastSyncDateTime = $item.configurationManagerClientHealthState.lastSyncDateTime memcmClientHealth_daysSinceLastSync = [math]::Round(((Get-Date) - ($item.configurationManagerClientHealthState.lastSyncDateTime | Get-Date -ErrorAction SilentlyContinue)).TotalDays,0) } ) } catch {} } # Filter and export just the unhealthy clients - those that have talked to Intune but haven't talked to MEMCM in the last 7 days $UnhealthyMEMCMClients = $Devices | where {$_.memcmClientHealth_state -ne 'healthy' -and $_.daysSinceLastSync -le 7 -and $_.memcmClientHealth_daysSinceLastSync -gt 7} $UnhealthyMEMCMClients | export-csv -Path $env:temp\UnhealthyMEMCMClients.csv -Force -NoTypeInformation # Send the email Send-MailMessage @EmailParams -Subject "[Azure Automation] Unhealthy MEMCM Clients in Intune ($($UnhealthyMEMCMClients.Count))" -Attachments "$env:temp\UnhealthyMEMCMClients.csv" ``` # Intune Assignments Report The **Intune Assignments Report** is a Power BI report intended to give you a single pane of glass into your assigned items in Microsoft Intune. It uses Azure automation to extract the data from Microsoft Graph and export it in CSV format to an Azure storage account. The CSV file is the data source for the Power BI report. The report can help you identify which Entra groups are being used in your assignments, how many assigned items you have and their types, the use of virtual groups (all users/devices) and any assignment filters in use.
### Supported assignable items Currently the solution supports at least the following assignable items. Most have been tested but not all. If you find any issues or want to add any missing assignable items, please raise an issue on the GitHub repository. * Apps * Compliance policies * Configuration policies * Windows Autopilot deployment profiles * Enrollment configurations * Enrollment profiles * Customization policies * App protection policies * App configuration policies * Policy sets * E-books * S mode supplemental policies * iOS app provisioning profiles * Windows 365 provisioning policies * Windows 365 user settings * Scripts and remediations * Windows Feature Update profiles * Windows Quality Update profiles * Windows Driver Update profiles * eSIM cellular profiles * Security baselines * Endpoint security policies * RBAC Roles * Scope tags * Multi admin approvals * Terms and conditions ### Currently unsupported items * Conditional access policies * Organizational messages * Anything unique to the Intune Suite * Anything unique to Autopatch ### GitHub Repository The files for this solution can be found in the following GitHub repository: # Create / configure an Azure automation account ## Create an Azure automation account If you don't have one already, create an [Automation account](https://docs.microsoft.com/en-us/azure/automation/automation-create-standalone-account) in Azure. ## Enable a Managed Identity In the Azure portal, locate your automation account. Under **Identity**, enable a **System assigned** identity. The identity will bear the same name as your automation account and will be visible as a service principal in Azure Enterprise applications. ![](/files/-M_RFBTlnLlW0a9uBNfX) You do not need to assign a role in Azure to this identity. # Grant API permissions Here we will grant Graph API permissions to the managed identity of your automation account so it can access data from MS Graph. ## Managed identity We need to grant API permissions to the service principal object in Azure. For a managed identity, this should be done with PowerShell at the time of writing. Run the following PowerShell code to grant the required API permissions if they are not present already. You need the **Microsoft Graph PowerShell SDK** installed and the **Global administrator** or **Application administrator** role. Set the following variables in the script: * **TenantID**. This is the tenant ID for your tenant. * **EnterpriseAppName**. This is the display name of your Azure automation account and its service principal. {% hint style="info" %} Note these are **application** permissions not **delegated** permissions {% endhint %} ```powershell # Tenant Id $TenantId = "" # List of permission names $RolesToAssign = @( "DeviceManagementApps.Read.All" "DeviceManagementConfiguration.Read.All" "DeviceManagementServiceConfig.Read.All" "CloudPC.Read.All" "DeviceManagementRBAC.Read.All" "GroupMember.Read.All" "DeviceManagementScripts.Read.All" ) # DisplayName of the Enterprise App you are assigning permissions to $EnterpriseAppName = "" # Connect to Graph Import-Module Microsoft.Graph.Applications Connect-Graph -TenantId $TenantId -NoWelcome # Get the service principals $GraphApp = Get-MgServicePrincipal -Filter "AppId eq '00000003-0000-0000-c000-000000000000'" # Microsoft Graph $EnterpriseApp = Get-MgServicePrincipal -Filter "DisplayName eq '$EnterpriseAppName'" # Assign the roles foreach ($Role in $RolesToAssign) { $Role = $GraphApp.AppRoles | Where-Object { $_.Value -eq $Role } $params = @{ principalId = $EnterpriseApp.Id resourceId = $GraphApp.Id appRoleId = $Role.Id } New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $EnterpriseApp.Id -BodyParameter $params } ``` Once granted, you will find these permissions listed against the **Enterprise application** for your managed identity in the **Permissions** blade, for example:
# Create / configure an Azure storage account Here we will create and configure a storage account in Azure granting the automation account the necessary permissions and creating a container for the report data. ## Create a storage account If you don't have one already create a [storage account](https://docs.microsoft.com/en-us/azure/storage/common/storage-account-create?tabs=azure-portal) in Azure. ## Create a container Under **Containers**, create a container which we will use to store the data files exported from MS Graph. I've called mine **intune-assignments**. Set the **Public access level** to **private**.
## Create a custom role Here we will create a custom role at the storage account level which has just the permissions we need to upload data to the container. We will assign this role to the managed identity. * In the storage account, top-level, open the **Access Control (IAM)** pane * On the **Roles** tab, locate the role **Storage Blob Data Contributor** * Click the 3 dots on the right of the role and choose **Clone** * Give the role a name. I've used **Storage Account Reader and Blob Contributor** ![](/files/-M_ROpYgyDqg---0R4g1) * On the **Permissions** tab, add or remove the cloned permissions as required. I've added two permissions that are required by this solution and removed a couple that aren't. All permissions are found under **Microsoft.Storage**. * Add **Microsoft.Storage/storageAccounts/read** * Add **Microsoft.Storage/storageAccounts/listkeys/action** ![](/files/-M_RP_ec2Svw0oywmCNC) * On the **Assignable scopes** tab, leave the default scope which should be the storage account itself * Click **Review + create** ## Assign the role Back in the **Access Control (IAM)** pane: * Click **Add role assignment** under **Grant access to this resource** * Locate the role you just created, select it and click **Next** * Against **Assign access to**, select **Managed Identity**. Click **Select members** * Locate for the managed identity under **Automation Account** and select it * Click **Review + assign** # Create an Azure automation runbook Here we will create a runbook in the automation account that will retrieve data from Microsoft Graph using the REST API and export the data to the Azure storage account. ## PowerShell Modules A couple of PowerShell modules are needed. If you are using the latest PowerShell runtime environment (7.2 at the time of writing), these modules should already be present and there should be no reason to create your own runtime environment. If you are using your own runtime environment, make sure the following modules are present and up-to-date. * **Az.Accounts** * **Az.Storage** ## Prepare the Runbook Download the automation runbook script from here: In the **Variables** section near the top of the script, set values for the following variables: * **ResourceGroup**. The name of the Resource group that hosts the Azure storage account. * **StorageAccount**. The name of the Storage account. * **Container**. The name of the container you created. ## Create a Runbook * Click on the **Runbooks** pane and choose **Create a runbook.** * Choose **Browse for file** and select the runbook script you downloaded. * Select the latest **runtime environment** (eg 7.2 at time of writing). * Click **Review + Create**. * When ready, **Publish** the runbook.
## Schedule the Runbook Schedule the Runbook to execute at regular intervals to keep the data updated, every 3 hours for example. * In the Azure portal, in the automation account, open the Runbook you created * Click **Link to schedule** * In the **Schedule** section, select an existing schedule or create a new one ![](/files/-M_SHJw26sAMuYhaSEaI) # Create a Power BI report Here we will look at how to connect Power BI to our storage account and import our data {% hint style="info" %} Make sure you have run the automation runbook at least once to export some data before configuring the Power BI report. {% endhint %} ## Download the Power BI template Download the Power BI template from here: ## Connect to Azure Blob storage * Open the template in Power BI desktop. * When you do, you'll be prompted for the S**torage account name** and the **Container name**. Enter the values from the storage account you created.
* If this is the first time you are connecting Power BI to the storage account, you may be prompted for the **Account key.** You'll find this in the storage account under **Access Keys**. ![](/files/-M_SKTX2L8Ge0L5zOxsw) {% hint style="warning" %} If you regenerate your storage account keys, you will need to update your Power BI report. For greater security, consider using a Service Principal instead when you publish your report. {% endhint %} * Click **Connect** * Your report data should now load in. ## Publish the report Once your report has been created, you can publish it to the Power BI service by clicking **Publish** on the **Home** menu. Once published, you can schedule the data to refresh periodically to keep the report up to date. Do not refresh more frequently than the schedule of your automation runbook. You do not need to use a data gateway because the storage account data source is also a cloud service. # Change log A history of changes
DateDescription
2025-10-06Added 'DeviceManagementScripts.Read.All' to list of Graph permissions required.
2024-04-12Added enrollment notification assignments
2024-04-04Initial public release
# Patch My PC Report The guide describes how to setup a Patch My PC Power BI report for Intune using Azure automation to regularly export data from Intune into Azure blob storage as the data source for the report. {% hint style="warning" %} ## The Patch My PC *Detailed* report has now been retired due to persistent issues with the Microsoft Graph API. The Patch My PC *Overview* report can continue to be used. {% endhint %} ## Overview In this guide we will create a Power BI report from a template to report on apps in Intune that are managed by the [Patch My PC](https://patchmypc.com/) publishing service. Patch My PC is a great tool for keeping 3rd party applications up-to-date. The report is only for applications in **Intune (**not ConfigMgr), and apps, updates and custom apps are supported. ## Azure Services As Power BI does not[ natively support connections to Microsoft Graph](https://docs.microsoft.com/en-us/power-query/connecting-to-graph), we will use Azure automation to regularly export data from Intune using the Microsoft Graph REST APIs and save this as CSV files in Azure blob storage. Power BI supports the use of files in an Azure storage account as a data source, and the Power BI service also supports scheduled refresh on this data source, so your report can automatically stay up to date. ## High-Level Process {% hint style="info" %} Although we create the required Azure resources in this guide, you may wish to use resources you already have in Azure for this. {% endhint %} 1. Create / configure an Azure automation account 2. Grant API permissions to the managed identity 3. Create / configure an Azure storage account 4. Create the Automation runbook 5. Create the Power BI report ## Requirements * A working Patch My PC for Intune installation with apps / updates / custom apps created and assigned * Some familiarity with Azure administration * Some familiarity creating Power BI reports * PowerShell knowledge * Appropriate permissions to create / configure the Azure resources # A look at the Power BI report Here we'll go through each page of the Power BI report ## Status Overview
This page contains an overview of installation status for all your assigned apps / updates / custom apps. The success % gauges are calculated from the total number of successful installations against the total number of applicable devices. Here you can see at a glance the overall health of your Patch My PC app deployments. This data is summarized by Microsoft Intune and presented as is. ## Tree Map
The Tree Map page shows at a glance which apps have the most number of successful installs and can be filtered by app type, ie app, update or custom app. ## Apps List
The Apps List page displays all your deployed apps managed by Patch My PC, including the Publisher, number of assignments, when the app was created, the app type and when it was last modified. You can filter by app type, app name or last modified date. ## Install Status
The Install Status page show the number of devices or users in each installation state (eg Installed, Pending, Failed, Not installed or Not applicable) per app, as well as the overall success percentage per app. You can filter by app type or app name. **User** data is provided as well as **device** data. # Change log | 2025-07-17 | Retired the **Detailed** report due to too many issues with the Microsoft Graph API. | | ---------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 2024-04-26 |

Major rewrite.
- Custom apps are now supported
- Redesigned the report visually
- Two separate reports are now available - the Overview report and the Detailed report.
- A number of code improvements in the runbook
- Moved the solution resources into its own GitHub repository

| | 2024-04-17 | Bug fix on the way app data is loaded into an array in the runbook. | | 2024-02-19 | Cast dates into "s" (sortable) format to provide consistency and correct interpretation of dates by the PowerBI report. Increased the timeout for 429 http status codes from 5 minutes to 10 minutes. | | 2024-02-01 | Removed the "Write-Output" command from the Get-PmpAppsList function because the results are sent to a variable and any additional output would break the list of apps returned. | | 2023-11-17 | Added retry logic with a 5-minute timeout on the Get-DeviceInstallStatusReport function in the runbook, to handle non-success return codes. | | 2023-02-03 | Updated both the runbook and the Power BI template to handle the scenario where they may be either no app or no updates deployed. | | 2023-01-18 | Added code to the Azure automation runbook to fix a bug where the PowerBI report would error if there were no data returned for the statusoverview or deviceinstallstatus reports. | | 2022-07-08 | Updated the Power BI template with a new-look dark theme. Added an "Application counts" chart on the installs report pages, as well as device and install counts, and an "install state" filter. | | 2022-07-08 | Updated the Remove-MSGraphExportJobDuplicates function with some more efficient code that (in testing) is around 30% quicker. Thanks to [Yannick Van Landeghem](https://twitter.com/NoahProd) for the tip on this! | | 2022-05-27 | Major rewrite to move away from the reports/exportJobs Graph endpoint due to much tighter limits put in place by MS for frequent requests for jobs. Switched back to the reports/getDeviceInstallStatusReport endpoint again and used looping to overcome the current 50 item limit per request. | | 2022-05-12 | Added a filter to the Request-ExportJobs function in the automation runbook to only include install states that are NOT 'not applicable'. This significantly reduces the overall processing time of the runbook where apps are targeted to a large number of devices for which the app is not applicable to that device. | | 2021-09-27 | Added some retry logic to the Request-ExportJobs function in the runbook to handle when Graph returns a bad request response. This is in response to changes made to service limits in MS Graph around 2021-09-24. | | 2021-08-23 | Switched from using the deviceManagement/reports/getDeviceInstallStatusReport Graph endpoint to exportJobs for the device installation status data due to the limitation of the endpoint only returning 50 results per call. | | 2021-08-23 | Added a function in the runbook to create a unique result set for the device installation status data by including only the most recent entry per device | | 2021-08-23 | Released version 1.1 of the Power BI report template removing the 'Not applicable' status from the donut charts for a better view. | | 2021-08-17 | Fixed an issue with the automation runbook where it would fail if the number of apps or updates in an export job was a multiple of 20. | | 2021-07-07 | Updated the runbook script to include better error handling and some retry logic in case the http 504 error (gateway timeout) is returned. | | 2021-07-01 | Initial release | # Video guides Watch the setup guides on YouTube! {% embed url="" %} {% embed url="" %} {% embed url="" %} {% embed url="" %} # Things to know Some things to be aware of with the report * Only apps **with assignments** will be reported on. Apps created by Patch My PC without assignments will not appear in the report. * The report only show apps in Intune **created by the Patch My PC publishing service**. These have a unique id stamped in the **notes** property of the app. * If using **Patch My PC Cloud**, a **Patch My PC category** (eg app, update) must be added to the app deployment since these are not added by default as with the publishing service. * We use the **beta** version of the Microsoft Graph REST API for some of the data. This version is not general availability and is subject to change (even though we all know Microsoft use it in the Intune portal 😉). * Success data may not be meaningful in the report unless apps are targeted with **required** assignments. * The Graph export filters device install status data to include only devices that are reporting a status OTHER than "not applicable". This speeds up the Graph requests, but also means only devices that are applicable for an app will appear in the report. # Create / configure an Azure automation account ## Create an Azure automation account If you don't have one already, create an [Automation account](https://docs.microsoft.com/en-us/azure/automation/automation-create-standalone-account) in Azure. ## Enable a Managed Identity In the Azure portal, locate your automation account. Under **Identity**, enable a **System assigned** identity. The identity will bear the same name as your automation account and will be visible as a service principal in Azure Enterprise applications. ![](/files/-M_RFBTlnLlW0a9uBNfX) You do not need to assign a role in Azure to this identity. # Grant API permissions Here we will grant Graph API permissions to the managed identity so it can access data from MS Graph. ## Managed identity We need to grant API permissions to the service principal object in Azure. For a managed identity, this can only be done with PowerShell at the time of writing. Run the following PowerShell code to grant API permissions. You need the **Microsoft Graph PowerShell SDK** and an account with **Global administrator** or **Application administrator** permissions. Set the following variables in the script: * **TenantID**. This is the tenant ID for your tenant. * **EnterpriseAppName**. The display name of your managed identity, which is the same as the name of your automation account. * **RolesToAssign**. Here you can list which permissions you want to grant. You can reference the [MS Docs](https://docs.microsoft.com/en-us/graph/permissions-reference) to find the permissions you need. As a minimum, the following permissions are needed for this report: * DeviceManagementManagedDevices.Read.All * DeviceManagementConfiguration.Read.All * DeviceManagementApps.Read.All {% hint style="info" %} Note these are **application** permissions not **delegated** permissions {% endhint %} ```powershell # Tenant Id $TenantId = "" # List of permission names $RolesToAssign = @( "DeviceManagementManagedDevices.Read.Alll" "DeviceManagementConfiguration.Read.All" "DeviceManagementApps.Read.All" ) # DisplayName of the Managed Identity (Enterprise app, Service principal) you are assigning permissions to $EnterpriseAppName = "" # Connect to Graph Import-Module Microsoft.Graph.Applications Connect-Graph -TenantId $TenantId -NoWelcome # Get the service principals $GraphApp = Get-MgServicePrincipal -Filter "AppId eq '00000003-0000-0000-c000-000000000000'" # Microsoft Graph $EnterpriseApp = Get-MgServicePrincipal -Filter "DisplayName eq '$EnterpriseAppName'" # Assign the roles foreach ($Role in $RolesToAssign) { $Role = $GraphApp.AppRoles | Where-Object { $_.Value -eq $Role } $params = @{ principalId = $EnterpriseApp.Id resourceId = $GraphApp.Id appRoleId = $Role.Id } New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $EnterpriseApp.Id -BodyParameter $params } ``` Once granted, you will find these permissions listed against the **Enterprise application** for your managed identity in the **Permissions** pane. ![](/files/-Mdax5aEzdXbftErsmQ4) # Create / configure an Azure storage account Here we will create and configure a storage account in Azure granting the automation account the necessary permissions and creating a container for the report data. ## Create a storage account If you don't have one already create a [storage account](https://docs.microsoft.com/en-us/azure/storage/common/storage-account-create?tabs=azure-portal) in Azure. ## Create a container Under **Containers**, create a container which we will use to store the data files exported from MS Graph. I've called mine **patchmypc-powerbi**. Set the **Public access level** to **private**. ![](/files/-MdaxyIluuXQ5PPslEJH) ## Create a custom role Here we will create a custom role at the storage account level which has just the permissions we need to upload data to the container. We will assign this role to the managed identity or run as account. * In the storage account, top-level, open the **Access Control (IAM)** pane * On the **Roles** tab, locate the role **Storage Blob Data Contributor** * Click the 3 dots on the right of the role and choose **Clone** * Give the role a name. I've used **Storage Account Reader and Blob Contributor** ![](/files/-M_ROpYgyDqg---0R4g1) * On the **Permissions** tab, add or remove the cloned permissions as required. I've added two permissions that are required by this solution and removed a couple that aren't. All permissions are found under **Microsoft.Storage**. * Add **Microsoft.Storage/storageAccounts/read** * Add **Microsoft.Storage/storageAccounts/listkeys/action** ![](/files/-M_RP_ec2Svw0oywmCNC) * On the **Assignable scopes** tab, leave the default scope which should be the storage account itself * Click **Review + create** ## Assign the role Back in the **Access Control (IAM)** pane: * Click **Add role assignment** under **Grant access to this resource** * Locate the role you just created, select it and click **Next** * Select **Managed Identity** and click **Select members** * Locate the Managed identity for your automation account and select it * Click **Review + assign** a couple times to complete**.** # Create an Azure automation runbook Here we will create a runbook in the automation account that will retrieve data from Microsoft Graph using the REST API and export the data to the Azure storage account. {% hint style="info" %} This process uses the [Runtime Environment experience](https://learn.microsoft.com/en-us/azure/automation/runtime-environment-overview). {% endhint %} {% hint style="info" %} The **Az.Accounts** and **Az.Storage** modules are required to execute this runbook. If you wish to use a **System-generated Runtime environment**, you should be fine as the Az modules are already included. If you use your own runtime environment, make sure the Az modules are included. {% endhint %} {% hint style="warning" %} It is highly recommend to use the latest PowerShell Core runtime version to execute the runbook (7.2 at the time of writing) {% endhint %} ## Download the Runbook script Download the [Export-MSGraphPatchMyPCData.ps1](https://github.com/SMSAgentSoftware/PatchMyPCReport/blob/main/Export-MSGraphPatchMyPCData.ps1) runbook from my GitHub repo. ## Configure the script In the runbook script, set the **first four** variables in the **Variables** script region at the top. **$ReportType**. Only the '**Overview**' report is now available. **$ResourceGroup**. Enter the name of the Azure resource group containing your Azure storage account. **$StorageAccount**. Enter the name of the Azure storage account. **$Container**. Enter the name of the Container in your storage account where you'll be exporting the data. ## Create the Runbook * Click on the **Runbooks** pane and choose **Create a runbook** * Choose **Browse for file** and locate the runbook script file * Select a **Runtime Environment** * Enter a **Description** if desired, and click **Review + Create**, then **Create** * In the **Edit PowerShell Runbook** window**, Publish** the runbook ## Schedule the Runbook Schedule the Runbook to execute at regular intervals to keep the data updated. * In the Azure portal, in the automation account, open the Runbook you created * Click **Link to schedule** * In the **Schedule** section, select an existing schedule or create a new one ![](/files/-Mdb-dKf7HOTU8cF6bcx) # Create the Power BI report Here we will create a Power BI report from the template ## Download Download the Power BI template for the **Overview** report [here](https://github.com/SMSAgentSoftware/PatchMyPCReport/blob/main/Patch%20My%20PC%20Overview%20Report.pbit). ## Configure the datasource * Open the Power BI template with Power BI desktop * Upon opening, you will be prompted for the **storage account name** and the **container name**
* Enter the **name** of the Azure storage account where you are saving the CSV exports * Make sure there are no spaces surrounding the storage account name * Enter the **name** of the container in the Azure storage account where the CSV files are exported to * Click **Load** * If this is the first time you are connecting to the storage account with Power BI, you will be prompted for the **Account key**. Retrieve this from the storage account under **Access Keys**. ![](/files/-M_SWAQtLAU1fn3CIPe9) Your data will then be imported into the report. {% hint style="warning" %} If you regenerate your storage account keys, you will need to update your Power BI report. {% endhint %} ## Publish the report Once your report has been created, you can publish it to the Power BI service by clicking **Publish** on the **Home** menu. Once published, you can schedule the data to refresh periodically to keep the report up to date. Do not refresh more frequently than the schedule of your automation runbook and don't configure the report to refresh at the same time - try 30 minutes later for example. There is no need to use a data gateway as the storage account data source is also a cloud service. {% hint style="info" %} When publishing the report to the Power BI service, for better security you may wish to configure a service principal to connect to the Azure storage account rather than using the account key. {% endhint %} # Feedback If you have any issues or suggestions please feel free to create an issue on the [GitHub repository](https://github.com/SMSAgentSoftware/PatchMyPCReport/issues) :) # Windows 11 Hardware Readiness Report ## Overview This Power BI report is more or less a mirror of what is available in Endpoint Analytics and is built from the same data. It allows you to see which devices in Intune are ready for Windows 11 and which are not and the reasons why. ![](/files/-MlJtjyh8w1avHCaqubb) ![](/files/-MlJtnddkmCmB9vYDaGb) The report uses the same process as the other reports in this site, ie an Azure automation account runbook exports the data from Microsoft Graph and sends it in CSV format to an Azure storage account container. The Power BI report then connects to the storage account and uses the CSV file as the source for the report. Scheduling the runbook to run regularly and scheduling a refresh on the Power BI report will keep the data up-to-date. ## Configure Azure Resources If you've already created any of my other reports in this site or used the same process, ie Azure automation runbook > Azure blob storage, you'll already have the Azure resources in place to run this report. Otherwise you'll need to create them using the guides below. {% content-ref url="/pages/-MhPv1XVi754Ozf78Jtx" %} [Create / configure an Azure automation account](/microsoft-endpoint-manager-reporting/automating-data-exports-from-microsoft-graph/azure-automation-account/create-configure-an-azure-automation-account) {% endcontent-ref %} {% content-ref url="/pages/-MhPvnhW1AGZDuZGrlKZ" %} [Grant API permissions](/microsoft-endpoint-manager-reporting/automating-data-exports-from-microsoft-graph/azure-automation-account/grant-api-permissions) {% endcontent-ref %} {% content-ref url="/pages/-Mi25918CCwoBsjrzRr5" %} [Azure Storage account](/microsoft-endpoint-manager-reporting/automating-data-exports-from-microsoft-graph/azure-storage-account) {% endcontent-ref %} For **API permissions** for the **managed identity of the automation account**, the following minimum are needed for this report: * DeviceManagementConfiguration.Read.All * DeviceManagementManagedDevices.Read.All * DeviceManagementManagedDevices.ReadWrite.All Make sure that the **Az.Accounts** and **Az.Storage** modules have been imported into the automation account. ## Azure automation runbook Download the runbook script below and create a runbook in your automation account. Set a recurring schedule for the runbook as needed, eg once a day. {% embed url="" %} ### Parameters You'll need to set the following parameters in the top of the runbook script: * **$ResourceGroup** - the name of the resource group containing the storage account you are using * **$StorageAccount** - the name of the storage account that will contain the exported data * **$Container** - the name of the container in the storage account ## Power BI Template Once you have executed the runbook and got data in your storage account, download the open the Power BI template below. {% embed url="" %} Upon opening, you'll be prompted for your **storage account name** and the **container name**: ![](/files/-MlK-yDwu-l-vHr3xNOY) If you haven't connected to the storage account with Power BI before, you'll be prompted for the **account key**, which you can find under **Access keys** in the storage account. ![](/files/-MlK01QEyaYV6YcTIJLj) Once the data has loaded in, you can publish your report to the Power BI service and set a refresh schedule. Make sure there is an offset between when the automation runbook is scheduled and when the Power BI report is scheduled to refresh. ## Feedback Feel free to send any feedback here. {% embed url="" %} # Gathering Custom Inventory with Intune Currently Microsoft Intune does not allow you customize the data it inventories for devices - there is a limited amount of inventory collected out of the box but there is no supported process to add to or change what is inventoried. If you are used to using MEMCM to gather hardware inventory from your devices on a schedule and customize what is gathered, you may be disappointed that there is no equivalent process in Intune. That doesn't mean it's impossible, however, and there are some creative ways to do this, but most of them have some limitations. A key mechanism to use is [Proactive remediations](https://docs.microsoft.com/en-us/mem/analytics/proactive-remediations). Yes, the name clearly indicates that is was not intended for gathering inventory data but rather remediating client-side issues, but in any case it's an incredibly useful mechanism that sort of functions like a centrally-managed task scheduler and opens many possibilities for creative use. For example, the guys over at [MSEndpointMgr.com](https://msendpointmgr.com/2021/04/12/enhance-intune-inventory-data-with-proactive-remediations-and-log-analytics/) wrote a nice blog about how you can use PR to gather inventory data directly into a log analytics workspace, although you do need to add your workspace ID and access key to the script which will get cached on the local machine. Another way to do this is to use PR to report the inventory data in the script output which essentially adds the data to Microsoft Graph and allows you to use either the MEM portal or the Graph API to retrieve it. A limitation here is that currently the output must be [2048 characters or less](https://docs.microsoft.com/en-us/mem/analytics/proactive-remediations#bkmk_requirements), so for large inventory gathering you'd need to break it up into multiple scripts. In this guide, I'll show you how to gather inventory in this way by using an Azure automation runbook to export the data from Microsoft Graph on a schedule and save it either to an Azure storage account or a Log Analytics workspace, where it can serve as a data source for Power BI reports, or Azure Monitor workbooks. # Set up the Azure Resources If you haven't already done so, you'll need to create and/or configure some Azure resources to enable you to automate exports from Microsoft Graph. You'll need to set up the following resources and this is documented elsewhere in this site. You can, of course, use existing resources you have in Azure - you just need to make sure they are configured for use with this solution, such as enabling a managed identity on the automation account, granting Graph API permissions to it and creating a custom role for the Azure storage account. ## Azure automation account The automation account is used to execute runbooks on a schedule. These runbooks will export data from Microsoft Graph and save them to Azure storage and/or send them to a Log Analytics workspace. For the Azure automation runbook, you don't need to create a runbook at this stage but you will need to import the required PowerShell modules into the automation account. {% content-ref url="/pages/-MhPv1XVi754Ozf78Jtx" %} [Create / configure an Azure automation account](/microsoft-endpoint-manager-reporting/automating-data-exports-from-microsoft-graph/azure-automation-account/create-configure-an-azure-automation-account) {% endcontent-ref %} {% content-ref url="/pages/-MhPvnhW1AGZDuZGrlKZ" %} [Grant API permissions](/microsoft-endpoint-manager-reporting/automating-data-exports-from-microsoft-graph/azure-automation-account/grant-api-permissions) {% endcontent-ref %} {% content-ref url="/pages/-MhPx9jqExbhbeFncHt2" %} [Create an Azure automation runbook](/microsoft-endpoint-manager-reporting/automating-data-exports-from-microsoft-graph/azure-automation-account/create-an-azure-automation-runbook) {% endcontent-ref %} ## Azure storage account If you want to export data from Microsoft Graph to an Azure storage account, reference the following guide. {% content-ref url="/pages/-Mi25918CCwoBsjrzRr5" %} [Azure Storage account](/microsoft-endpoint-manager-reporting/automating-data-exports-from-microsoft-graph/azure-storage-account) {% endcontent-ref %} # Create a Proactive remediations script package In this simple example, we'll create a script package in Proactive remediations in the MEM portal which will report on whether a device is pending a restart from software updates. ## Create the script package In the MEM portal, navigate to Reports > Endpoint Analytics > Proactive remediations Click **Create script package** and give it a name and description ![](/files/-Mj_A8DcZuTw97u3SeAT) Download the following script, then on the **Settings** page in the script package upload it as the **Detection script file**. There is no need to add a remediation script. Run the script in 64-bit PowerShell. {% hint style="success" %} Have a read of the script so you understand how it works. You can use it as a starting point for your own scripts. The key things are that the inventoried data is outputted as key-value pairs in JSON format and that the output is not longer than the permitted length. {% endhint %} {% embed url="" %} ![](/files/-Mj_AJji0JAcONVRxFoK) Add a scope tag if you need to. ![](/files/-Mj_BCAu6SmUt5-g3jvI) On the **Assignments** page add an **assignment** and set the **schedule**. {% hint style="info" %} Don't run the script more frequently than necessary. If the data you are inventorying doesn't change often, don't inventory it often. Also consider how often you will be exporting the data - if that's once per day, for example, a daily schedule on the PR should suffice. {% endhint %} ![](/files/-Mj_BGIkD46hYRbbPaFI) Click **Create**. ![](/files/-Mj_BK3NGfXWGNY9O_y0) ### View the output Once some of your targeted devices have run the script, you can view the output in the MEM portal and verify that it's good. In Proactive remediations, click on the script package you created and view the **Device status** report. ![](/files/-Mj_DHYFvU4IOuycdNyi) To view the output, click **Columns** and select at least the **Pre-remediation detection output** column. ![](/files/-Mj_DYAjYypyz802Sy5v) In that column, click **Review** to see the output. ![](/files/-Mj_DkaUTbeR15nEA_Bw) ![](/files/-Mj_DuNm45pBRC4obBTD) Note that the output is in JSON format - this makes it easy for the automation runbook to use the data. # Create a runbook Create an Azure automation runbook that will export your inventory data from Microsoft Graph. ## Get your Proactive remediations script package GUID For the automation runbook, we'll need to know the GUID of the script package we just created. An easy way to do this is to use the Developer Tools in Microsoft Edge. In the MEM portal, navigate to Proactive remediations, but before you click on the script package you created, press F12 to open the DevTools. Click on the **Network** tab at the top. Then click to open the script package and you'll see the URL/s that the portal is calling. You'll see one with the name **getRemediationHistory**, for example - right-click on that and copy the link address. ![](/files/-Mj_HDNRaJYlze9BZCpj) ![](/files/-Mj_HpId4Zc5MUM948qp) You can then paste it into notepad, for example, and you'll have the GUID that you need in the URL. Make a note of this GUID. ![](/files/-Mj_I1MeKDFA3tsLkKsS) ## Create a runbook (sending to Azure storage account) Download the following runbooks script. {% embed url="" %} In your Azure automation account, navigate to **Runbooks** and Import a runbook. Browse to and import the runbook script, select the Runbook type **PowerShell** and add a description. ![](/files/-MjeOyC8P9JKtyCdg08u) In the runbook script you'll need to add some parameter values at the top: * **$ResourceGroup.** This is the name of the resource group that your storage account belongs to. * **$StorageAccount**. This is the name of your storage account. * **$Container**. This is the name of the container in the storage account where you'll export your data to (must exist) * **$ProactiveRemediationsScriptGUID**. This is the GUID for your PR script package that you obtained earlier. The rest of the script can remain unchanged. ![](/files/-MjeO0UhCHsRUZ6QS8Ld) **Publish** the runbook, then start it. After execution is complete, you should see the CSV file created in your storage account. ![](/files/-MjeO7Tve-8izVI0pPiF) On the **Edit** tab of the blob, you can see your exported data. ![](/files/-MjeOFROJPY2Gj6WuCzC) For completeness, here's the same data imported into Power BI. ![](/files/-MjeOKXkmljqBpQnEpBL) ## Create a runbook (sending to Log Analytics workspace) First you'll need to add the **WorkspaceID** and **PrimaryKey** from the Log Analytics workspace as encrypted variables in the Azure automation account. In your LA workspace, go to **Agents management** and copy the Workspace ID and Primary key. ![](/files/-MjeUb2jMvKc7-lsSk-m) In the automation account, go to **Variables** and **Add a variable**. Create the following variables as strings and paste in the copied values. Select the **Encryption** option. * **WorkspaceID** * **PrimaryKey** ![](/files/-MjeVgoaaj8NDkOElYZn) Now download the following runbook script. {% embed url="" %} In your Azure automation account, navigate to **Runbooks** and Import a runbook. Browse to and import the runbook script, select the Runbook type **PowerShell** and add a description. ![](/files/-MjeOyC8P9JKtyCdg08u) In the runbook script you'll need to add some parameter values at the top: * **$ProactiveRemediationsScriptGUID**. This is the GUID for your PR script package that you obtained earlier. * **$LogName**. This is the name of the custom log you'll send data to in the LA workspace. It doesn't need to already exist. As long as your automation account variables are named the same as in the script, you don't need to change those. ![](/files/-MjeZ19vGojeG-bHYJd2) The rest of the script can remain unchanged. **Publish** the runbook, then start it. After execution is complete, you can query the data in the custom log from the LA workspace. Bear in mind that for the first run it can take some minutes before the data is ingested and available in the workspace. The custom log name will be appended with "\_CL" and only non-null values will be ingested and available to report on. ![](/files/-Mje_VIk889lplbn2jm3) ## Schedule the runbook Schedule the Runbook to execute at the desired intervals to keep the data updated. * In the Azure portal, in the automation account, open the Runbook you created * Click **Link to schedule** * In the **Schedule** section, select an existing schedule or create a new one ![](/files/-Mdb-dKf7HOTU8cF6bcx) # Get-AzSubscriptionActivityLog Gets Activity Log events from an Azure subscription with filtering options. ## Syntax ```powershell Get-AzSubscriptionActivityLog [-TenantId ] [-SubscriptionId ] [-TimespanHours ] [-IncludeProperties ] [-IncludeListAndGetOperations ] [-IdentityType ] [-Level ] [-Category ] [-Caller ] [-ResourceGroupName ] [-ResourceProviderName ] [-ResourceIdMatch ] [-ResourceType ] [-OperationName ] [-Status ] ``` ## Description Get-AzSubscriptionActivityLog retrieves events from the Activity Log in an Azure subscription using the REST API. Caller identities are translated to their friendly names from their GUIDs. Requires the **Az.Accounts** module for authentication in the current context. {% hint style="info" %} Note by default, operations with 'List' or 'Get token' in the name are excluded from the results as these can be numerous. To include them, use the -IncludeListAndGetOperations parameter {% endhint %} ## Permissions Requires at least a **Reader** role in the Azure subscription. Requires **Directory.Read.All** permission in Microsoft Graph. ## Install Install the script from the PowerShell gallery: ```powershell Install-Script Get-AzSubscriptionActivityLog -Force ``` ## Connect to Azure if you haven't already authenticated to Azure, run the **Connect-AzAccount** cmdlet first, eg ```powershell Connect-AzAccount -Subscription "e7b7fedf-90ab-4b0c-913b-a08ccd060d9a" ``` ## Examples ### Example 1 - Get activity log events for the last 12 hours ```powershell Get-AzSubscriptionActivityLog -TenantId 'a84894e7-90hb-40e3-9783-320d0334b3cc' -SubscriptionID 'e7b7fedf-1d1d-4b0c-913b-a08ccd060d9a' -TimespanHours 12 ``` This command gets activity logs from the specified tenant and subscription over the last 12 hours. ### Example 2 - Get activity log events for a specific identity type ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -IdentityType ManagedIdentity ``` This command gets events where the caller type is a managed identity. ### Example 3 - Get activity log events for specific categories ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -Category 'Policy','Security' ``` This command gets events with a category of *Policy* or *Security*. ### Example 4 - Get activity log events for specific callers ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -Caller 'Windows 365','Microsoft.RecoveryServices' ``` This command gets events where the calling identity is either *Windows 365* or *Microsoft.RecoveryServices*. You could also specify the displayname of a managed identity or the UPN of a AAD user. ### Example 5 - Get activity log events with a specific severity ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -Level Error,Warning ``` This command gets events with a severity level of either *Error* or *Warning*. ### Example 6 - Get activity log events for specific resource groups ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -ResourceGroupName 'rg-01','rg-02' ``` This command gets events from the resource groups *rg-01* and *rg-02.* ### Example 7 - Get activity log events for specific resource providers ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -ResourceProviderName 'Microsoft.Network','Microsoft.VirtualMachineImages' ``` This command gets events where the resource provider is either *Microsoft.Network* or *Microsoft.VirtualMachineImages* ### Example 8 - Get activity log events for a specific resource ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -ResourceIdMatch "VM001" ``` This command gets events where the resourceId matches the string *VM001*, which in this case is a virtual machine name. This could be any part of the resourceId name after the resource provider and supports a single string only. ### Example 9 - Get activity log events for a specific resource type ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -ResourceType 'MICROSOFT.RECOVERYSERVICES/vaults','Microsoft.Compute/virtualMachines' ``` This command gets events where the resource type is either *MICROSOFT.RECOVERYSERVICES/vaults* or *Microsoft.Compute/virtualMachines* ### Example 10 - Get activity log events for specific operations ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -OperationName 'Start Virtual Machine','Backup Protected Item' ``` This command gets events where the operation name is either *Start Virtual Machine* or *Backup Protected Item* ### Example 11 - Get activity log events with a specific status ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -Status Failed,Accepted ``` This command gets events with a status of *Failed* or *Accepted*. ### Example 12 - Get activity log events including List and Get token operations ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -IncludeListAndGetOperations ``` This command gets all events including any *List* or *Get token* operations, which are excluded by default as they can be numerous. ### Example 13 - Get activity logs including additional properties ```powershell Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -IncludeProperties ``` This command gets events and includes any additional properties that are attached to the event. ### Example 14 - View the additional properties for an event ```powershell $ActivityLog = Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID -IncludeProperties $ActivityLog[0].properties | Format-List ``` This command retrieves the additional properties for the first event in the array, formatted as a list. ### Example 15 - Group and count events by Operation ```powershell $ActivityLog = Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID $ActivityLog | Group-Object -Property operationName -NoElement | Sort-Object -Property Count -Descending | Format-Table -AutoSize ``` This command groups events by their operationName and displays the counts of each operation with the most numerous first
### Example 16 - Group and count events by category ```powershell $ActivityLog = Get-AzSubscriptionActivityLog -TenantId $TenantId -SubscriptionId $SubscriptionID $ActivityLog | Group-Object -Property category -NoElement | Sort-Object -Property Count -Descending | Format-Table -AutoSize ``` This command groups events by their category and displays the counts of each category with the most numerous first.
## Parameters
-TenantId **Required**. GUID as a String. The tenant Id for the Azure subscription.
-SubscriptionId **Required**. GUID as a String. The Azure subscription Id.
TimespanHours Integer. The number of hours past (from now) to retrieve events. Default is 6.
IncludeProperties Switch. Use this parameter to include the additional properties for the event. Each event may have additional properties that differ depending on the event type.
IncludeListAndGetOperations Switch. By default, operations with "List" or "Get token" in the name are excluded as they can be numerous and aren't always useful. Add this parameter if you wish to include them instead.
IdentityType String\[]. The identity type of the caller. Valid values are *Application, ManagedIdentity, Service,User* or *$null*.
Level String\[]. The event level or severity. Valid values are *Informational, Warning, Error* or *Critical*.
Category String\[]. The event category, for example *Administrative, Security, Policy, Recommendation*
Caller String\[]. The identity that performed the action in the event.
ResourceGroupName String\[]. The name of one or more resource groups to filter on.
ResourceProviderName String\[]. One or more resource providers to filter on, such as *Microsoft.Compute* or *Microsoft.RecoveryServices*
ResourceIdMatch String. Use this to search for a resource by name using a match, for example a VM name or a NIC name.
ResourceType String\[]. One or more resource types to filter on, for example *Microsoft.Network/networkInterfaces* or *Microsoft.HybridCompute/machines/extensions*
OperationName String\[]. The localized values of one or more operation names, for example *"Create or Update Network Interface"* or *"Backup Protected Item"*
Status String\[]. The event status. Valid values are *Accepted, Started, Succeeded, Failed*.
# Automated Azure Table Storage Backups In this guide I'll cover how you can automate regular backups of Azure Storage Tables to CSV format in a Storage Container using an Azure Function. Microsoft currently provides no native capability for backing up Azure table storage. If, therefore, you have any solutions that have a dependency on table storage your solution is at risk. Backing up table data means you can confidently store data in those tables without fear of some important information or configuration being accidentally deleted and unretrievable. This solution provides an Azure function which will backup storage tables on a schedule that you can set, or on-demand. It works within an Azure subscription and across resource groups and multiple storage accounts, backing up table data to a container in the same storage account as the source table/s. It utilises a handy .Net library created by [medienstudio ](https://medienstudio.net/en/)which takes care of converting your table data to CSV format. Point-in-time restores are easily performed using the [Microsoft Azure Storage Explorer](https://azure.microsoft.com/en-us/products/storage/storage-explorer), which provides a GUI experience for importing CSV files into a storage table. The solution is easily deployed using a PowerShell script to create and configure all the required Azure resources. The list of Azure tables you want to backup is itself contained in an Azure table, which is also backed-up by this solution. To add or remove tables in the backup, simply edit the configuration table. The Azure function uses a timer trigger, and you can configure the backup schedule simply by editing the cron expression which is saved to an application setting in the Azure function app. The Azure resources created by this solution are basically free to run as long as you remain within the tier limits. You obviously may incur some additional cost in your storage accounts for storing the backup files, and the cost depends on how many tables and how much data is in the tables that you are backing up, as well as how many backups you want to keep. # Change log A history of changes
DateDescription
2024-10-18Updated solution to .Net 8 isolated worker model
2023-07-24Initial release
# Deploy the solution This section provides instructions for deploying the solution At a high level, the following steps need to be performed: * Run the PowerShell script to create the Azure resources * Set the backup schedule * Add the tables you want to backup to the configuration table * Add role assignments to the storage account/s * Optionally create a Lifecycle management rule for the backup container/s # Create the Azure resources In this step, we'll run the PowerShell script which will create all the Azure resources required by this solution. To create the Azure resources, download the following PowerShell script: {% @github-files/github-code-block url="" %} {% hint style="warning" %} To create all the resources required by this solution, either the **Owner** role or the **Contributor** PLUS **User Access Administrator** roles are required in the Azure subscription. {% endhint %} ### Azure Module requirements The following Az modules are required to run the script: * Az.Accounts * Az.Resources * Az.Storage * Az.OperationalInsights * Az.ApplicationInsights * Az.Functions * Az.WebSites ### Set parameters Set the following parameters at the top of the script: * **Tenant** (tenant Id) * **Subscription** (subscription name) * **Location** (Azure region name)
### Run the script A successful execution of the script will report all the resources being created. Make note of the **resource group name**. All the resources created will be placed in the same resource group for easy management.
Check the resource group in the Azure portal for the created resources:
The following resources are created: * A storage account * A log analytics workspace * A function app * An app service plan * An application insights instance In addition, some role assignments are created to allow the signed-in user to create the configuration table and backup container in the solution's own storage account, and to also allow the system managed identity of the function app to access the container and table. The Azure function is deployed using [ZIP push deploy](https://learn.microsoft.com/en-us/azure/azure-functions/deployment-zip-push), meaning the function app will then be in read-only mode in the portal as the function will be running from the package. # Set the backup schedule The backup schedule is set in the function app settings using a cron expression. The function reads this setting to determine how often the function timer will trigger. In the created resource group, locate the function app. In the function app, go to **Settings > Configuration**. In the Application settings, you'll notice a setting called **BackupConfigurationTimerExpression.**
This contains the[ cron expression](https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-timer?tabs=python-v2%2Cin-process\&pivots=programming-language-csharp#ncrontab-expressions) that determines how often the function executes and therefore how often your tables are backed up. The default expression, **0 0 1 \* \* \*,** triggers the function every day, once a day, at 01:00. Change this per your requirements, then save your changes. # Add storage tables to the backup The backup configuration is stored in an Azure table in the storage account created by this solution. The solution is pre-configured to backup this table as an example. You can edit the table using either the portal or the Microsoft Azure Storage Explorer. In the portal, locate the storage account that was created in the resource group. In the storage account, go to **Storage browser > Tables** You'll find a table called **AutomatedTableBackupConfiguration**.
Open the table and you will see the default entry, which references this table itself:
To add more tables to the backup, simply click **Add entity**
Add a new table as follows: * **PartitionKey**. This is the **name** of the storage account containing the table/s you want to back up. * **RowKey**. This is the name of the storage **container** within the storage account where the backups will be saved. You do not need to pre-create this container as the solution will create it for you when the first backup runs. * **SourceTableNames**. Add here the **name/s** of the table/s you want to backup within that storage account, separated by a comma. Click **Insert** to add the entity. The next time the backup runs, these tables will be added to the backup. To **remove** a table, simply delete the entity that contains it, or if the entity contains multiple tables, simply delete the table name from the **SourceTableNames** value. # Add role assignments to the storage account/s Before the solution can backup your storage tables, it needs to be granted access to the storage account containing the Azure tables that you want to backup. Specifically, the system managed identity of the function app needs to be granted the **Storage Table Data Reader** and **Storage Blob Data Contributor** roles to the storage account. The script below can do this for you. You'll need to run this against every storage account where you are backing up tables. {% embed url="" %} Download the script and set the parameters at the top of the script: * **Tenant** (tenant Id) * **Subscription** (subscription name) * **StorageAccountName** (the name of the storage account containing the tables you want to backup)
Execute the script, and upon successful execution the new role assigments will be reported:
In the storage account in the Azure portal, in the **Access Control (IAM)** blade, in the **Role assignments** tab, you will see the new role assignments added.
# Create a lifecycle management rule The container where you store your backups will grow indefinitely over time, so its a good idea to add a lifecycle management rule on the backup container to purge old backups. This rule ensures that the container doesn't retain any backup data older than x number of days to keep your storage costs down. To create this rule, you can run the following PowerShell script. You'll need to do this for every storage account where you are running backups. {% embed url="" %} Download the script and set the parameters at the top of the script: * **azSubscription** (the name of the Azure subscription) * **resourceGroupName** (the name of the resource group containing the storage account) * **storageAccountName** (the name of the storage account containing the storage tables being backed up) * **retentionPeriod** (the number of days you want to retain backup data for) * **backupContainername** (the name of the container where the backups are stored) * **ruleName** (the name of the rule you are creating)
After a successful execution of the script, you'll find the rule created in the storage account under **Lifecycle management** in the portal.
The rule uses a filter so that it applies only to the **tablebackups** container, and not any other containers, and will purge any backups older than the date you defined in the script. # Run a manual backup To test the backup process, you can run a manual backup from the Azure portal. In the resource group, locate the function app resource. In the function app, go to **Functions**, then open the **backupAzureTables** function.
In the **Code + Test** blade, click **Test/Run** in the top menu, then click **Run**.
You will see the output of the backup run in the logging window. You can view this any time using the **Monitor** tab of the function, or in the application insights instance.
To check your backup, open the storage account containing the tables you have backed up in the Azure portal. In the **Storage browse**r, open the **tablebackups** container.
You will see your backup CSV file in the container.
# Restore a backup To restore a backup, you can use the [Microsoft Azure Storage Explorer](https://azure.microsoft.com/en-us/products/storage/storage-explorer). This provides a nice UI to interface with the storage account and lets you import data from a CSV backup file into the target table, or into another table. Using the Azure Storage Explorer, connect to your Azure subscription and locate the storage account containing your backups.
Within the **tablebackups** blob container, open the folder with your table name and identify which backup file you want to restore from.
Select the file and click **Download** in the menu to save the file locally.
Open the storage table you want to restore to and click **Import** in the menu. Select your locally-saved backup file to import it.
Notice the option at the bottom: **Replace entity values in the table with imported data.** In my testing this option does not seem to have any effect as whether it is selected or not, any row being restored which matches the **PartitionKey** and **RowKey** of any entity in the table will be overwritten with the restored data. In any case, any entities in the table in that are not present in the backup file will remain present in the table and any entities missing from the table will be restored from the imported data. So if you want to do an authentic point-in-time restore, you may wish to first delete all the entities in the table before you restore, or simply restore to a new table, and change the name when you're happy that the restored data is good.