Explore Kusto Query Language (KQL) and Intune Device Query

Let’s explore Kusto Query Language (KQL) and learn how to use it to query Intune-Device in this article. I shall expound on the Kusto Query Language (KQL) as well as provide practical examples of KQL queries.

The Device Query feature in the Intune service is designed to facilitate device management and information retrieval efficiently and streamlined. To use it, you need to select the device from the Intune portal and then run the Kusto Query Language (KQL) query. The Device Query reports are available in real time, and you can retrieve them directly. The output returned can be utilized for responding to security threats, troubleshooting the device, and making informed business decisions.

In case your license does not include Microsoft Intune Advanced Analytics, the Device Query option will not be available in your tenant. To access the features of Microsoft Intune Advanced Analytics, you can utilize the Intune Advanced Analytics Add-on or Microsoft Intune Suite. It is imperative to note that the lack of a license that covers Microsoft Intune Advanced Analytics may hinder the availability of the Device Query option.

Intune’s Device Query feature is a valuable tool that provides administrators with crucial insights into a wide range of software and hardware configurations. What sets Device Query apart is its ability to encrypt all data queries and results, ensuring that all transmission is secure. This feature offers a significant advantage over other similar platforms as it guarantees the protection of sensitive data throughout the entire process. Device query only supports a subset of KQL operators.

Patch My PC

What is Kusto Query Language (KQL)?

Kusto Query Language (KQL) is the query language that retrieves information from Intune. You can use the KQL query method to get a quick overview of Azure AD joined devices Vs. Hybrid Azure Active Directory joined devices.

Explore Kusto Query Language (KQL) and Intune Device Query Fig. 1
Explore Kusto Query Language (KQL) and Intune Device Query Fig. 1

KQL is a simple and powerful language. It enables you to query structured, semi-structured, and unstructured data with ease. The query intent is easily understood with this expressive and readable language optimized for authoring experiences. A Kusto query is a read-only request that processes data and generates results. It is presented in plain text and employs a data-flow model that is easily understandable, authorizable, and automatable. Typically, a Kusto query consists of one or more query statements.

Tabular expression statements represent the most commonly employed query statement type in the context of tabular datasets. These statements are characterized by both input and output being composed of tabular data. The statements comprise a series of operators. Each operator takes as input a tabular dataset and returns a tabular output. The operators are arranged in a sequence, which is represented by a pipe symbol ( | ). Data flows sequentially through the operators, with output from one operator serving as input for the subsequent operator.

I would recommend referring to Microsoft’s Kusto Query Language learning resources. These resources provide comprehensive and insightful information on the subject matter and are designed to facilitate a deeper understanding of the topic. Also, refer to the Kusto Query Language (KQL) overview to explore more..! The following operators are currently supported in KQL; thanks to Microsoft for the table.

Adaptiva
Supported Operators Description
Table operatorsIt can be used to filter, summarize, and transform data streams
Scalar operators
It produces a new result from one or more other values
Aggregation functionsAggregation functions can be used with the summarize table operator to calculate summarized values.
Scalar functionsA scalar function is a function that returns one value per invocation
Explore Kusto Query Language (KQL) and Intune-Device Query.Table.1

How to use the Device Queries in Intune

We have enough discussed of the Intune-Device Query and Kusto Query Language (KQL). Let’s start learning how to use Kusto Query Language (KQL) in the Intune portal and retrieve live data.

  • Sign in to the Microsoft Intune Admin portal.
  • Go to Devices > All devices
  • Select the Device that you’re interested in.
  • On the right-hand side, click on Device query.
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.2
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.2

Once you click on Device query, you will have the Properties table like BiosInfo.

Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.3
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.3

Device Query to List BIOS-related Information

Let’s start with a simple Device query to list all BIOS-related pieces of information. Once you click on any Device query, you will see a list of the Properties table. Select BiosInfo to query Properties. I probably can’t test a lot of Device Queries in this article. However, I will test some useful Queries. The below KQL query will list all the BIOS-related information. Let’s start..!!!

BiosInfo
| project Manufacturer, ReleaseDateTime, SerialNumber, SmBiosVersion

Paste the above query inside the box and click Run to execute. The result will appear on the result panel. If you execute the query by simply typing BiosInfo, you can retrieve all the details. This is a great starting point for learning the Kusto Query Language.

Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.4
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.4

Device Query to Monitor CPU Performance and Health

