KQL Search

Query Details

Identity Service Principal Signinfromnew IP

Query

//Detect when an Azure AD service principal signs in from an IP previously not seen

//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs

AADServicePrincipalSignInLogs
| where TimeGenerated > ago(180d) and TimeGenerated < ago(7d)
| distinct AppId, IPAddress
| join kind=rightanti
    (
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago(7d)
    | project TimeGenerated, AppId, IPAddress, ResultType, ServicePrincipalName
    )
    on IPAddress
| where ResultType == "0"
| distinct ServicePrincipalName, AppId, IPAddress

Explanation

This query detects when an Azure AD service principal signs in from an IP address that has not been seen before. It uses the Azure Active Directory - Service Principal Signin Logs data connector. The query looks at logs from the past 180 days but within the last 7 days. It finds distinct combinations of AppId and IPAddress. It then performs a right anti-join with logs from the past 7 days, selecting specific fields. It filters for logs with a ResultType of "0" and finds distinct combinations of ServicePrincipalName, AppId, and IPAddress.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

AADServicePrincipalSignInLogs

Keywords

AADServicePrincipalSignInLogsTimeGeneratedAppIdIPAddressResultTypeServicePrincipalName

Operators

whereagodistinctjoinkindonproject

Actions

GitHub