PowerShell循环调用XML API获取ADSL账户详情并导出CSV求助

First, let's address the loop issue in your script. The core problem was that you weren't iterating over each CustomerRef in your $ConnectionList—here's a corrected version with error handling and CSV export, plus tips for more efficient requests:

Corrected Sequential Script #

$username = "abc"
$password = "mypassword"
$encoded = [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("$username`:$password"))
$headers = @{Authorization = "Basic $encoded"}

# Fetch list of live ADSL connections
$ConnectionURI = "https://api.myisp.net/xml/stable/ListConnections.php?liveorceased=live&fields=Customer Ref"
$GetConnections = Invoke-RestMethod -Uri $ConnectionURI -Headers $headers
$ConnectionList = $GetConnections.ResponseBlock.Response.OperationResponse.ADSLAccount

# Initialize array to store all account details
$allAccountDetails = @()

# Loop through each CustomerRef to fetch individual account data
foreach ($connection in $ConnectionList) {
    $currentRef = $connection.CustomerRef
    try {
        # Build URI for the current account
        $ConDetailsURI = "https://api.myisp.net/xml/stable/AdslAccount.php?ref=$currentRef"
        # Fetch account details
        $details = Invoke-RestMethod -Uri $ConDetailsURI -Headers $headers -ErrorAction Stop
        # Extract required fields
        $accountInfo = $details.ResponseBlock.Response.OperationResponse.ADSLAccount | 
            Select-Object OurRef, Telephone, NAT, IPAddress, FullUsername, Status, CBUK, Downstream, Upstream
        # Add to our collection
        $allAccountDetails += $accountInfo
        Write-Host "Successfully retrieved details for $currentRef"
    }
    catch {
        Write-Warning "Failed to fetch details for $currentRef : $($_.Exception.Message)"
        # Optional: Add placeholder entry for failed requests
        $allAccountDetails += [PSCustomObject]@{
            OurRef = $currentRef
            Telephone = "Error"
            NAT = "Error"
            IPAddress = "Error"
            FullUsername = "Error"
            Status = "Error"
            CBUK = "Error"
            Downstream = "Error"
            Upstream = "Error"
        }
    }
}

# Export all collected data to CSV
$allAccountDetails | Export-Csv -Path "ADSL_Account_Details.csv" -NoTypeInformation -Encoding UTF8
Write-Host "Export completed. File saved to ADSL_Account_Details.csv"

Key Fixes: #

• Added a foreach loop to iterate over each entry in $ConnectionList
• Used $connection.CustomerRef to target each specific account reference
• Collected all results into an array for bulk CSV export
• Added error handling to catch and log failed API requests

Boost Efficiency with Parallel Requests #

If you're using PowerShell 7 or later, you can use parallel processing to run multiple API requests at once—this will cut down execution time significantly for large lists. Adjust the -ThrottleLimit to match your ISP's API rate limits:

$username = "abc"
$password = "mypassword"
$encoded = [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("$username`:$password"))
$headers = @{Authorization = "Basic $encoded"}

# Fetch list of live ADSL connections
$ConnectionURI = "https://api.myisp.net/xml/stable/ListConnections.php?liveorceased=live&fields=Customer Ref"
$GetConnections = Invoke-RestMethod -Uri $ConnectionURI -Headers $headers
$ConnectionList = $GetConnections.ResponseBlock.Response.OperationResponse.ADSLAccount

# Run parallel API requests
$allAccountDetails = $ConnectionList | ForEach-Object -Parallel {
    $authHeaders = $using:headers
    $currentRef = $_.CustomerRef
    try {
        $uri = "https://api.myisp.net/xml/stable/AdslAccount.php?ref=$currentRef"
        $details = Invoke-RestMethod -Uri $uri -Headers $authHeaders -ErrorAction Stop
        $details.ResponseBlock.Response.OperationResponse.ADSLAccount | 
            Select-Object OurRef, Telephone, NAT, IPAddress, FullUsername, Status, CBUK, Downstream, Upstream
    }
    catch {
        Write-Warning "Failed for $currentRef : $($_.Exception.Message)"
        [PSCustomObject]@{
            OurRef = $currentRef
            Telephone = "Failed"
            NAT = "Failed"
            IPAddress = "Failed"
            FullUsername = "Failed"
            Status = "Failed"
            CBUK = "Failed"
            Downstream = "Failed"
            Upstream = "Failed"
        }
    }
} -ThrottleLimit 10 # Adjust based on your ISP's rate limits

# Export results to CSV
$allAccountDetails | Export-Csv -Path "ADSL_Account_Details_Parallel.csv" -NoTypeInformation -Encoding UTF8
Write-Host "Parallel export completed. File saved to ADSL_Account_Details_Parallel.csv"

Additional Efficiency Tips: #

1. Check for Bulk Endpoints: First, verify if your ISP's API supports fetching multiple accounts at once (e.g., a ref parameter accepting comma-separated values). This is the most efficient method if available.
2. Respect Rate Limits: Start with a low throttle limit and increase it gradually to avoid 429 (Too Many Requests) errors.
3. Cache Connection List: If you run this script frequently, cache the $ConnectionList results to avoid redundant calls to the ListConnections endpoint.

内容的提问来源于stack exchange，提问作者bm97ppc