Lesson Learned #495: Monitoring DNS Resolution with PowerShell of Azure SQL Server

Today, I worked on a service request where the DNS was providing an incorrect IP address randomly. In this article, I would like to share a PowerShell script that checks the DNS resolution every 5 seconds to help identify the issue.

This script resolves a DNS name using two methods (Resolve-DnsName and nslookup), logs the details, and saves the results in a CSV file for further analysis. This approach helps in identifying the DNS server responses and understanding the resolution process over time.

1. Resolve-DnsName - A PowerShell cmdlet that provides detailed information about DNS queries.
2. nslookup - A command-line utility that queries DNS servers and provides details about the resolution process, including the responding DNS server.

# Function to resolve the DNS name using Resolve-DnsName and nslookup, then save the details to a CSV file
$OutPutFolder="c:\DnsResolution" ##Folder where the data will be saved.
$outputFile = "dns_resolution_log.csv" ##file where the data will be saved.
$hostname = "servername.database.windows.net" ##name of the resource that we are going to check.

function Resolve-DNS {

    # Initialize an empty array to hold the data
    $results = @()

    # Resolve using Resolve-DnsName
    try {
        $dnsResult = Resolve-DnsName -Name $hostname -ErrorAction Stop
        if ($dnsResult) {
            foreach ($result in $dnsResult) {
                $results += [PSCustomObject]@{
                    Timestamp  = (Get-Date -Format "yyyy-MM-dd HH:mm:ss")
                    Method     = "Resolve-DnsName"
                    Hostname   = $result.Name
                    QueryType  = $result.QueryType
                    TimeToLive = $result.TimeToLive
                    IPAddress  = $result.IPAddress
                    DnsServer  = $null  # Not provided by Resolve-DnsName
                }
            }
        } else {
            logMsg "No results from Resolve-DnsName"
        }
    } catch {
        logMsg "Error resolving ${hostname} using Resolve-DnsName: $_"
    }

    # Resolve using nslookup
    try {
        $nslookupResult = nslookup $hostname
        $dnsServer = $null
        $ipAddresses = @()

        foreach ($line in $nslookupResult) {
            if ($line -match "^Server:") {
                $dnsServer = $line -replace "Server:", ""
                $dnsServer = $dnsServer.Trim()
            }
            if ($line -match "^Address:") {
                $ip = $line -replace "Address:", ""
                if ($ip -notmatch "[:#]") {  # Exclude IPv6 and port information
                    $ipAddresses += $ip.Trim()
                }
            }
        }

        if ($ipAddresses.Count -gt 0) {
            foreach ($ip in $ipAddresses) {
                $results += [PSCustomObject]@{
                    Timestamp  = (Get-Date -Format "yyyy-MM-dd HH:mm:ss")
                    Method     = "nslookup"
                    Hostname   = $hostname
                    QueryType  = $null  # Not provided by nslookup
                    TimeToLive = $null  # Not provided by nslookup
                    IPAddress  = $ip
                    DnsServer  = $dnsServer
                }
            }
        } else {
            logMsg "No IP addresses found in nslookup response"
        }
    } catch {
        logMsg "Error resolving ${hostname} using nslookup: $_"
    }

    # Save results to CSV
    if ($results.Count -gt 0) {
      try
      {
        if (-not (Test-Path $outputFile)) {
            $results | Export-Csv -Path $outputFile -NoTypeInformation
        } else {
            $results | Export-Csv -Path $outputFile -NoTypeInformation -Append -Force
        }
        logMsg "DNS resolution details saved to $outputFile"
      }
       catch {
        logMsg "not possible to save the output file $_"
    }
    }
}

#--------------------------------
# Log the operations
#--------------------------------
function logMsg {
    Param (
        [Parameter(Mandatory=$true, Position=0)]
        [string] $msg,
        [Parameter(Mandatory=$false, Position=1)]
        [int] $Color = 0,
        [Parameter(Mandatory=$false, Position=2)]
        [boolean] $Show = $true, 
        [Parameter(Mandatory=$false, Position=3)]
        [boolean] $bShowDate = $true
    )

    try {
        if ($bShowDate -eq $true) {
            $Fecha = Get-Date -format "yyyy-MM-dd HH:mm:ss"
            $msg = "$Fecha $msg"
        }

        if ($Show -eq $true) {
            switch ($Color) {
                1 { Write-Host -ForegroundColor Cyan $msg }
                2 { Write-Host -ForegroundColor White -BackgroundColor Red $msg }
                3 { Write-Host -ForegroundColor Yellow $msg }
                Default { Write-Host -ForegroundColor White $msg }
            }
        }
    } catch {
        Write-Host $msg
    }
}

#--------------------------------------------------------------
#Create a folder 
#--------------------------------------------------------------
Function CreateFolder
{ 
  Param( [Parameter(Mandatory)]$Folder ) 
  try
   {
    $FileExists = Test-Path $Folder
    if($FileExists -eq $False)
    {
     $result = New-Item $Folder -type directory 
     if($result -eq $null)
     {
       return $false
     }
    }
    return $true
   }
  catch
  {
   return $false
  }
 }

 function GiveMeFolderName([Parameter(Mandatory)]$FolderSalida)
{
  try
   {
    $Pos = $FolderSalida.Substring($FolderSalida.Length-1,1)
    If( $Pos -ne "\" )
     {return $FolderSalida + "\"}
    else
     {return $FolderSalida}
   }
  catch
  {
    return $FolderSalida
  }
}

#--------------------------------
#Validate Param
#--------------------------------
function TestEmpty($s)
{
if ([string]::IsNullOrWhitespace($s))
  {
    return $true;
  }
else
  {
    return $false;
  }
}

clear

If( TestEmpty($OutPutFolder) )
{
  write-host "Output folder is empty"
  exit;
}

If( TestEmpty($outputFile) )
{
  write-host "Output file is empty"
  exit;
 }

If( TestEmpty($hostname) )
{
  write-host "HostName is empty"
  exit;
}

$result = CreateFolder($OutPutFolder) #Creating the folder that we are going to have the results, log and zip.
If( $result -eq $false)
 { 
  write-host "Was not possible to create the folder"
  exit;
 }

$DateFormat = Get-Date -format "yyyy-MM-dd_HHmmss"
$OutPutFolder = GiveMeFolderName($OutPutFolder) #Creating a correct folder adding at the end \.
$outputFile = $OutPutFolder + $outputFile + "_" + $DateFormat 

# Run the function every 5 seconds
while ($true) {
    Resolve-DNS
    Start-Sleep -Seconds 5
}

Example using Private Endpoint

Example using Public Endpoint

Disclaimer

The use of this application and the provided scripts is intended for educational and informational purposes only. The scripts and methods demonstrated in this guide are provided "as is" without any warranties or guarantees. It is the user's responsibility to ensure the accuracy, reliability, and suitability of these tools for their specific needs.