Forum Discussion

Danielburt1650's avatar
Danielburt1650
Copper Contributor
Nov 01, 2023

Find sign-ins from non entra managed devices over the past month using KQL

Hi we are looking to find logins from non domain joined devices using KQL but can't seem to write a query that works. I know I can find this data in defender for cloud apps but the logs limit only reaches 5000.

 

I was thinking something likethis 

SigninLogs
| where createdDateTime >= ago(30d)
| where deviceTrustType !in ("Enrolled", "Managed")
| project createdDateTime, userPrincipalName, ipAddress, deviceTrustType, deviceId, appDisplayName, location

 

KQL
kusto
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Nov 01, 2023

    Danielburt1650 How about this?

    SigninLogs
| where TimeGenerated >= ago(30d)
| where DeviceDetail.isCompliant == false
        and ResultType ==0
| extend deviceName_ = tostring(DeviceDetail.displayName)
| extend trustType_ = tostring(DeviceDetail.trustType)
| distinct deviceName_, trustType_, UserPrincipalName , IPAddress, AppDisplayName, DeviceID=tostring(DeviceDetail), Location

      Some more ideas here: Microsoft Entra security operations for devices | Microsoft Learn

Resources