Extracting Azure Logs from a Storage Account

 I was recently asked to look at the logs for an Azure Key Vault to extract all of the IP addresses that had been connecting to it.

This would have been a simple task if the logs were in a Log Analytics Workspace but they had been configured to go into a Azure Storage Account.

The problem with having the data in an Azure Storage Account is that it isn't structured in way that makes it easy to get at the data due to the number of folders:

Within each file the data isn't formatted as json, each row is a json row but the file itself isn't which means that we can't read in the entire file:

{ "time": "2024-10-31T16:04:55.4510338Z", "category": "AuditEvent", "operationName": "VaultGet", "resultType": "Success", "correlationId": "abcdef12-ab12-34cd-1234-abcdef123456", "callerIpAddress": "1.1.1.1", "identity": {"claim":{"http://schemas.microsoft.com/identity/claims/objectidentifier":"ea1ad6fd-e8e9-41d3-b6c8-f062fd2d36fb","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn":"live.com#[email protected]","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name":"live.com#xxxx.xx.xx","appid":"abcdef12-ab12-34cd-1234-abcdef123456"}}, "properties": {"id":"https://grussexamplekv.vault.azure.net/","clientInfo":"Mozilla/5.0","requestUri":"https://management.azure.com/subscriptions/abcdef12-ab12-34cd-1234-abcdef123456/resourceGroups/grussBlog01/providers/Microsoft.KeyVault/vaults/GrussExampleKV?api-version=2018-02-14","httpStatusCode":200,"properties":{"sku":{"Family":"A","Name":"Standard","Capacity":null},"tenantId":"abcdef12-ab12-34cd-1234-abcdef123456","networkAcls":{"bypass":"None","defaultAction":"Allow"},"enabledForDeployment":false,"enabledForDiskEncryption":false,"enabledForTemplateDeployment":false,"enableSoftDelete":true,"softDeleteRetentionInDays":7,"enableRbacAuthorization":true,"enablePurgeProtection":null}}, "resourceId": "/SUBSCRIPTIONS/abcdef12-ab12-34cd-1234-abcdef123456/RESOURCEGROUPS/GRUSSBLOG01/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/GRUSSEXAMPLEKV", "operationVersion": "2018-02-14", "resultSignature": "OK", "durationMs": "32"}
{ "time": "2024-10-31T16:15:03.5039974Z", "category": "AuditEvent", "operationName": "VaultGet", "resultType": "Success", "correlationId": "abcdef12-ab12-34cd-1234-abcdef123456", "callerIpAddress": "1.1.1.1", "identity": {"claim":{"http://schemas.microsoft.com/identity/claims/objectidentifier":"ea1ad6fd-e8e9-41d3-b6c8-f062fd2d36fb","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn":"live.com#xxxx.xx.xx","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name":"live.com#xxxx.xx.xx","appid":"abcdef12-ab12-34cd-1234-abcdef123456"}}, "properties": {"id":"https://grussexamplekv.vault.azure.net/","clientInfo":"python/3.6.6","requestUri":"https://management.azure.com/subscriptions/abcdef12-ab12-34cd-1234-abcdef123456/resourceGroups/grussBlog01/providers/Microsoft.KeyVault/vaults/GrussExampleKV?api-version=2019-09-01","httpStatusCode":200,"properties":{"sku":{"Family":"A","Name":"Standard","Capacity":null},"tenantId":"abcdef12-ab12-34cd-1234-abcdef123456","networkAcls":{"bypass":"None","defaultAction":"Allow"},"enabledForDeployment":false,"enabledForDiskEncryption":false,"enabledForTemplateDeployment":false,"enableSoftDelete":true,"softDeleteRetentionInDays":7,"enableRbacAuthorization":true,"enablePurgeProtection":null}}, "resourceId": "/SUBSCRIPTIONS/abcdef12-ab12-34cd-1234-abcdef123456/RESOURCEGROUPS/GRUSSBLOG01/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/GRUSSEXAMPLEKV", "operationVersion": "2019-09-01", "resultSignature": "OK", "durationMs": "51"}
{ "time": "2024-10-31T16:04:49.1252392Z", "category": "AuditEvent", "operationName": "VaultGet", "resultType": "Success", "correlationId": "abcdef12-ab12-34cd-1234-abcdef123456", "callerIpAddress": "1.1.1.1", "identity": {"claim":{"http://schemas.microsoft.com/identity/claims/objectidentifier":"ea1ad6fd-e8e9-41d3-b6c8-f062fd2d36fb","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn":"live.com#xxxx.xx.xx","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name":"live.com#xxxx.xx.xx","appid":"abcdef12-ab12-34cd-1234-abcdef123456"}}, "properties": {"id":"https://grussexamplekv.vault.azure.net/","clientInfo":"Mozilla/5.0","requestUri":"https://management.azure.com/subscriptions/abcdef12-ab12-34cd-1234-abcdef123456/resourceGroups/grussBlog01/providers/Microsoft.KeyVault/vaults/GrussExampleKV?api-version=2023-08-01-preview","httpStatusCode":200,"properties":{"sku":{"Family":"A","Name":"Standard","Capacity":null},"tenantId":"abcdef12-ab12-34cd-1234-abcdef123456","networkAcls":{"bypass":"None","defaultAction":"Allow"},"enabledForDeployment":false,"enabledForDiskEncryption":false,"enabledForTemplateDeployment":false,"enableSoftDelete":true,"softDeleteRetentionInDays":7,"enableRbacAuthorization":true,"enablePurgeProtection":null}}, "resourceId": "/SUBSCRIPTIONS/abcdef12-ab12-34cd-1234-abcdef123456/RESOURCEGROUPS/GRUSSBLOG01/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/GRUSSEXAMPLEKV", "operationVersion": "2023-08-01-preview", "resultSignature": "OK", "durationMs": "38"}
{ "time": "2024-10-31T16:04:44.1125905Z", "category": "AuditEvent", "operationName": "VaultGet", "resultType": "Success", "correlationId": "abcdef12-ab12-34cd-1234-abcdef123456", "callerIpAddress": "1.1.1.1", "identity": {"claim":{"http://schemas.microsoft.com/identity/claims/objectidentifier":"ea1ad6fd-e8e9-41d3-b6c8-f062fd2d36fb","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn":"live.com#xxxx.xx.xx","http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name":"live.com#xxxx.xx.xx","appid":"abcdef12-ab12-34cd-1234-abcdef123456"}}, "properties": {"id":"https://grussexamplekv.vault.azure.net/","clientInfo":"Mozilla/5.0","requestUri":"https://management.azure.com/subscriptions/abcdef12-ab12-34cd-1234-abcdef123456/resourceGroups/grussBlog01/providers/Microsoft.KeyVault/vaults/GrussExampleKV?api-version=2023-08-01-preview","httpStatusCode":200,"properties":{"sku":{"Family":"A","Name":"Standard","Capacity":null},"tenantId":"abcdef12-ab12-34cd-1234-abcdef123456","networkAcls":{"bypass":"None","defaultAction":"Allow"},"enabledForDeployment":false,"enabledForDiskEncryption":false,"enabledForTemplateDeployment":false,"enableSoftDelete":true,"softDeleteRetentionInDays":7,"enableRbacAuthorization":true,"enablePurgeProtection":null}}, "resourceId": "/SUBSCRIPTIONS/abcdef12-ab12-34cd-1234-abcdef123456/RESOURCEGROUPS/GRUSSBLOG01/PROVIDERS/MICROSOFT.KEYVAULT/VAULTS/GRUSSEXAMPLEKV", "operationVersion": "2023-08-01-preview", "resultSignature": "OK", "durationMs": "30"}

