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!
Good Workaround! 