KQL Search

Query Details

HomeAI ToolsDevice Query

Successful Join Of Fake Device Using ROPC Query By Goldjg

Query

//Query written by Graham Gold https://www.linkedin.com/in/graham-gold/ - Github: @goldjg
let SigninEvents = SigninLogs
| where TimeGenerated >= ago(90d)
| where ResourceDisplayName has "Device Registration Service"
| where AuthenticationProtocol has "ropc"
| where ResultType == "0"
| project SigninTime = CreatedDateTime,SigninUserPrincipalName = UserPrincipalName,SigninCorrelationId = CorrelationId,
SigninUserAgent = UserAgent,
StartTime = CreatedDateTime - 60s,
EndTime = CreatedDateTime + 60s,
dummy = 1;
SigninEvents
| join kind=inner (
AuditLogs
| where OperationName has "Add device"
| where Result has "success"
| project AuditTime = ActivityDateTime,FakeDeviceId = tostring(parse_json(tostring(TargetResources[0].id))),FakeDeviceDisplayName = tostring(TargetResources [0].displayName),
AuditCorrelationId = CorrelationId,
dummy = 1
) on dummy
| where AuditTime between (StartTime .. EndTime)
| project SigninTime, AuditTime, SigninUserPrincipalName, SigninCorrelationId, SigninUserAgent, FakeDeviceId, FakeDeviceDisplayName, AuditCorrelationId

Explanation

This query is designed to identify successful device registration events that are closely associated with specific sign-in activities. Here's a simplified breakdown of what the query does:

  1. Filter Sign-in Events:

    • It starts by filtering sign-in logs from the past 90 days.
    • It looks for events related to the "Device Registration Service" where the authentication protocol used is "ropc" (Resource Owner Password Credentials).
    • It only considers successful sign-ins (where ResultType is "0").
    • It selects specific details from these sign-in events, including the time of sign-in, user principal name, correlation ID, and user agent. It also defines a time window (from 60 seconds before to 60 seconds after the sign-in) to match with audit logs.

  2. Filter Audit Logs:

    • It then filters audit logs for operations named "Add device" that were successful.
    • It extracts details such as the time of the audit event, a fake device ID, device display name, and correlation ID.

  3. Join and Match Events:

    • The query performs an inner join between the filtered sign-in events and audit logs based on a dummy column (used to facilitate the join).
    • It further filters the joined data to find audit events that occurred within the defined time window around each sign-in event.

  4. Output:

    • Finally, it projects (selects) relevant information from the matched events, including the times of the sign-in and audit events, user principal name, correlation IDs, user agent, and device details.

In essence, this query helps identify and correlate successful device registration events with specific sign-in activities, providing insights into the context and details of these operations.

Details

Jay Kerai profile picture

Jay Kerai

Released: July 30, 2025

Tables

SigninLogsAuditLogs

Keywords

SigninLogsDeviceRegistrationServiceAuthenticationProtocolResultTypeCreatedDateTimeUserPrincipalNameCorrelationIdUserAgentAuditLogsOperationNameResultActivityDateTimeTargetResourcesIdDisplayName

Operators

let|where>=ago()has==project-+joinkind=innertostring()parse_json()between..

Actions