I just published a new Python script that I’ve dubbed Excel to Markdown to Github and wanted to share the details of the what and the why.

Using some of the PowerShell scripts I published on nexxai.dev last week, I found that I had a need to dump the results of some of these spreadsheets into a more accessible documentation repo. We use Confluence here and so the easy choice was to convert those worksheets to Markdown. However, when I looked around, I couldn’t find anything that would do what I needed without costing several thousand(!!!) dollars.

This script takes a single input (the path to your spreadsheet), will prompt you for the heading level to use for the title of the table, and will dump to the screen every worksheet in the workbook. You can then copy/paste this text into the documentation management system of your choice, and presto, you have an editable document that you and your teams can use.

I hope this help you if you ever have to convert Excel to Markdown!

Today I had to get all of the scopes and reservations from all activated DHCP servers in our Active Directory domain. I whipped up this PowerShell script and figured someone else out there might need it. Please note that the Import-Excel package is required if you plan on using this script verbatim. It can be easily modified to not use it, however this is left as an exercise for the reader to implement.

Get-DhcpServerInDC | ForEach-Object {
    $DHCPServer = $_
    $hostName = $DHCPServer.DnsName
    Write-Host $hostName

    $scopes = Get-DhcpServerv4Scope -ComputerName $DHCPServer.DnsName | Where-Object { $_.State -eq "Active" } | Select-Object -Property Name, ScopeId, SubnetMask, StartRange, EndRange
    $scopes | Export-Excel ".\DHCPScopes.xlsx" -WorkSheetname "$($hostName)-Scopes" -AutoSize -AutoFilter

    ForEach ($scope in $scopes) {
        $reservations = Get-DhcpServerv4Reservation -ComputerName $DHCPServer.DnsName -ScopeId $scope.ScopeId | Select-Object -Property ClientId, Description, IPAddress, Name
        $reservations | Export-Excel ".\DHCPScopes.xlsx" -WorkSheetname "$($hostName)-Reservations" -AutoSize -AutoFilter 
    }
}

Just a quick post here as I found myself needing to find out what conditional forwarders each domain controller in an Active Directory domain had configured. We have nearly a hundred domain controllers and so going manually one-by-one was simply not an option.

I whipped up this PowerShell script and figured that someone else out there might need something similar. It is parallelized (the number of $instances can be changed to do more/less parallel work) and then just dumps it to a CSV-ish file.

$instances = 10

Get-AdDomainController -Filter * | ForEach-Object -ThrottleLimit $instances -Parallel {
    $dc = $_
    Write-Host $dc.Name
    $zones = Get-DnsServerZone -ComputerName $dc.Name | Where-Object {$_.ZoneType -eq "Forwarder" }
    $string = $dc.Name + ","
     ForEach ($zone in $zones) {
         $string = $string + $zone.ZoneName + ","
     }
    Write-Host $string
    $string | Out-File -FilePath ".\zones.txt" -Append 
}

There’s a lot of room for improvement here obviously, but it should at least get you going.

Azure recently implemented a change to the API Management service whereby deleting the instance only puts it into a soft-deleted state rather than completely nuking it from orbit. This may be desirable for data recovery purposes but it means that if you run a terraform destroy on an environment with an APIM instance on it and then you try and rebuild that environment, it will fail due to the fact that the name you’re trying to use is being held onto by the previously removed instance. So since neither Azure CLI nor Az PowerShell natively support purging, I’m going to show you how to manually purge a soft-deleted Azure API Management instance.

NOTE: The below script uses the basic Az PowerShell tools but with a little elbow grease could be adapted to bash/zsh (provided you have a way of retrieving your Azure access token using OAuth).

$token = Get-AzAccessToken

$request = @{
    Method = 'DELETE'
    Uri    = "https://management.azure.com/subscriptions/{subscriptionGuid}/providers/Microsoft.ApiManagement/locations/{region}/deletedservices/{apimName}?api-version=2020-06-01-preview"
    Headers = @{
        Authorization = "Bearer $($token.Token)"
    }
}

