Signin Logs with expanded Conditional Access Policies

id: 4eb6d052-9873-4092-b989-66eae780e203
name: Signin Logs with expanded Conditional Access Policies
description: |
  'Example query for SigninLogs showing how to break out packed fields.  In this case extending conditional access Policies '
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - SigninLogs
tactics:
  - Impact
query: |

  SigninLogs 
  | extend OS = DeviceDetail.operatingSystem, Browser = DeviceDetail.browser
  | extend ConditionalAccessPol0Name = tostring(ConditionalAccessPolicies[0].displayName), ConditionalAccessPol0Result = tostring(ConditionalAccessPolicies[0].result)
  | extend ConditionalAccessPol1Name = tostring(ConditionalAccessPolicies[1].displayName), ConditionalAccessPol1Result = tostring(ConditionalAccessPolicies[1].result)
  | extend ConditionalAccessPol2Name = tostring(ConditionalAccessPolicies[2].displayName), ConditionalAccessPol2Result = tostring(ConditionalAccessPolicies[2].result)
  | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails)
  | extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city)
  | extend Date = startofday(TimeGenerated), Hour = datetime_part("Hour", TimeGenerated)
  | summarize count() by Date, Identity, UserDisplayName, UserPrincipalName, IPAddress, ResultType, ResultDescription, StatusCode, StatusDetails, 
  ConditionalAccessPol0Name, ConditionalAccessPol0Result, ConditionalAccessPol1Name, ConditionalAccessPol1Result, ConditionalAccessPol2Name, ConditionalAccessPol2Result, 
  Location, State, City
  | extend timestamp = Date, AccountCustomEntity = UserPrincipalName, IPCustomEntity = IPAddress
  | sort by Date 
  
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: AccountCustomEntity
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPCustomEntity
version: 1.0.0
metadata:
    source:
        kind: Community
    author:
        name: Shain
    support:
        tier: Community
    categories:
        domains: [ "Security - Other", "Identity" ]