To extract the callerIpAddress meant that a script was required to navigate through the folders (recursively) then read each file and extract the information we needed.

$logsDirectory = '<< location of directory >>'

# Loop through each folder and output the full path of any *.json files.
Function Get-JsonFile {
    Param ($jsonDirectory)
    
    Get-ChildItem -Path $jsonDirectory | ForEach-Object {
        # Write-Output $_.Name
        If ($_.Name -like "*.json") {
            $_.FullName
        } ElseIf ($_.PSIsContainer) {
            Get-JsonFile -jsonDirectory $_.FullName
        } 
    }
}

# Treat each row as a json document and output the callerIpAddress
Function ExtractIPFromJsonFile {
    Param ($jsonFileName)

    $jsonFile = Get-Content $jsonFileName
    ForEach ($row in $jsonFile) {
        $jsonRow = $row | ConvertFrom-Json
        Write-Output $jsonRow.callerIpAddress
    }
}

# Get a list of all of the files using the above function
$files = Get-JsonFile $logsDirectory
ForEach ($file in $files) {
    $IPs = ExtractIPFromJsonFile -jsonFileName $file
    # Add the IP addresses found to a file.
    Add-Content -path "$logsDirectory/IPs.txt" -Value $IPs
}


# Now all IPs are written to file
# read it back in and get distinct IPs (and counts)
$allIPs = Get-Content -path "$logsDirectory/IPs.txt" | Group-Object
$allIPs | ForEach-Object {
    [PSCustomObject]@{
        Line = $_.Name
        Count = $_.Count
    }
} | Sort-Object -Property Count -Descending

    This will also output the IP addresses with the number of times they have connected to the KeyVault. Note: This doesn't check to see if the requests were successful or not! Not ground breaking but might be useful for someone (or me!) in the future if you need to dive into logs stored in a storage account.