One of the capabilities that Microsoft has been discussing a ton as of late is Intune Device Query. Its usefulness is up for debate, but that is why I am writing this two-part series to close out December. In Part One, we will cover:

• What is Intune Device Query?
• What Exactly is Kusto Query Language? (KQL)
• The Basics of KQL

What is Intune Device Query?

Part two will dig deep into Intune Device Query along with some real-world viability.

Intune Device Query lets you quickly query information about the state of your devices in Intune. The query is run in real-time, which lets you take action as needed from a security perspective or make key business decisions.

There are some real requirements that people may be a bit displeased with:

• Requires Microsoft Intune Suite ($10 per user) or Intune Advanced Analytics Add-On License ($5 per user).
• Device must be enrolled in Endpoint Analytics
• Must use WNS (Windows Notification Service)
• Device must be Intune-Managed and Corporate-Owned
• Remote Actions requires at least the Help Desk role in Intune, Device Query requires the “Managed Devices – Query” permission (most roles have this e.g. Intune Admin, Help Desk Operator, Read-Only Operator, etc.)
• Only works on Windows 10+

We’ll cover more about IDQ (Intune Device Query) in part two, but let’s focus on the “beloved” KQL.

What is Kusto Query Language (KQL)?

KQL was invented somewhere around 2014-2016-ish during the development of Microsoft’s big data analytics platform Azure Data Explorer.

KQL was created to explore data and discover patterns, look for anomalies, create statistical modeling, and much more. The idea with KQL was to create something simple and human readable while being able to effectively query structured, semi-structured, and unstructured data. Several services in Azure have been leveraging KQL for quite a while like Log Analytics or Azure Monitor.

A KQL query is a read-only request (thank god) that takes tabular input and produces tabular output. For us to properly understand KQL, we will start with the building blocks. First, let’s look at a very simple KQL query for Intune Device Query and then we will break it out and discuss the basics:

Cpu
| project ProcessorId, Model, ProcessorType, Architecture

So, I ran that and this was the result:

Now, we have seen something very basic, but it gives us some of the core concepts around how this all works. In Part Two, we will look at the tables, expressions, properties, and much more inside of Intune, but let’s focus on the basics for now.

The Basics of KQL

Obviously, everything starts with a table. Specifying the table is very easy (even easier than SQL). You just simply state the name and add a “pipe”:

Bios
|

That is how we start with our query by saying let’s look at this table. Beyond that, we will start to check out some of the building blocks of our queries. We will be focused on capabilities that will be available in Intune Device Query versus the entire world of KQL:

• Table Operators
• Scalar Operators
• Aggregation Functions
• Scalar Functions

Table Operators in KQL

So WTF is a “Table Operator?” Sarcasm would say “it’s an operator for a table” but we can do better than that.

We have a collection of commands we take on a table to start building our query, which is similar to what many people with basic SQL knowledge are familiar with aka “Where?”

The available table operators are:

• Count (tell me how many entries are in that table):

• Distinct (return unique records):

• Join (merge two rows together to make a new super table by matching row for the same device, fun fact on this one is if you try certain combinations it crashes Intune e.g. Windows Service and Process)

• Order By (Sort rows of input by one of the columns)

• Project (This is basically “Select” in SQL. It lets you select what properties to include, rename, or drop. You can also insert new computed columns)

• Take (Return a specified number of rows)

• Top (Returns the first N records sorted by specified columns. Honestly not sure why Take and Top both exist. One note the not null doesn’t work on this)

• Where (Filter a table based on certain criteria)

Scalar Operators in KQL

Now, we move onto the scalar operators in KQL. These are fairly basic and stuff you know pretty well most likely. Within KQL, we can use scalar operators with the where filter or other items to refine the data we are looking for. The different operators supported, which we full from the Microsoft docs are:

Overall, we use these operators to write compelling queries that meet our needs like:

Certificate
| where CommonName contains "Microsoft" and KeyStrength == 4096 and SelfSigned != true

That query itself is a nice example of multiple scalar operators at play to refine your results into something that is useful.

Aggregation Functions in KQL

The aggregation functions are a bit different. We now introduce another option for Device Query called “Summarize”

We can basically use functions to do things like:

• Return averages across a property
• Return a count across a property
• Return the number of discount values across a property
• Return the maximum or minimum value
• Return a percentile
• Return a sum
• Leverage If functions for count, min, max, and sum if the “If” evaluates as true

Below is an example of using the average function in action:

Scalar Functions in KQL

The final thing we will cover are scalar functions. We use scalar functions to deliver “one-dimensional” results as part of our larger queries as a whole.

We can some of these things:

• Subtract or Add to time properties stored in tables
• Round date and time values
• Evaluate a list and return the first one that returns true
• Booleans to return if something is true or false
• Returns current time
• Return X amount of characters in a string or a extracts substring
• Concatenates
• Converts to string

These ones are a bit confusing because it’s sometimes hard to know if they’re actually helpful or not. One example I used them in are evaluating certificates. This is the query I used:

Certificate
| where ValidToDateTime >= now() + 365d and SubjectName contains "Microsoft" and SelfSigned == false | top 100 by SubjectName

With this, we get a nice result set:

The final query we will use with the If statement:

Certificate
| where ValidToDateTime < now() + 365d or ValidToDateTime < now() and SelfSigned == false and IsCa == false
| top 100 by ValidToDateTime
| project SubjectName, Issuer, ValidToDateTime, IsExpired = iif(ValidToDateTime < now(), "Expired", "Valid"), SerialNumber

Final Thoughts

Overall, I had a bunch of thoughts after looking at device query and how it leverages KQL. I’m going to pass most of it onto the product team. Let’s first cover the gaps we’ve found:

• Multiple key KQL capabilities don’t work like formatting date/time.
• Capped at 15 queries per minute
• Some of the queries should require == but instead a single = works
• Certain operators are unsupported like !like
• I’m somewhat confused by the placement of “Device Actions” Sure its convenient, but it would be nicer if you could write an If statement in KQL that could trigger a device action e.g. renewing a certificate or rotating an admin password.

Overall, Device Query is “nice”, but I would want to query across an entire fleet instead of a single device. I think most of my utility is querying registry keys, which I was gonna save until next week but here’s a small sample:

It does have potential, but in general for the cost we need more cohesion with the admin experience overall.