Identify Users’ Enrolled Mobile Devices (iOS and Android) with KQL

Recently, I received a request from a customer to identify users who have enrolled their mobile devices, specifically iOS and Android, managed by Intune.

Initially, I explored the Intune console, applying filters based on the operating system to generate a list of mobile devices and their associated users. However, this approach only provided a basic view, requiring me to export the data to a CSV file and use Excel formulas to identify users with both iOS and Android devices—an arduous and time-consuming process.

Streamlining the Process with KQL and Power BI

To simplify this use case, I turned to KQL (Kusto Query Language) within Azure Log Analytics. By integrating this with Power BI or Azure workbooks, I was able to create a more efficient and visually engaging solution.

Prerequisites: Ensure that your Diagnostic Settings for device logs are enabled in your Log Analytics workspace. If you’ve already configured this, you’re ready to start using KQL and Power BI.

The KQL query analyzes device data from the Intune Log Analytics workspace, identifying users who have both iOS and Android devices. It includes a subquery that summarizes the count of iOS and Android devices for each user and creates a flag to indicate whether a user has both types of devices.

I have added new column for devices that have not contacted Intune within the last 30 days are marked as "Inactive," but you can adjust this timeframe to suit your needs.

KQL Queries for Device Enrollment

Here’s a KQL query to find users who have enrolled both iOS and Android devices (regardless of the enrollment method):

//Define Variables
let startDate = ago(30d);
let endDate = now();
let iOS_check = dynamic(["iOS/iPadOS"]);
// Subquery to calculate iOS and android devices (Both)
let UserDeviceSummary = IntuneDevices
     | where ManagedBy != "MDE"
     | summarize 
         has_ios = countif(OS == "iOS/iPadOS"), 
         has_android = countif(OS contains "Android")
       by UPN
     | extend iOSAndAndroid = iff(has_ios > 0 and has_android > 0, "Yes", "No");
 // Main query to get device details and join with BothDevices column
IntuneDevices
 | summarize arg_max(TimeGenerated, *) by SerialNumber, DeviceId
 | where (OS in (iOS_check) or OS contains "Android")
       and ManagedBy != "MDE" 
 | extend DeviceUrl = strcat('https://endpoint.microsoft.com/#blade/Microsoft_Intune_Devices/DeviceSettingsBlade/overview/mdmDeviceId/', DeviceId)
 | extend Active = iff((todatetime(LastContact) between (startDate .. endDate)), "Active", "Inactive")
 | join kind=inner UserDeviceSummary on UPN
 | where iOSAndAndroid == "Yes"
 | project UPN, UserEmail, DeviceName, OS, OSVersion, Manufacturer, Model, CreatedDate,LastContact,Active,DeviceUrl, iOSAndAndroid

For users who have enrolled both iOS and Android devices specifically with a Personally-Owned Work Profile, you can use the following KQL query:

//Define Variables
 let startDate = ago(30d);
 let endDate = now();
 let ios_android_check = dynamic(["iOS/iPadOS", "Android (Personally-Owned Work Profile)"]);
 // Subquery to calculate BothDevices column
 let UserDeviceSummary = IntuneDevices
     | where OS in (ios_android_check) and ManagedBy != "MDE" and CompliantState != "ConfigManager"
     | summarize has_ios = countif(OS == "iOS/iPadOS"), has_android = countif(OS == "Android (Personally-Owned Work Profile)") by UPN
     | extend iOSAndAndroid = iff(has_ios > 0 and has_android > 0, "Yes", "No");
 // Main query to get device details and join with BothDevices column
IntuneDevices
 | summarize arg_max(TimeGenerated, *) by SerialNumber, DeviceId
 | where OS in (ios_android_check) and ManagedBy != "MDE" and CompliantState != "ConfigManager"
 | extend DeviceUrl = strcat('https://endpoint.microsoft.com/#blade/Microsoft_Intune_Devices/DeviceSettingsBlade/overview/mdmDeviceId/', DeviceId)
 | extend Active = iff((todatetime(LastContact) between (startDate .. endDate)), "Active", "Inactive")
 | join kind=inner UserDeviceSummary on UPN
 | where iOSAndAndroid == "Yes"
 | project UPN, UserEmail, DeviceName, OS, OSVersion, Manufacturer, Model, CreatedDate,LastContact,Active,DeviceUrl, iOSAndAndroid

Output Visualization:

You can use these KQL queries in Power BI for enhanced data visualization, making it easier to present and analyze the enrolled devices.

I hope this guide helps you streamline your device management process!

For more information on KQL functions, refer to the official documentation: KQL Aggregation Functions