Introduction
If you go into the Azure Sentinel Logs page you can get a listing of all the tables that you have as well as the individual columns that make up the tables. Wouldn’t it be great if you could export that list into a CSV file? With the use of Azure Sentinel’s REST API and some PowerShell you can!
Getting the Data
First of all, if you have not used Azure Sentinel’s REST API before, take a look at my blog post Your first Azure Sentinel REST API call to learn how.
We are going to be using a different URL than normal. The one we will be using here is:
"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/{operationalInsightsResourceProvider}/workspaces/{workspaceName}/api/metadata?api-version=2017-01-01-preview"
making all the need changes as required. I didn’t find this with the other REST API calls that Microsoft published. I used Edge’s Programmer’s Tools to record the calls being made when the Azure Sentinel Logs page was loaded and figured it out from there.
We are also going to make a slightly different call to get the information. The call we will be making is
$results = (Invoke-RestMethod -Method "Get" -Uri $url -Headers $authHeader ).tables
This will get us a listing of all the tables in your Azure Sentinel environment. There are some other top level entries other than tables but I am going to ignore those since they just show how the tables will appear when sorted in the Logs page and the functions that are part of your environment.
Once we get the $results back it is just a matter of iterating through all the tables, outputting its name, and then iterating through all of the table’s columns and displaying the name, description (if available), and datatype.
This is just a basic PowerShell program. It is not a full-fledged program that accepts inputs but it should give you a good idea of how it works.
$context = Get-AzContext
$profile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$profileClient = New-Object -TypeName Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient -ArgumentList ($profile)
$token = $profileClient.AcquireAccessToken($context.Subscription.TenantId)
$authHeader = @{
'Content-Type' = 'application/json'
'Authorization' = 'Bearer ' + $token.AccessToken
}
$filename = "./AzureSentinelLogs.csv"
$url = "https://management.azure.com/subscriptions/7ed1d5e8-b30e-4205-8b0f-629cb7daa671/resourceGroups/RG-Sentinel-Beta/providers/Microsoft.OperationalInsights/workspaces/LA-Sentinel-Beta/api/metadata?api-version=2017-01-01-preview"
$results = (Invoke-RestMethod -Method "Get" -Uri $url -Headers $authHeader ).tables
#There is a Description field for the table so you can add it in later
$output = "Name,Description,Column Name, Column Description, Column Type"
$output >> $filename
#Iterate through all the tables in the file
foreach ($table in $results) {
$output = $table.name + ","
$output >> $filename
foreach ($column in $table.columns | Sort-Object -Property name) {
#Column names starting with an underscore do not show up in the Logs
#listing so I am skipping them here as well.
if (!$column.name.StartsWith("_")) {
#The two commas in the beginning are to indent the column information to match the header
$output = ",," + $column.name + "," + $column.description + "," + $column.type
$output >> $filename
}
}
}
Summary
That is all there is to it! Would be nice to have a description for all the columns, not to mention the tables, but it is a start. The funny thing is when I was looking at the output of the call in Edge’s Programmer’s Tools it does show some of the tables having a description field but it doesn’t show up when I make my call. Oh well. 🙂
Update: You can look at this page to get information about which Data connector writes to which tables. You can add a column for this information. https://docs.microsoft.com/en-us/azure/sentinel/connect-data-sources