Introduction

Desktop Analytics is a powerful tool for helping plan deployments of Windows 10 Feature Updates. Two of its features are the Apps and Drivers tabs which provides cloud enabled insights in to your Application and Driver estate and its compatibility with Windows 10 Feature Updates.

The Apps tab has a Plan Installs column which tells you how many devices the particular Application is installed on. Similarly, the Drivers tab has a Plan Devices column which does the same.

One of the shortcomings of the Desktop Analytics console is that although it tells you how many devices have an Application or Driver , you cannot drill down further to get a list of those devices. Being able to view the list of devices without having to run a report in ConfigMgr is a challenge that I recently encountered.

In steps Log Analytics workspace queries! Desktop Analytics stores its data in a Log Analytics workspace. Using the query language Kusto (KQL) we can search the database directly and export lists of devices. Below I have shared the scripts I wrote to do this along with explanations of what they are doing.

Skip to the end of this post if you just want to see the scripts in their entirety.

Pre-requisites

• Ability to run queries in the Log Analytics workspace in which Desktop Analytics resides.
• Access to the Desktop Analytics console.

Prepare

1. Get the exact wording for the search terms

First go to the Desktop Analytics console and drill down in to your Deployment Plan > Plan assets > Apps.

Select the Application that you want to report on from the list. In this example I want Adobe Acrobat Reader DC version 21.001.20145.

Copy the exact wording of the name and version number. We will use this in the script query.

If you are searching for a Driver then you go to Deployment Plan > Plan assets > Drivers.

Select the Driver that you want to report on from the list. In this example I want the HP HD Camera Driver spuvcbv64.sys version 3.7.8.5.

Again, ensure to copy the exact wording of the name and version number.

2. Open a Log Analytics workspace query

Navigate to the Log Analytics workspace that contains the Desktop Analytics data. On the left hand menu select Logs.

You should see a blank New Query 1 window (you may have to close the Queries popup to see this).

The scripts use tables in the Microsoft365Analytics sphere. If you expand this on the left you will see all the available tables.

Tip: Double clicking a table will add it to the query pane. Select Run with just the name of a table in the query pane and you will see all the data in the table.

Scripts Deep Dive

Tip: Desktop Analytics takes a data scrape from Microsoft’s central Telemetry repository once every 24 hours. The historical data is left in the tables. So when searching a table be sure to filter by the last 24 hours to prevent duplication of results!

Applications

There are three sections to this code that deal with each table it needs to work through to find the device names from a given Application name.

Creates a variable named AppProgID.

Searches the MAApplication table for the Application name and version number. Filter on the most recent data scrape (last day) and extract only the unique ProgramID. There is one ProgramID per Application/Version pairing so each version of Adobe Acrobat Reader DC has its own unique ProgramID.

Tip: You can leave the AppVersion field empty if you are searching for an Application that Desktop Analytics has not detected the version of. You will encounter these In the DA console, the version field is blank.

Another variable is created called Devices. Searches the ProgramID column of the MAApplicationInstance table for entries matching the ProgramID stored in the AppProgID variable.

Filters for the most recent data scrape (last day) and extracts the unique device ID. These IDs are meaningless outside of Desktop Analytics so we need to convert these to the device names.

Search the DeviceID column in the MADevice table for anything matching entries in the Devices variable. Filters for the most recent data scrape (last day) and extracts a useful set of information about the devices.

You can export this list to CSV by clicking Export at the top of the Query pane.

Drivers

Due to a difference in the way the data is stored in the tables there are only two parts to this script.

Creates a variable named DeviceID.

Searches the MADriverInstanceReadiness table for the Driver name and version number. Filters on the most recent data scrape (last day) and extracts only the unique DeviceID of all devices that have the Driver installed.

Tip: You can leave the DriverVersion field empty if you are searching for a Driver that Desktop Analytics has not detected the version of. You will encounter these in the DA console, the version field is blank.

Search the DeviceID column in the MADevice table for anything matching entries in the DeviceID variable. Filters for the most recent data scrape (last day) and extracts a useful set of information about the devices.

You can export this list to CSV by clicking Export at the top of the Query pane.

Scripts In Full

Applications

// Desktop Analytics: List workstations with a particular
// Application installed.
// Author: Marcus Zvimba, 28th April 2021
//
/////// INSTRUCTIONS
// Replace the text in the speech marks after AppName and AppVersion with the name and
// version of the Application as it appears in the Desktop Analytics console.
// The text is not case sensitive.
//

//Get ProgramID
let AppProgID =
    MAApplication
    | where AppName =~ "Adobe Acrobat Reader DC"
    | where AppVersion =~ "21.001.20145"
    | where TimeGenerated > ago(1d)
    | project ProgramId;
//Get Device IDs
let Devices =
    MAApplicationInstance
    | where ProgramID in (AppProgID)
    | where TimeGenerated > ago(1d)
    | project DeviceId;
//Get Device Names
MADevice
| where DeviceId in (Devices)
| where TimeGenerated > ago(1d)
| project DeviceName, DeviceId, DeviceLastSeenDate, Manufacturer, Model, OSVersion, OSEdition, OSBuildNumber, OSArchitecture

Drivers

// Desktop Analytics: List workstations with a particular
// driver installed.
// Author: Marcus Zvimba, 28th April 2021
//
/////// INSTRUCTIONS
// Replace the text in the speech marks after DriverName and DriverVersion with the name and
// version of the driver as it appears in the Desktop Analytics console.
// The text is not case sensitive.
//

//Get DeviceID
let DeviceID =
    MADriverInstanceReadiness
    | where DriverName =~ "spuvcbv64.sys"
    | where DriverVersion =~ "3.7.8.5"
    | where TimeGenerated > ago(1d)
    | project DeviceId;
//Get Device Names
MADevice
| where DeviceId in (DeviceID)
| where TimeGenerated > ago(1d)
| project DeviceName, DeviceId, DeviceLastSeenDate, Manufacturer, Model, OSVersion, OSEdition, OSBuildNumber, OSArchitecture

Summary

Kusto is a powerful language and once the core concepts are understood it can be leveraged to maximise the data available from Desktop Analytics. You can create graphical reports to compliment the built-in reports in the Desktop Analytics console.

There is not much movement from Microsoft on the Desktop Analytics UserVoice forum so I highly recommend becoming familiar with how to query the data directly.