Extracting values from JSON column using KQL (Azure Data Explorer)

user_10964894 0
Viewed 1823

Can you please tell me how to extract values of category, enabled and categoryGroup from the below JSON column in KQL(Azure Data Explorer).

Below JSON value is exactly what I see in the column called "Logs". I see that the Column Logs is defined as string datatype in table

AzLogsCoverage 
| extend Logs = case(isnull(Logs) or isempty(Logs), 'N/A', Logs)
| where Logs <> 'N/A'
| project Logs
| extend LogsCategory = parse_json(Logs).category


[
  {
    "category": "Administrative",
    "enabled": true,
    "categoryGroup": null
  },
  {
    "category": "Security",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "ServiceHealth",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Alert",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Recommendation",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Policy",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Autoscale",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "ResourceHealth",
    "enabled": false,
    "categoryGroup": null
  }
]

kql azure-data-explorer
2 Answers

if the input is of type string, you first need to invoke parse_json() on it, to make it of type dynamic.

Then, you can use mv-expand/mv-apply to expand elements in the array, and then you can explicitly project properties of interest for each element.

for example:

print input = ```[
  {
    "category": "Administrative",
    "enabled": true,
    "categoryGroup": null
  },
  {
    "category": "Security",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "ServiceHealth",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Alert",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Recommendation",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Policy",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Autoscale",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "ResourceHealth",
    "enabled": false,
    "categoryGroup": null
  }
]```
| extend d = parse_json(input)
| mv-apply d on (
    project Category = tostring(d.category),
            Enabled = tobool(d.enabled),
            CategoryGroup = tostring(d.categoryGroup)
)
| project-away input
Category Enabled CategoryGroup
Administrative True
Security False
ServiceHealth False
Alert False
Recommendation False
Policy False
Autoscale False
ResourceHealth False
user_78618070 

AzLogsCoverage
| extend Logs = case(isnull(Logs) or isempty(Logs), 'N/A', Logs)
| extend Metrics = case(isnull(Metrics) or isempty(Metrics), 'N/A', Metrics)
| where Logs <> 'N/A'
| extend LogsDynamic = todynamic(Logs)
| extend MetricsDynamics = todynamic(Metrics)
| mv-expand LogsDynamic, MetricsDynamics
| project SubscriptionId, ResourceId, ResourceName, ResourceType, DiagnosticSettingStatus, DiagnosticSettingId, DiagnosticSettingName, DiagnosticSettingType, LAworkspaceId, LAworkspaceRetentionPeriod,   
LogsDynamic.category,LogsDynamic.enabled,LogsDynamic.categoryGroup, MetricsDynamics.category, MetricsDynamics.categoryGroup, MetricsDynamics.enabled, MetricsDynamics.retentionPolicy.enabled , MetricsDynamics.retentionPolicy.days

user_109648940
Related