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.
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.
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.
| Supported Operators | Description |
|---|---|
| Table operators | It can be used to filter, summarize, and transform data streams |
| Scalar operators | It produces a new result from one or more other values |
| Aggregation functions | Aggregation functions can be used with the summarize table operator to calculate summarized values. |
| Scalar functions | A scalar function is a function that returns one value per invocation |
- SCCM Collection for Windows 11 22H2 Devices | CMPivot | SQL Queries
- Use SCCM CMPivot to Perform Security Audits Created New Local User Account | Tried to Reset Password
- List of Intune Devices with Patch Deployment Status and Country Details using KQL queries
- Intune Pivot Query Real-time Reporting Troubleshooting Capabilities
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.
Once you click on Device query, you will have the Properties table like BiosInfo.
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, SmBiosVersionPaste 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.
- Intune Report for AAD Joined Vs Hybrid AAD Joined Devices using KQL Query
- Intune Device Encryption Status Report
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 , CpuStatusPaste the above query inside the box and click Run to execute.
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
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- Intune Anomaly Detection Device and Advanced Analytics
- MS Defender Advanced Hunting using KQL Queries
- Intune Diagnostics Settings Log Analytics KQL Queries Azure Subscription Missing Issue
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
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
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 here – HTMD 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.