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.
Resolve-DnsName – A PowerShell cmdlet that provides detailed information about DNS queries.
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.
Microsoft Tech Community – Latest Blogs –Read More