基于CSV规则生成Service Request：SMLETS+PowerShell技术求助

1. 核心修复：遍历CSV并读取列数据 #

原代码未遍历CSV行，导致无法读取列值。使用foreach循环遍历Import-Csv返回的每行对象，直接通过列名访问数据（列名含空格时用${row.'列名'}格式），空数据行也会正常生成工单。

2. 实现到期日规则 #

将CSV中的SLO倒计时转换为整数，判断后计算到期日：

• 若SLO倒计时小于7天，到期日设为当前日期+7天
• 否则使用SLO倒计时数值作为天数

3. 实现支持组与分配规则 #

• 默认支持组为ServiceDesk
• 若host_name字段不含RES，将支持组设为EITS_HW_Notes，并分配给CSV中指定的custodian用户（需确保CSV包含该列）

完整代码示例 #

# 切换环境：注释掉测试/生产服务器
# Prod
# $GLOBAL:smdefaultcomputer = "prodserver"
# Test
$GLOBAL:smdefaultcomputer = "testserver"
Import-Module SMlets

$path = "C:\Temp\Test.csv"
$csv = Import-Csv -Path $path

# 预加载SCSM类与枚举值
$srClass = Get-SCSMClass -Name System.WorkItem.ServiceRequest
$srprior = Get-SCSMEnumeration -Name ServiceRequestPriorityEnum.Medium
$srurg = Get-SCSMEnumeration -Name ServiceRequestUrgencyEnum.Medium
$ararea = Get-SCSMEnumeration -Name Enum.add3768303064ec18890170ba33cffda
$userClass = Get-SCSMClass -Name Microsoft.AD.UserBase$
$AffectedUserRel = Get-SCSMRelationshipClass -Name System.WorkItemAffectedUser$
$AssignedToRel = Get-SCSMRelationshipClass -Name System.WorkItemAssignedToUser$

# 遍历CSV每行生成SR
foreach ($row in $csv) {
    # 处理到期日逻辑
    $sloDays = [int]::TryParse($row.'SLO倒计时', [ref]$null) ? [int]$row.'SLO倒计时' : 7
    $targetDays = $sloDays -lt 7 ? 7 : $sloDays
    $dueDate = (Get-Date).AddDays($targetDays)

    # 处理支持组与分配用户逻辑
    $supportGroup = "ServiceDesk"
    $assignedUser = $null
    if (-not [string]::IsNullOrWhiteSpace($row.host_name) -and -not $row.host_name.Contains("RES")) {
        $supportGroup = "EITS_HW_Notes"
        # 从CSV获取custodian并查询AD用户，需确保custodian格式为"域\用户名"或"用户名@域"
        if (-not [string]::IsNullOrWhiteSpace($row.custodian)) {
            $custodianParts = $row.custodian -split '\\|@'
            $userName = $custodianParts[0]
            $domain = $custodianParts.Count -gt 1 ? $custodianParts[1] : "SHERMAN"
            $cString = "UserName = '$userName' and Domain = '$domain'"
            $crit = New-Object Microsoft.EnterpriseManagement.Common.EnterpriseManagementObjectCriteria($cString, $userClass)
            $assignedUser = Get-SCSMObject -Criteria $crit
        }
    }

    # 构造SR参数（用CSV数据填充标题/描述，空值时用默认值）
    $srargs = @{
        Title         = [string]::IsNullOrWhiteSpace($row.Title) ? "Default SR Title" : $row.Title
        Urgency       = $srurg
        Priority      = $srprior
        Area          = $ararea
        SupportGroup  = $supportGroup
        Description   = [string]::IsNullOrWhiteSpace($row.Description) ? "Default SR Description" : $row.Description
        TargetEndDate = $dueDate  # 到期日字段，需确认SCSM类属性名是否正确，若不对可替换为对应属性
    }

    # 创建SR
    $newServiceRequest = New-SCSMObject -Class $srClass -PropertyHashtable $srargs -PassThru
    if (-not $newServiceRequest) {
        Write-Warning "Failed to create SR for row: $($row | Out-String)"
        continue
    }
    Write-Host "Created SR: $($newServiceRequest.Id)"

    # 设置受影响用户（保留原逻辑，可根据需求修改为CSV中的用户）
    $cString = "UserName = 'itservicenotifications' and Domain = 'SHERMAN'"
    $crit = New-Object Microsoft.EnterpriseManagement.Common.EnterpriseManagementObjectCriteria($cString, $userClass)
    $affectedUser = Get-SCSMObject -Criteria $crit
    if ($affectedUser) {
        New-SCSMRelationshipObject -Relationship $AffectedUserRel -Source $newServiceRequest -Target $affectedUser -Bulk | Out-Null
    }

    # 分配给custodian（仅当支持组为EITS_HW_Notes且用户存在时）
    if ($assignedUser) {
        New-SCSMRelationshipObject -Relationship $AssignedToRel -Source $newServiceRequest -Target $assignedUser -Bulk | Out-Null
        Write-Host "Assigned SR $($newServiceRequest.Id) to $($assignedUser.UserName)"
    }
}

关键说明 #

• CSV列名匹配：确保代码中使用的列名（如'SLO倒计时'、host_name、custodian）与CSV文件的列名完全一致，大小写不敏感但需匹配空格和特殊字符。
• SCSM属性名验证：TargetEndDate是SCSM服务请求的到期日默认属性名，若环境中属性名不同，可通过Get-SCSMClass -Name System.WorkItem.ServiceRequest | Select-Object -ExpandProperty Properties查询正确属性名。
• 空值处理：代码中用[string]::IsNullOrWhiteSpace判断空值，确保空数据行也能生成默认内容的工单。

邮件生成工单补充 #

若需通过邮件生成SR，可配置SCSM的邮件连接器：

1. 在SCSM控制台中创建邮件连接器，指定接收邮箱
2. 编写PowerShell脚本读取CSV并生成符合SCSM邮件模板格式的邮件（包含标题、描述、支持组等关键字段）
3. 发送邮件到指定邮箱，SCSM会自动解析并生成对应SR

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