✄𐘗
text code block:
✄𐘗from logs-azure.auditlogs-* metadata _id, _version, _index
| where event.action == "Update application"
| where `azure.auditlogs.properties.target_resources.0.modified_properties.0.display_name` == "FederatedIdentityCredentials"
| eval Esql.fic_old_value = `azure.auditlogs.properties.target_resources.0.modified_properties.0.old_value`
| eval Esql.fic_new_value = `azure.auditlogs.properties.target_resources.0.modified_properties.0.new_value`
// each credential is one JSON object, so the number of "{" is the credential count
| eval Esql.fic_old_count = mv_count(split(Esql.fic_old_value, "{")) - 1
| eval Esql.fic_new_count = mv_count(split(Esql.fic_new_value, "{")) - 1
// removals never alert
| where Esql.fic_new_count > 0 and Esql.fic_new_count >= Esql.fic_old_count
// strip the outer brackets and expand the new array into one row per credential object
| eval Esql.fic_object = split(replace(substring(Esql.fic_new_value, 2, length(Esql.fic_new_value) - 2), "\\},\\{", "}\n{"), "\n")
| mv_expand Esql.fic_object
| eval Esql.fic_id = json_extract(Esql.fic_object, "Id"),
       Esql.fic_new_issuer = json_extract(Esql.fic_object, "Issuer"),
       Esql.fic_new_subject = json_extract(Esql.fic_object, "Subject")
// locate the same credential Id in the old array and rebuild that object to read its previous issuer and subject
| eval Esql.fic_old_id_pos = locate(Esql.fic_old_value, concat("\"Id\":\"", Esql.fic_id, "\""))
| eval Esql.fic_old_object = case(
    Esql.fic_old_id_pos > 0,
    concat("{", mv_last(split(substring(Esql.fic_old_value, 1, Esql.fic_old_id_pos - 1), "{")), mv_first(split(substring(Esql.fic_old_value, Esql.fic_old_id_pos), "}")), "}"),
    null
  )
| eval Esql.fic_old_issuer = json_extract(Esql.fic_old_object, "Issuer"),
       Esql.fic_old_subject = json_extract(Esql.fic_old_object, "Subject")
// subject is null on flexible credentials that use a claims matching expression, so coalesce before comparing
| eval Esql.fic_change = case(
    Esql.fic_old_count == 0, "fic_created",
    Esql.fic_old_id_pos == 0 and Esql.fic_new_count > Esql.fic_old_count, "fic_added",
    Esql.fic_old_issuer != Esql.fic_new_issuer, "fic_issuer_changed",
    coalesce(Esql.fic_old_subject, "") != coalesce(Esql.fic_new_subject, ""), "fic_subject_changed",
    "none"
  )
| where Esql.fic_change != "none"
// the raw old/new arrays stay on the alert under azure.*; drop the working copies and intermediates
| drop Esql.fic_old_value, Esql.fic_new_value, Esql.fic_object, Esql.fic_old_object, Esql.fic_old_id_pos
| keep @timestamp, Esql.*, azure.*, event.*, cloud.*, related.*, tags, source.*, agent.*, client.*, _id, _version, _index, data_stream.namespace