ApoAlly Insights Setup

Windows Server 2019

Powershell Upload Script

apoally-insights-upload.ps1

# upload_csv.ps1
#
# This script now automatically searches for an optional schema file (.json or .csvs)
# with the same base name as the CSV/TSV file. JSON has priority over CSVS if both exist.
# If no schema file is found, only the CSV/TSV will be uploaded.
#
# Environment:
#   $apiUrl      - Default: "https://api.apoally.de/insights/upload"
#   $apiKey      - Default: "YOUR-API-KEY"
#   $csvFolder   - Default: "D:\ApoAlly-Insights"
#   $waitMinutes - Default: 5
#
# This script checks each CSV/TSV file in the $csvFolder. If a file is older than $waitMinutes,
# it will be uploaded to the API. If an error occurs, an error.log is created.

# Configuration
$apiUrl = "https://api.apoally.de/insights/upload"
$apiKey = "YOUR-API-KEY"
$csvFolder = "D:\ApoAlly-Insights"
$waitMinutes = 5

function Upload-SingleFile {
    param (
        [string]$CsvFile
    )

    # Check for an existing JSON or CSVS schema file with the same base name (JSON has priority)
    $schemaFileJson = Join-Path $CsvFile.DirectoryName ("$($CsvFile.BaseName).json")
    $schemaFileCsvs = Join-Path $CsvFile.DirectoryName ("$($CsvFile.BaseName).csvs")
    $schemaFileToUpload = $null

    if (Test-Path $schemaFileJson) {
        $schemaFileToUpload = $schemaFileJson
    }
    elseif (Test-Path $schemaFileCsvs) {
        $schemaFileToUpload = $schemaFileCsvs
    }

    Write-Host "Uploading file: $($CsvFile.FullName)"
    if ($schemaFileToUpload) {
        Write-Host "Found optional schema file: $schemaFileToUpload"
    }

    # Determine MIME type based on extension (.csv or .tsv)
    $extension = $CsvFile.Extension.ToLower()
    $contentType = "text/csv"
    if ($extension -eq ".tsv") {
        $contentType = "text/tab-separated-values"
    }

    # Build the multipart/form-data body
    $boundary = "----MyBoundary$(Get-Random)"
    $LF = "`r`n"
    $bodyLines = @()

    # 1) Add the CSV/TSV part
    $csvContent = Get-Content $CsvFile.FullName -Raw
    $bodyLines += "--$boundary"
    $bodyLines += "Content-Disposition: form-data; name=`"files`"; filename=`"$($CsvFile.Name)`""
    $bodyLines += "Content-Type: $contentType$LF"
    $bodyLines += $csvContent

    # 2) If we have a schema file, add it as a second part
    if ($schemaFileToUpload) {
        $schemaContent = Get-Content $schemaFileToUpload -Raw
        $bodyLines += "--$boundary"
        $bodyLines += "Content-Disposition: form-data; name=`"files`"; filename=`"$($schemaFileToUpload)`""

        if ($schemaFileToUpload.ToLower().EndsWith(".json")) {
            $bodyLines += "Content-Type: application/json$LF"
        }
        else {
            # For .csvs, we can treat it as text
            $bodyLines += "Content-Type: text/plain$LF"
        }

        $bodyLines += $schemaContent
    }

    # End boundary
    $bodyLines += "--$boundary--"

    $body = $bodyLines -join $LF
    $headers = @{
        "X-API-Key" = $apiKey
        "Content-Type" = "multipart/form-data; boundary=$boundary"
    }

    try {
        $response = Invoke-RestMethod -Uri $apiUrl -Method Post -Headers $headers -Body $body
        return $response
    }
    catch {
        throw $_
    }
}

