sql-scripts/TPDT-676 - monitor connectivity/tpdt-676 - test from pharmacy to pos2.ps1

# Define the target SMB share and output CSV file
$targetMachine = ""
$targetShare = ""  # Change this to the UNC path of your SMB share
$tcpPort = 1433  # Specify the port number to test (e.g., HTTP = 80, HTTPS = 443)
$rootDrive = "U:\"  #root drive where files will be stored
$subFolder = "TPDT-676" #subfolder where all files will be stored on the $rootDrive

#ensure we have a working drive
if(-not(Test-Path -Path $rootDrive)){
    $rootDrive = "D:\"
}

#create the destination folder if missing
$dest = "$rootDrive\$subFolder".Replace("\\","\")
if (-not (Test-Path -Path $dest)) {
    New-Item -ItemType Directory -Path $dest
}

$outputCsvLAT = "$dest\tpdt-676-POS2.csv" #name of the csv file with the results
$logFile = "$dest\tpdt-676-POS2.log"   #place of log file (for debugging sql job)

$maxFiles = 3       #how many csv file we keep after doing a rotation
$maxFileSizeMB = 10 #What is the max size of a csv file before we do a rotation in MB
$testCount = 5      # Number of connection attempts per iteration 

# Function to check the size of the CSV file
function Check-CsvFileSize {
    param ($filePath)
    
    if (Test-Path $filePath) {
        $fileSizeMB = (Get-Item $filePath).length / 1MB
        return $fileSizeMB
    } else {
        return 0
    }
}

# Function to rotate files
function Rotate-CsvFile {
    param ($filePath, $rotationPath, $maxFiles)
    
    #trailing backslash needed for concatenation of the path
    if (-not $rotationPath.EndsWith("\")) {
        $rotationPath += "\"
    }

    # Create rotation directory if it doesn''t exist
    if (-not (Test-Path $rotationPath)) {
        New-Item -ItemType Directory -Path $rotationPath
    }

    # Get the current timestamp to append to the rotated filename
    $timestamp = Get-Date -Format "yyyyMMdd-HHmmss"
    $fileName = [System.IO.Path]::GetFileNameWithoutExtension($filePath)
    $fileExtension = [System.IO.Path]::GetExtension($filePath)

    # Generate new rotated filename
    $rotatedFileName = "$rotationPath$fileName-$timestamp$fileExtension"
    
    # Rotate the file
    Rename-Item -Path $filePath -NewName $rotatedFileName

    # Get all rotated files sorted by creation time
    $rotatedFiles = Get-ChildItem -Path $rotationPath -Filter "$fileName-*$fileExtension" | Sort-Object LastWriteTime -Descending

    # Keep only the most recent 3 files
    $filesToDelete = $rotatedFiles | Select-Object -Skip $maxFiles
    foreach ($file in $filesToDelete) {
        Remove-Item -Path $file.FullName
    }
}

function write-log {
    param(
        [Parameter(ValueFromPipeline = $true)]
        [string]$msg,
        [string]$log = $logFile
    )

    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss.fff"
    $line = "$timestamp`t$msg"

    Add-Content -Path $log -Value $line
    write-host $line
}

#Get the replini-backup URI and hostname
function get-target {
    $shareDS = Invoke-Sqlcmd -ServerInstance "(local)" -Database master -Query @"
SELECT SettingValue AS backupSrc, SettingId
FROM ActiveSystemServer.cfg.Settings
WHERE SettingId LIKE 'Values.Modules.Replication.DbInitializationBackupPath%'
AND LEN(SettingValue) > 1;
"@
    $shareValue = $shareDS.backupSrc.Trim()
    $machineName = ($shareValue -split "\\") | Where-Object { $_ -ne "" } | Select-Object -First 1
    $result = [PSCustomObject]@{
        smbShare    = "$shareValue"
        machineName = "$machineName"
    }

    return $result
}

# Function to perform SMB share reachability test
function Test-SmbShareAccessibility {
    param(
        [string]$sharePath,
        [int]$count
    )
    
    $successfulConnections = 0
    $latencyTimes = @()
    write-log "to check: $sharePath"

    for ($i = 1; $i -le $count; $i++) {
        # Measure the time taken to check share accessibility
        $startTime = [DateTime]::Now

        # Check if the SMB share is accessible using Test-Path
        if (Test-Path -Path $sharePath) {
            $successfulConnections++
            $latencyTimes += (([DateTime]::Now) - $startTime).TotalMilliseconds
        }
        else {
            # Log failed connection as N/A latency
            $latencyTimes += "N/A"
        }

        Start-Sleep -Milliseconds 500  # Slight delay between attempts
    }

    # Calculate packet loss percentage
    $packetLoss = (($count - $successfulConnections) / $count) * 100

    # Calculate average latency excluding N/A entries
    $avgLatency = ($latencyTimes | Where-Object { $_ -ne "N/A" } | Measure-Object -Average).Average

    return [pscustomobject]@{
        Timestamp  = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
        SMBShare   = $sharePath
        PacketLoss = "$packetLoss"
        AvgLatency = if ($null -ne $avgLatency) { $avgLatency } else { "N/A" }
    }
}

# Function to perform TCP latency and packet loss test
function Test-TcpLatencyAndPacketLoss {
    param(
        [string]$target,
        [int]$port,
        [int]$count
    )
    
    $successfulConnections = 0
    $latencyTimes = @()

    for ($i = 1; $i -le $count; $i++) {
        # Perform TCP connection test using Test-NetConnection
        $tcpTest = Test-NetConnection -ComputerName $target -Port $port -InformationLevel Quiet 
        
        if ($tcpTest) {
            $successfulConnections++
            # Simulate latency as the connection time (since Test-NetConnection does not give latency directly)
            $connectionStart = [DateTime]::Now
            $tcpTest = Test-NetConnection -ComputerName $target -Port $port -InformationLevel Quiet
            $connectionEnd = [DateTime]::Now
            $latency = ($connectionEnd - $connectionStart).TotalMilliseconds
            $latencyTimes += $latency
        }
        else {
            # Log failed connection as N/A latency
            $latencyTimes += "N/A"
        }

        Start-Sleep -Milliseconds 500  # Slight delay between attempts
    }

    # Calculate packet loss percentage
    $packetLoss = (($count - $successfulConnections) / $count) * 100

    # Calculate average latency excluding N/A entries
    $avgLatency = ($latencyTimes | Where-Object { $_ -ne "N/A" } | Measure-Object -Average).Average

    return [pscustomobject]@{
        Timestamp  = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
        Machine    = $target
        Port       = $port
        PacketLoss = "$packetLoss"
        AvgLatency = if ($null -ne $avgLatency) { $avgLatency } else { "N/A" }
    }
}



if (-not (Test-Path $rootDrive)) {
    write-log "No $rootDrive drive is present"
    return -1
}
else {
    $trg = get-target
    $targetMachine = $trg.machineName
    $targetShare = $trg.smbShare

    # Run the TCP latency and packet loss test
    $result = Test-TcpLatencyAndPacketLoss -target $targetMachine -port $tcpPort -count $testCount

    # Check the CSV file size 
    $csvFileSize = Check-CsvFileSize -filePath $outputCsvLAT

    # If the file size exceeds the threshold, rotate the file. Force casting of the values to [double] to ensure it's not a string comparison
    if ([double]$csvFileSize -ge [double]$maxFileSizeMB) {
        Rotate-CsvFile -filePath $outputCsvLAT -rotationPath $dest -maxFiles $maxFiles
    }
    
    # Check if the CSV file exists, if not create it
    if (-not (Test-Path $outputCsvLAT)) {
        # Create CSV file with headers
        $result | Export-Csv -Path $outputCsvLAT -NoTypeInformation
    }
    else {
        # Append the result to the CSV file
        $result | Export-Csv -Path $outputCsvLAT -NoTypeInformation -Append
    }
}