Invoke-RestMethod @request

The only values you’ll need to supply are the subscriptionGuid, region, and apimName in the Uri.

Now the next time you’re stuck wondering why you can’t tear down and rebuild your environments with your IaC tool of choice, you’ll know how to purge a soft-deleted Azure API Management instance.

Source: Microsoft docs

The problem

We’ve recently begun attempting to scale our Azure App Services up and down for our test environments; scaling them up to match production performance levels (SKU: PremiumV2) during the day and then back down to minimal (SKU: Basic) at the end of the working day to save on costs. Just in the last month or two however, we’ve started to get the error “Cannot use the SKU Basic with File Change Audit for site XXX-XXX-XXX-XXX”.

Initially, we thought it had to do with the fact that we had a Diagnostic setting that was tracking AppServiceFileAuditLogs, but even after removing that Diagnostic setting before attempting the scale down, the issue persisted.

After banging our head against the walls with no progress being made, we opened a low-severity ticket with Azure Support to understand what was going on. They suggested we remove the following App Configuration settings:

• DIAGNOSTICS_AZUREBLOBCONTAINERSASURL
• DIAGNOSTICS_AZUREBLOBRETENTIONINDAYS
• DiagnosticServices_EXTENSION_VERSION
• WEBSITE_HTTPLOGGING_CONTAINER_URL
• WEBSITE_HTTPLOGGING_RETENTION_DAYS

Again, these did not have any effect.

It was at this time that I was in the portal browsing around for something else and happened to notice the “JSON View” option at the top right of the App Service so I checked it out and grep’d for audit just to see what I’d find.

Bingo: fileChangeAuditEnabled

Not so bingo: fileChangeAuditEnabled: null

But seeing that setting got me to thinking. What if there’s a bug in what JSON View is showing. The error we’re receiving is clearly saying it’s enabled, but the website is showing null; what if there’s some kind of type-mismatch going on behind the portal that is showing null but actually has a setting? What if we could use a different mechanism to test that theory?

Well, it just so happens that Azure PowerShell has a Get-AzResource function that can do just that and this blog post shows us how to do that.

The solution

First, let’s get the resource:

$appServiceRG = "example-resource-group"
$appServiceName = "example-app-service-name"
$config = Get-AzResource -ResourceGroupName $appServiceRG `
    -ResourceType "Microsoft.Web/sites/config" `
    -ResourceName "$($appServiceName)/web" `
    -apiVersion 2016-08-01

We now have an object in $config that we can now check the properties of by doing:

$config.Properties

And there it is:

fileChangeAuditEnabled                 : True

Now all we need to do is configure it to false (and also unset a property called ReservedInstanceCount which is a duplicate of preWarmedInstanceCount but cannot be included when we try and reset the other setting due to what I assume is Azure just keeping it around for legacy reasons):

$config.Properties.fileChangeAuditEnabled = "false"
$config.Properties.PSObject.Properties.Remove('ReservedInstanceCount')

Next, as per the suggestion from Parameswaran in the comments (thank you!), create a new Array (since existing arrays are of fixed size and cannot be modified) while removing AppServiceFileAuditLogs from the list of azureMonitorLogCategories

$newCategories = @()

ForEach ($entry in $config.Properties.azureMonitorLogCategories) {
    If ($entry -ne "AppServiceFileAuditLogs") {
        $newCategories += $entry
    }
}

$config.Properties.azureMonitorLogCategories = $newCategories

And finally, let’s set the setting:

$config | Set-AzResource -Force

Next, for any Deployment Slots you have on this resource, repeat these steps again, but using the following resource retrieval code:

$config = Get-AzResource -ResourceGroupName $appServiceRG `
    -ResourceType "Microsoft.Web/sites/slots" `
    -ResourceName "$($appServiceName)" `
    -apiVersion 2016-08-01

Now, when you try to scale down from a PremiumV2 SKU to a Basic SKU, you will no longer receive the error of “Cannot use the SKU Basic with File Change Audit for site XXX-XXX-XXX-XXX”.