Azure Service I Ps

Query

let AzurePublicIPs = externaldata(
    changeNumber: string,
    cloud: string,
    values: dynamic, // 'values' should be of type dynamic to hold JSON objects
    name: string,
    id: string,
    properties: dynamic, // 'properties' should also be dynamic if it contains nested JSON
    changenumber2: string,
    region: string,
    regionId: string,
    platform: string,
    systemService: string,
    addressPrefixes: dynamic, // 'addressPrefixes' should be of type dynamic if it's an array
    networkFeatures: dynamic) // 'networkFeatures' should be of type dynamic if it's an array or nested JSON
[@"https://download.microsoft.com/download/7/1/D/71D86715-5596-4529-9B13-DA13A5DE5B63/ServiceTags_Public_20240422.json"] with (format="MultiJSON", ingestionMapping='[{"Column":"changeNumber","Properties":{"Path":"$.changeNumber"}},{"Column":"cloud","Properties":{"Path":"$.cloud"}},{"Column":"values","Properties":{"Path":"$.values"}},{"Column":"name","Properties":{"Path":"$.values.name"}},{"Column":"id","Properties":{"Path":"$.values.id"}},{"Column":"properties","Properties":{"Path":"$.values.properties"}},{"Column":"changenumber2","Properties":{"Path":"$.values.properties.changeNumber"}},{"Column":"region","Properties":{"Path":"$.values.properties.region"}},{"Column":"regionId","Properties":{"Path":"$.values.properties.regionId"}},{"Column":"platform","Properties":{"Path":"$.values.properties.platform"}},{"Column":"systemService","Properties":{"Path":"$.values.properties.systemService"}},{"Column":"addressPrefixes","Properties":{"Path":"$.values.properties.addressPrefixes"}},{"Column":"networkFeatures","Properties":{"Path":"$.values.properties.networkFeatures"}}]'); // Ensure this line ends with a single quote
AzurePublicIPs
| mv-expand values to typeof(dynamic)
| extend
    valueName = values.name,
    valueId = values.id,
    valueChangeNumber = values.properties.changeNumber,
    valueRegion = values.properties.region,
    valueRegionId = values.properties.regionId,
    valuePlatform = values.properties.platform,
    valueSystemService = values.properties.systemService,
    valueAddressPrefixes = values.properties.addressPrefixes,
    valueNetworkFeatures = values.properties.networkFeatures
| project
    cloud,
    changeNumber,
    valueName,
    valueId,
    valueChangeNumber,
    valueRegion,
    valueRegionId,
    valuePlatform,
    valueSystemService,
    valueAddressPrefixes,
    valueNetworkFeatures

Explanation

This query is designed to extract and process data from a JSON file containing Azure public IP address information. Here's a simplified explanation of what it does:

Data Source: It pulls data from an external JSON file hosted at a specific URL. This file contains information about Azure's public IP addresses.
Data Structure: The data is structured in a way that includes various attributes like changeNumber, cloud, values, and more. Some of these attributes, such as values, properties, addressPrefixes, and networkFeatures, are dynamic, meaning they can hold complex or nested JSON objects.
Data Extraction: The query uses an externaldata function to specify the structure of the data and how it should be ingested. It maps JSON paths to specific columns in the dataset.
Data Expansion: The mv-expand operator is used to expand the values array, allowing each element within it to be processed individually.
Data Transformation: The extend operator is used to create new columns by extracting specific properties from the expanded values data, such as name, id, changeNumber, region, etc.
Data Projection: Finally, the project operator is used to select and display specific columns of interest, including cloud, changeNumber, and various properties extracted from the values.

In summary, this query retrieves and processes Azure public IP data from a JSON file, expanding nested structures and extracting specific attributes for analysis or reporting.

Details

Rod Trent

Released: April 25, 2024

Tables

AzurePublicIPs

Keywords

AzurePublicIPsCloudValuesNameIdPropertiesRegionPlatformSystemServiceAddressPrefixesNetworkFeatures

Operators

letexternaldatawithmv-expandextendproject

Actions

GitHub