Microsoft Sentinel, error in EntityMappings: The given column does not exist

Long time, no blogging. Been busy starting a company. Working a lot with Microsoft Sentinel lately, so lots of content will be coming surrounding that. This time, we’ll do a short one:

Tables in log analytics are weird. Or, at least they can cause headaches because the available columns vary based on what content you ingest.

I reached an issue when creating scheduled alert rules / analytics rules (using the azurerm_sentinel_alert_rule_scheduled Terraform resource, but the method should be irrelevant), where my KQL was refering to a column called “PublicIPAddress”. This column is at least used when populating Azure DDoS Protection Standard logs to log analytics, but this environment did not have that. One of the entities was mapping that PublicIPAddress column to an IP entity, as follows:

resource "azurerm_sentinel_alert_rule_scheduled" "ar" {
    name                       = "ed38f228-0c0f-41ef-9078-54465b2b1589"
    display_name               = "Azure DDoS Protection Detected an attack"
    severity                   = "High"
    description                = "The Azure DDoS Protection Standard service has detected an active attack towards a public IP address."

    event_grouping {
        aggregation_method = "AlertPerResult"
    }

    incident_configuration {
        create_incident = true
        grouping {
            enabled                = true
            entity_matching_method = "AllEntities"
            lookback_duration      = "P1D"
        }
    }

    query_period      = "PT5M"
    query_frequency   = "PT5M"
    trigger_operator  = "GreaterThan"
    trigger_threshold = 0
    tactics           = [
        "Impact",
    ]

    entity_mapping {
        entity_type = "IP"
        field_mapping {
            column_name = "PublicIPAddress"
            identifier  = "Address"
        }
    }
    entity_mapping {
        entity_type = "AzureResource"
        field_mapping {
            column_name = "_ResourceId"
            identifier  = "ResourceId"
        }
    }


    query = <<QUERY
AzureDiagnostics
| where Category == "DDoSProtectionNotifications"
    QUERY

    log_analytics_workspace_id = var.log_analytics_workspace_id
}

The error I got was the following:

Error in EntityMappings: The given column ‘PublicIPAddress’ does not exist

What is happening is that the reference to PublicIPAddress in the IP entity must refer to a valid column. However, I am deploying the same configuration to loads of Sentinel instances, and want to use the same code, so I needed a solution.

In order to fix this issue, the column_ifexists function is very useful, as it will replace your error when a default value instead. So with a simple fix to our code, using extend to add an additional column IPCustomEntity with the empty string as default value, and using that column in the entity mapping, everything worked just fine:

resource "azurerm_sentinel_alert_rule_scheduled" "ar" {
    name                       = "ed38f228-0c0f-41ef-9078-54465b2b1589"
    display_name               = "Azure DDoS Protection Detected an attack"
    severity                   = "High"
    description                = "The Azure DDoS Protection Standard service has detected an active attack towards a public IP address."

    event_grouping {
        aggregation_method = "AlertPerResult"
    }

    incident_configuration {
        create_incident = true
        grouping {
            enabled                = true
            entity_matching_method = "AllEntities"
            lookback_duration      = "P1D"
        }
    }

    query_period      = "PT5M"
    query_frequency   = "PT5M"
    trigger_operator  = "GreaterThan"
    trigger_threshold = 0
    tactics           = [
        "Impact",
    ]

    entity_mapping {
        entity_type = "IP"
        field_mapping {
            column_name = "IPCustomEntity"
            identifier  = "Address"
        }
    }
    entity_mapping {
        entity_type = "AzureResource"
        field_mapping {
            column_name = "_ResourceId"
            identifier  = "ResourceId"
        }
    }


    query = <<QUERY
AzureDiagnostics
| where Category == "DDoSProtectionNotifications"
| extend IPCustomEntity = column_ifexists("PublicIPAddress", "")
    QUERY

    log_analytics_workspace_id = var.log_analytics_workspace_id
}

Hope it saves someone some time at some point!

Terraform module for automatically maintaining Azure DevOps variable group with app secret

Dropping a quick Terraform module that automatically rotates the password of an Azure AD application, outputting the value into an Azure DevOps variable group. This can be super handy when maintaining a tenant by code, allowing developers to “order” app secrets.

Continue reading “Terraform module for automatically maintaining Azure DevOps variable group with app secret”

Digging into Azure AD Certificate-Based Authentication

Azure AD Certificate-Based Authentication is now in public preview, with a surprisingly good documentation. Usually I have to guess how 50% of a feature actually works, but this time they have gone all-in with technical details of just about everything. What is a blogger to do? Well, let’s configure it and see if we can sneak a peek behind the scenes 🙂

Illustration with steps about how Azure AD certificate-based authentication works.
Continue reading “Digging into Azure AD Certificate-Based Authentication”

Checking out Azure AD cross tenant access policies

So, as one does, I was checking out the different Microsoft Graph AppRoles, which are the application scopes available. And then I found this:

Now, I now cross tenant access is something Microsoft has been working on for a while, and I have seen some preview stuff presented, but I have no access to any preview at all at this point. However, let’s see what we can find!

Disclaimer: Do not use this in production, as it is only a private preview feature I discovered.

Continue reading “Checking out Azure AD cross tenant access policies”

Another deep dive into Azure AD Workload Identity Federation using GitHub actions

I just had a previous blogpost about Workload Identity Federation, where I went into the details of how authentication works. This time, I want to use GitHub actions, which is the currently supported method. There are some limitations to the documentation today, but hopefully we will be able to do things like accessing KeyVaults and other services, not only using Azure CLI.

Continue reading “Another deep dive into Azure AD Workload Identity Federation using GitHub actions”

A deep dive into Azure AD Workload identity federation

Workload Identity Federation is a rather new concept in Azure AD, where service principals do not have keys in a directory, but in stead is federated to an external OpenID Connect (OIDC) provider, such as Okta, Ping, Github, GCP, AWS and – well – Azure AD.

A part of an earlier blogpost used a JWT in a client credential grant, signed by a KeyVault based certificate, to authenticate as an application. This time, this JWT will not be signed by a certificate, but instead by an OIDC provider.

Continue reading “A deep dive into Azure AD Workload identity federation”

Script to create named locations for Azure regions

Just built a quick little script that creates named locations for conditional access based on the IP addresses provided and updated by Microsoft, through a json file.

By using this script to manage named locations, you can, through the workload identities conditional access policies currently in preview, limit where your service principals can be used from. You can say things like “These service principals can only be used from Azure region West Europe”.

Find it here in my GitHub!