function Upload-CSVFiles {
    param (
        [string]$Folder
    )

    # Get all CSV and TSV files recursively, ignoring those matching '_img.csv' or '_img.tsv'
    $csvFiles = Get-ChildItem -Path $Folder -Recurse -File -Include *.csv, *.tsv |
        Where-Object { $_.Name -notmatch '_img\.(csv|tsv)$' }

    foreach ($file in $csvFiles) {
        # Define the error log file name for the data file
        $errorLog = Join-Path $file.DirectoryName ("$($file.BaseName).error.log")

        # Skip if an error log already exists
        if (Test-Path $errorLog) {
            Write-Host "Skipping file due to existing error log: $($file.FullName)"
            continue
        }

        # Check if the file is at least $waitMinutes old
        $fileAge = (Get-Date) - $file.LastWriteTime
        if ($fileAge.TotalMinutes -lt $waitMinutes) {
            Write-Host "File is not old enough (Age: $([math]::Round($fileAge.TotalMinutes, 2)) minutes): $($file.FullName)"
            continue
        }

        Write-Host "Attempting upload of: $($file.FullName)"
        try {
            $response = Upload-SingleFile -CsvFile $file

            if ($response.status -eq "success") {
                Write-Host "Upload successful: $($file.FullName)"
                Remove-Item $file.FullName -Force

                # Check and delete any associated _img.csv or _img.tsv file
                $imgCsv = Join-Path $file.DirectoryName ("$($file.BaseName)_img.csv")
                $imgTsv = Join-Path $file.DirectoryName ("$($file.BaseName)_img.tsv")
                if (Test-Path $imgCsv) {
                    Remove-Item $imgCsv -Force
                    Write-Host "Deleted associated _img.csv file: $imgCsv"
                }
                if (Test-Path $imgTsv) {
                    Remove-Item $imgTsv -Force
                    Write-Host "Deleted associated _img.tsv file: $imgTsv"
                }
            }
            else {
                Write-Host "Upload failed for file: $($file.FullName). Creating error log."
                $errorMessage = "Error uploading: " + ($response.error)
                $errorMessage | Out-File -FilePath $errorLog -Encoding utf8
            }
        }
        catch {
            Write-Host "Error uploading file $($file.FullName): $_. Creating error log."
            $_ | Out-File -FilePath $errorLog -Encoding utf8
        }
    }
}

function Start-Watch {
    param (
        [string]$Folder,
        [int]$WaitMinutes
    )

    # Initial processing at startup
    Upload-CSVFiles -Folder $Folder

    # Endlessly repeat, waiting 1 minute between checks
    while ($true) {
        Start-Sleep -Seconds 60
        Upload-CSVFiles -Folder $Folder
    }
}

# Start folder watch
Start-Watch -Folder $csvFolder -WaitMinutes $waitMinutes

---

Installation and Configuration Guide for the Script

The following guide explains how to save, configure, and set up the PowerShell script apoally-insights-upload.ps1 on a Windows Server 2019 so that it runs automatically at system startup.

---

1. Save the Script

1. Open a text editor (e.g., Notepad).
2. Copy the content of the PowerShell script apoally-insights-upload.ps1 into the editor.
3. Save the file as apoally-insights-upload.ps1 in the desired directory, e.g., C:\Scripts.

---

2. Adjust PowerShell Execution Policy

To run the script, the PowerShell execution policy must be adjusted:

1. Open PowerShell as an administrator.
2. Enter the following command to change the execution policy:

   Set-ExecutionPolicy RemoteSigned
   

3. Confirm the change with Y (Yes).

---

3. Adjust API Key and Configuration

1. Open the file apoally-insights-upload.ps1 with a text editor.
2. Replace YOUR-API-KEY with your actual API key.
3. Adjust the path for the folder $csvFolder if your CSV files are stored in a different directory.
4. Save the changes.

---

4. Set Up a Task for Autostart

To run the script automatically at system startup, set up a scheduled task:

1. Open the Task Scheduler via the Start menu.
2. Click on Create Task.
3. Under General, enter a name for the task, e.g., ApoAlly Insights Upload.
4. Enable the option Run with highest privileges.
5. Go to the Triggers tab and click New: - Select At startup. - Click OK.
6. Go to the Actions tab and click New: - Select Start a program. - Enter the following in the Program/script field:

   powershell.exe
   

   - Enter the following in the Add arguments (optional) field:

   -File "C:\Scripts\apoally-insights-upload.ps1"
   

   - Click OK.
7. Go to the Conditions tab and disable the option Start the task only if the computer is on AC power if the task should also run on battery power.
8. Click OK to create the task.

---

5. Test Run

1. Run the script manually to ensure it works correctly:

   powershell.exe -File "C:\Scripts\apoally-insights-upload.ps1"
   

2. Check the output and the generated log files to ensure no errors occur.

---

6. Monitoring and Troubleshooting

• Regularly check the log files stored in the same directory as the CSV files.
• If the scheduled task does not run, check the settings in the Task Scheduler and ensure the script is configured correctly.

---

With these steps, the script is successfully set up on your Windows Server 2019 and will run automatically at system startup.