PowerShell处理Zendesk API数据：ID转名称与条件过滤问题

一、Agent/Author ID转对应名称的解决方案 #

首先需要拉取Zendesk的用户列表（Agent属于平台用户），构建ID到名称的映射哈希表，实现快速查找替换：

# 拉取Zendesk用户数据并构建ID-名称映射
$authHeaders = @{Authorization = "Bearer YOUR_ZENDESK_API_TOKEN"}
$zdUsers = Invoke-RestMethod -Uri "https://your-subdomain.zendesk.com/api/v2/users.json" -Headers $authHeaders
$userMap = @{}
foreach ($user in $zdUsers.users) {
    $userMap[$user.id] = $user.name
}

# 处理工单/事件数据，替换ID为名称
$processedData = $zendeskRawJsonData | ForEach-Object {
    # 根据实际字段名调整，比如author_id、assignee_id
    $_ | Add-Member -MemberType NoteProperty -Name "AuthorName" -Value $userMap[$_.author_id] -Force
    $_ | Add-Member -MemberType NoteProperty -Name "AgentName" -Value $userMap[$_.assignee_id] -Force
    $_
}

二、解决ID重复问题 #

基于数据的唯一标识（如ticket_id或event_id）去重，保留第一条出现的记录：

# 按唯一ID分组去重
$deduplicatedData = $processedData | Group-Object -Property id | ForEach-Object { $_.Group[0] }

# 或用Select-Object直接去重（适用于ID是全局唯一的场景）
# $deduplicatedData = $processedData | Select-Object -Unique -Property id, *

三、过滤规则与SQL字段映射处理 #

严格按照要求的规则过滤数据并生成SQL插入所需结构：

$filteredData = $deduplicatedData | 
    # 规则1：丢弃无Agent名称的记录
    Where-Object { $_.AgentName } |
    ForEach-Object {
        $sqlType = ""
        # 规则2：Subject字段且Create类型
        if ($_.eventData.field_name -eq "Subject" -and $_.type -eq "Create") {
            $sqlType = "create"
        }
        # 规则3：Comment类型根据public值设置
        elseif ($_.type -eq "Comment") {
            $sqlType = if ($_.public) { "public" } else { "private" }
        }
        # 规则4：status为solved时设置
        elseif ($_.status -eq "solved") {
            $sqlType = "solved"
        }

        # 输出适配SQL表的对象
        [PSCustomObject]@{
            TicketId     = $_.ticket_id
            AgentName    = $_.AgentName
            AuthorName   = $_.AuthorName
            Type         = $sqlType
            CreatedAt    = [DateTime]$_.created_at
            # 按需添加其他SQL表字段
        }
    }

四、代码整体优化建议 #

1. 分页处理：Zendesk API存在分页限制，拉取数据时需循环处理next_page字段，避免遗漏数据：

$allZendeskData = @()
$nextPage = "https://your-subdomain.zendesk.com/api/v2/tickets.json"
while ($nextPage) {
    $response = Invoke-RestMethod -Uri $nextPage -Headers $authHeaders
    $allZendeskData += $response.tickets
    $nextPage = $response.next_page
}

2. 参数化SQL插入：避免SQL注入风险，同时提升插入效率：

$conn = New-Object System.Data.SqlClient.SqlConnection("Server=.;Database=YourDB;Integrated Security=True")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = @"
INSERT INTO ZendeskTickets (TicketId, AgentName, AuthorName, Type, CreatedAt)
VALUES (@TicketId, @AgentName, @AuthorName, @Type, @CreatedAt)
"@
# 定义参数类型
$cmd.Parameters.Add("@TicketId", [System.Data.SqlDbType]::Int64)
$cmd.Parameters.Add("@AgentName", [System.Data.SqlDbType]::NVarChar, 100)
$cmd.Parameters.Add("@AuthorName", [System.Data.SqlDbType]::NVarChar, 100)
$cmd.Parameters.Add("@Type", [System.Data.SqlDbType]::NVarChar, 20)
$cmd.Parameters.Add("@CreatedAt", [System.Data.SqlDbType]::DateTime)

foreach ($item in $filteredData) {
    $cmd.Parameters["@TicketId"].Value = $item.TicketId
    $cmd.Parameters["@AgentName"].Value = $item.AgentName
    $cmd.Parameters["@AuthorName"].Value = $item.AuthorName
    $cmd.Parameters["@Type"].Value = $item.Type
    $cmd.Parameters["@CreatedAt"].Value = $item.CreatedAt
    $cmd.ExecuteNonQuery()
}
$conn.Close()

3. 错误处理与日志：添加try/catch块捕获异常，记录处理日志：

try {
    # 数据拉取、处理、插入逻辑
}
catch {
    Write-Error "处理失败：$_"
    Add-Content -Path "zendesk_error.log" -Value "$(Get-Date): $_"
}

4. 模块化拆分：将数据拉取、ID映射、过滤、SQL插入拆分为独立函数，提升代码复用性和可读性。

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