Click on Cpu in the Properties table to retrieve the live status of the CPU. The Cpu Properties table contains various columns that assist you in monitoring CPU performance and health.

Cpu | project Manufacturer, CurrentClockSpeed, MaxClockSpeed , CpuStatus

Paste the above query inside the box and click Run to execute.

Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.5
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.5

The result will appear on the result panel. The Result has Manufacturer, CurrentClockSpeed, MaxClockSpeed and CpuStatus. You can add more columns and conditions if you are good with KQL querying.

List all the Files in a Directory using Device Query

We will use the FileInfo table to get all the files in a Directory from an enrolled device. You can view columns such as the Filename, Date Modified, and Size of the file. This can be used when troubleshooting any kind of issue on a user’s computer. Also, you can monitor which folder or file consumes more disk space on the user’s computer.

  • Click on FileInfo and paste the below query
  • Click Run to execute
FileInfo('C:\Windows\temp\*')
|  project Path, Directory, SizeBytes , LastModifiedDateTime, CreatedDateTime
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.6
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.6

You can use order by command if you would like to sort any columns. I have sorted SizeBytes columns using the below query.

FileInfo('C:\Windows\temp\*')
|  project Path, Directory, SizeBytes , LastModifiedDateTime, CreatedDateTime | order by SizeBytes desc

Device Query to Find Processes With High Memory Usage

Using a device query, it’s easy to Find Processes With High Memory Usage. Click on the Process table and explore the columns available. The below query will give you the process name and its average Memory Usage Bytes. Thanks to Copilot for this query!

  • Click on Process and paste the below query
  • Click Run to execute
Process
| summarize AvgMemoryUsageBytes=avg(WorkingSetSizeBytes), MaxMemoryUsageBytes=max(WorkingSetSizeBytes), MinMemoryUsageBytes=min(WorkingSetSizeBytes) by ProcessName
| order by AvgMemoryUsageBytes desc
| project ProcessName, AvgMemoryUsageBytes, MaxMemoryUsageBytes, MinMemoryUsageBytes
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.7
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.7

List the Latest Application Installations using Device Query

There are various ways to monitor the success of an application installation in Intune. We typically rely on Intune reporting post-deployment to ensure that the application has been deployed successfully. The Windows Event IDs can also be used to monitor the application installation status. There are dedicated event IDs for monitoring application installation success and failure. Let’s learn how to utilize Device Query to read event logs.

Click on WindowsEvent in the Properties table to retrieve event logs from an enrolled device. You can view columns such as the EventId, Level, LoggedDateTime, LogName, Message, ProviderName and Windows User Account. Let’s see the Query to display the Latest Application Installations on the device that has been enrolled.

  • Click on WindowsEvent and paste the below query
  • Click Run to execute
WindowsEvent('Application', 7d)
| where Message contains 'Installation completed successfully' 
| project LogName, EventId , Message, LoggedDateTime
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.8
Explore Kusto Query Language (KQL) and Intune-Device Query. Fig.8

That’s all for now! In the Intune portal, there are a total of 21 device query properties and their corresponding columns available. I recommend that you explore these properties and columns, and start querying slowly with simple tasks. I’ll be back with more use cases and device queries in my next article.

Thank you for your patience in reading this post. I look forward to seeing you in the next post. Keep supporting the HTMD Community.

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click hereHTMD WhatsApp.

Author

About Author – Sujin Nelladath has over 10 years of experience in device management technologies and Automation solutions. He writes and shares his experiences related to Microsoft device management technologies, Azure, and PowerShell automation.

2 thoughts on “Explore Kusto Query Language (KQL) and Intune Device Query”

  1. Is there any we can utilize reporting like SQL query from SCCM DB for Intune managed AAD machines. I am looking for a solution to query the details of all the available windows 11 machines and to create reports based on various requirements just like how we used to SQL query from SCCM DB.

    Reply
    • To query details of Windows 11 machines that are Azure AD joined, enrolled in Intune, and co-managed, you can create a collection in Configuration Manager using a custom WQL query below

      select * from SMS_R_System where SMS_R_System.AADTenantID = “4252590E-6F9B-4AA1-AA9F-D7717C111B07” and SMS_R_System.ResourceId not in (select ResourceID from SMS_R_System where SMS_R_System.ResourceDomainORWorkgroup = “INTRANET”)

      Replace “4252590E-6F9B-4AA1-AA9F-D7717C111B07” with your AAD tenant ID and “INTRANET” with your domain name. You can do more querying once the collection is created.

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.