KQL Sign-Ins: Non-Joined Devices

KQL for SigninLogs showing non-joined Azure AD devices by User and Application.

SigninLogs
| where TimeGenerated > ago (1d)
| where ResultType == 0
| extend trustType = tostring(DeviceDetail.trustType)
| where trustType != "Hybrid Azure AD joined"
| where trustType != "Azure AD joined"
| where ResourceIdentity <> 'radius'
| summarize
    ['SigninCount']=dcount(Id)
    by UserPrincipalName, AppDisplayName
| sort by ['SigninCount']

// UserPrincipalName         | AppDisplayName    | SigninCount
// |-------------------------|-------------------|-------------|
// john.smith@domain.com     | SharePoint Online | 500
// admin@domain.com          | SharePoint Online | 500
// john.smith@domain.com     | Exchange Online   | 1500
// admin@domain.com          | Exchange Online   | 1500

KQL for SigninLogs showing non-joined Azure AD devices by User

SigninLogs
| where TimeGenerated > ago (1d)
| where ResultType == 0
| extend trustType = tostring(DeviceDetail.trustType)
| where trustType != "Hybrid Azure AD joined"
| where trustType != "Azure AD joined"
| where ResourceIdentity <> 'radius'
| summarize
    ['SigninCount']=dcount(Id)
    by UserPrincipalName
| sort by ['SigninCount'] desc 

// UserPrincipalname      | SigninCount
// |----------------------|--------------|
// john.smith@domain.com  | 2000
// admin@domain.com       | 2000

KQL for SigninLogs showing non-joined Azure AD devices by Application

SigninLogs
| where TimeGenerated > ago (1d)
| where ResultType == 0
| extend trustType = tostring(DeviceDetail.trustType)
| where trustType != "Hybrid Azure AD joined"
| where trustType != "Azure AD joined"
| where ResourceIdentity <> 'radius'
| summarize
    ['UniqueUserSigninCount']=dcount(UserPrincipalName)
    by AppDisplayName
| sort by ['UniqueUserSigninCount'] desc 

// AppDisplayName               | UniqueUserSigninCount
// |----------------------------|-----------------------|
// Office 365 SharePoint Online | 2
// Office 365 Exchange Online   | 2