AzureADConditionalAccessStateChanges

AzureADConditionalAccessStateChanges.kql

// AzureAD Conditinoal Access State cmparisson
let CAStateBefore = CloudAppEvents
    | where Timestamp > startofday(ago(30d)) and Timestamp < startofday(ago(1d))
    | where ActionType == "Set-ConditionalAccessPolicy"
    | extend CAId = tostring((split(tostring(parse_json(ActivityObjects)[2].Value), @"\"))[1])
    | extend CAState = extractjson("$.State", tostring((parse_json(ActivityObjects)[3].Value)))
    | extend CAName = tostring(parse_json(ActivityObjects)[6].Value)
    | where isnotempty(CAState)
    | where CAName != "Default Policy"
    | summarize arg_max(Timestamp, *) by CAId
    | project Timestamp, CAName, CAState, CAId
    | sort by Timestamp desc;
let CAStateCurrent = CloudAppEvents
    | where Timestamp > startofday(now())
    | where ActionType == "Set-ConditionalAccessPolicy"
    | extend CAId = tostring((split(tostring(parse_json(ActivityObjects)[2].Value), @"\"))[1])
    | extend CAState = extractjson("$.State", tostring((parse_json(ActivityObjects)[3].Value)))
    | extend CAUpdateTime = todatetime(tostring(parse_json(ActivityObjects)[4].Value))
    | extend CAName = tostring(parse_json(ActivityObjects)[6].Value)
    | where isnotempty(CAState)
    | where CAName != "Default Policy"
    | project Timestamp, CAName, CAState, CAId, CAUpdateTime
    | summarize arg_max(Timestamp, *) by CAId
    | sort by Timestamp desc;
CAStateBefore
| join kind= leftouter CAStateCurrent
    on $left.CAId == $right.CAId
| extend CAStateCurrent = CAState1
| extend CAStateBefore = CAState
| project CAUpdateTime, CAName, CAStateCurrent, CAStateBefore
| sort by CAUpdateTime desc