Applied KQL

Helping others get things done using Kusto Query Language (KQL).

KQL query: Get inactive/stale Intune devices

by

KQL query for Inactive/stale Intune devices – Copilot Designer

Get Intune devices that have not contacted Intune within the last 45 days and should be considered inactive/stale.

Prerequisites for using this KQL query

To use this KQL query, you must be sending your Intune logs to an Azure Log Analytics workspace.

KQL query to get inactive/stale Intune devices

Inactive/stale in this case means the device hasn’t checked in within the last 45 days. The results of this query should be reviewed for disablement/deletion per CIS Safeguard 5.3.

IntuneDevices
| extend LastContactTime = todatetime(LastContact)
| where LastContactTime < ago(45d)
| summarize arg_max(LastContactTime, *) by DeviceName

// credit: ugurkocde/KQL_Intune (github.com)

This will return a result set with a large number of columns, returning as much information as possible from the list of devices.

Deconstructing the KQL query

Read on to understand HOW the query above works.

This query retrieves information about Intune devices, calculates their last contact time, filters out devices that haven’t been contacted in the last 45 days, and then summarizes the data by showing the most recent contact time for each device.

IntuneDevices

We’re starting with the IntuneDevices table, which contains data about devices managed by Intune.

| extend LastContactTime = todatetime(LastContact)

The extend operator creates a new column called LastContactTime. It takes the existing LastContact column (which contains date and time information) and converts it to a proper datetime format. This step ensures that we can perform time-based calculations on this data.

| where LastContactTime < ago(45d)

The where operator filters the rows in our dataset. This is a tricky one in that the time-based calculation means that the older the datetime value, the lower the value. The more recent the date timevalue, the higher the value. In this case, it keeps only the rows where the LastContactTime is a lower value than the value of 45 days ago.

| summarize arg_max(LastContactTime, *) by DeviceName

The summarize operator aggregates data. Here, we’re using the arg_max function. Let’s break it down further:

  • arg_max(LastContactTime, *): This function finds the row within each group (grouped by DeviceName) that has the maximum LastContactTime. In other words, it identifies the most recent contact time for each device.
  • by DeviceName: We group the data by the DeviceName column. For each unique device, we’ll find the latest contact time.

Reference