the msg_s column does not exist in table AzureDiagnostics, many queries for the workbook need to be updated

[anranwuyan]

the msg_s column does not exist in table AzureDiagnostics for the (Azure Firewall Workbook), many queries for the workbook need to be updated.

sample one:

let materializedData =
materialize(
AzureDiagnostics
| where Category == "AzureFirewallApplicationRule"
| where Resource in~ (split("{Resource:label}", ", "))
| project msg_s, Resource, TimeGenerated);
union
(
materializedData
| where msg_s has "Web Category:" and msg_s has ". Url"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort " to " FQDN ":" DestinationPort ". Url:" Url ". Action: " Action ". Rule Collection:" RuleCollection ". Rule:" Rule ". Web Category:" WebCategory
),
(
materializedData
| where msg_s !has "Web Category:" and msg_s has ". Url" and msg_s has ". No rule matched"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort " to " FQDN ":" DestinationPort ". Url:" Url ". Action: " Action ". No rule matched" *
),
(
materializedData
| where msg_s !has "Web Category:" and msg_s !has ". Url" and msg_s has ". No rule matched"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort " to " FQDN ":" DestinationPort ". Action: " Action ". No rule matched" *
),
(
materializedData
| where msg_s has "Web Category:" and msg_s !has ". Url"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort " to " FQDN ":" DestinationPort ". Action: " Action ". Rule Collection:" RuleCollection ". Rule:" Rule ". Web Category:" WebCategory
),
(
materializedData
| where msg_s !has "Web Category:" and msg_s !has ". Url" and msg_s !has "Rule Collection" and msg_s !has " Reason: "
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort " to " FQDN ":" DestinationPort ". Action: " Action ". " RuleCollection ". " Rule
),
(
materializedData
| where msg_s !has "Web Category:" and msg_s !has ". Url" and msg_s !has "Rule Collection" and msg_s !has "TLS extension was missing"
| where msg_s has " Reason:"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort ". Action: " Action ". Reason: " Rule "."
),
(
materializedData
| where msg_s !has "Web Category:" and msg_s !has ". Url" and msg_s !has "TLS extension was missing" and msg_s !has "No rule matched"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort " to " FQDN ":" DestinationPort ". Action: " Action ". Rule Collection: " RuleCollection ". Rule: " Rule
),
(
materializedData
| where msg_s !has "Web Category:" and msg_s !has ". Url" and msg_s !has "Rule Collection" and msg_s !has " Reason: "
| where msg_s has "Rule Collection Group"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort " to " FQDN ":" DestinationPort ". Action: " Action ". Policy:" Policy ". Rule Collection Group:" RuleCollectionGroup ". Rule Collection: " RuleCollection ". Rule: " Rule
)
| where RuleCollection matches regex ".*"
| summarize Count = count(), last_log = datetime_diff("second", now(), max(TimeGenerated)) by RuleCollection, Rule, WebCategory

[tobystic]

@LeoYuAtMicrosoft Thank you for the feedback. We will get back to you in 36 hours upon review.

[iranogajec]

+1 on this, the workbook is not usable with resource specific log analytics diagnostic log streaming. Workbook is usable only when Azure Firewall is configured to stream diagnostic logs to the AzureDiagnostics table.

[gumoden]

Hi @LeoYuAtMicrosoft and @iranogajec, for resource specific logs there's another workbook available here.

Also, if you are using Resource Specific Logs, we now have the workbook available for Azure Firewall directly on Azure Portal, so you don't need to deploy it.