PowerShell服务器健康脚本导出CSV及Teradata加载咨询

没问题，我帮你搞定这两个需求，一步步来：

你的脚本已经把所有检测结果存在了$Result数组里，直接导出CSV就行。不过当前数组里的字段都是带%的字符串（比如"80%"），这种格式加载到Teradata会很麻烦，所以先调整成纯数值类型再导出，同时加个检测时间字段方便后续分析。

修改后的完整脚本如下：

$ServerListFile = "C:\Users\HOSDUM01\Desktop\Auto\Server_health_check\list.txt"
$ServerList = Get-Content $ServerListFile -ErrorAction SilentlyContinue
$result = @()

ForEach ($computername in $ServerList) {
    $AVGProc = Get-WmiObject -computername $computername win32_processor | Measure-Object -property LoadPercentage -Average | Select Average
    $OS = gwmi -Class win32_operatingsystem -computername $computername | Select-Object @{Name = "MemoryUsage"; Expression = {[math]::Round((($_.TotalVisibleMemorySize - $_.FreePhysicalMemory) * 100) / $_.TotalVisibleMemorySize, 2) }}
    $vol = Get-WmiObject -Class win32_Volume -ComputerName $computername -Filter "DriveLetter = 'C:'" | Select-object @{Name = "CPercentFree"; Expression = {[math]::Round(($_.FreeSpace / $_.Capacity) * 100, 2) } }
    $vol2 = Get-WmiObject -Class win32_Volume -ComputerName $computername -Filter "DriveLetter = 'E:'" | Select-object @{Name = "EPercentFree"; Expression = {[math]::Round(($_.FreeSpace / $_.Capacity) * 100, 2) } }
    $vol3 = Get-WmiObject -Class win32_Volume -ComputerName $computername -Filter "DriveLetter = 'F:'" | Select-object @{Name = "FPercentFree"; Expression = {[math]::Round(($_.FreeSpace / $_.Capacity) * 100, 2) } }

    # 存储纯数值，去掉%，添加检测时间
    $result += [PSCustomObject] @{
        ServerName = $computername
        CPULoad = $AVGProc.Average
        MemLoad = $OS.MemoryUsage
        CDriveFreePercent = $vol.CPercentFree
        EDriveFreePercent = $vol2.EPercentFree
        FDriveFreePercent = $vol3.FPercentFree
        CheckTime = Get-Date
    }
}

# 导出CSV，-NoTypeInformation去掉开头的类型注释，UTF8避免乱码
$result | Export-Csv -Path "C:\Users\HOSDUM01\Desktop\Auto\Server_health_check\ServerHealthReport.csv" -NoTypeInformation -Encoding UTF8

# 原HTML生成和邮件发送部分保留，仅调整显示逻辑
$Outputreport = "<HTML><TITLE> Server Health Report </TITLE> <BODY background-color:peachpuff> <font color =""#99000"" face=""Microsoft Tai le""> <H2> Server Health Report </H2></font> <Table border=1 cellpadding=0 cellspacing=0> <TR bgcolor=Cyan align=center> <TD><B>EP_Server_Name</B></TD> <TD><B>CPU_Utilizied</B></TD> <TD><B>Memory_Utilized</B></TD> <TD><B>C_Drive_Free_Percentage</B></TD> <TD><B>E_Drive_Free_Percentage</B></TD> <TD><B>F_Drive_Free_Percentage</B></TD></TR>"

Foreach ($Entry in $Result) {
    # 用数值判断阈值，更准确
    if (($Entry.CPULoad -ge 80) -or ($Entry.MemLoad -ge 80) -or ($Entry.CDriveFreePercent -le 20) -or ($Entry.EDriveFreePercent -le 20) -or ($Entry.FDriveFreePercent -le 20)) {
        $Outputreport += "<TR bgcolor=red>"
    } else {
        $Outputreport += "<TR bgcolor=green>"
    }
    # 显示时再加上%符号
    $Outputreport += "<TD>$($Entry.ServerName)</TD><TD align=center>$($Entry.CPULoad)%</TD><TD align=center>$($Entry.MemLoad)%</TD><TD align=center>$($Entry.CDriveFreePercent)%</TD><TD align=center>$($Entry.EDriveFreePercent)%</TD><TD align=center>$($Entry.FDriveFreePercent)%</TD></TR>"
}

$Outputreport += "</Table></BODY></HTML>"
$Outputreport | out-file C:\Users\HOSDUM01\Desktop\Auto\Server_health_check\Test.htm

# 邮件发送部分保留
$smtpServer = "Random"
$smtpFrom = "test@test.com"
$smtpTo = "test@test.com"
$messageSubject = "Servers Health report"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Body = "<head><pre>$style</pre></head>"
$message.Body += Get-Content C:\Users\HOSDUM01\Desktop\Auto\Server_health_check\test.htm
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)

核心修改点：

• 把百分比字段改成纯数值，用[math]::Round()保留两位小数
• 添加CheckTime字段记录检测时间
• 用Export-Csv导出CSV，确保格式适合Teradata加载
• HTML显示时再把数值转成带%的字符串，不影响原有邮件效果

可以用Teradata官方的BTEQ工具（大部分Teradata客户端都会预装）实现，PowerShell和批处理都能调用，步骤如下：

第一步：编写BTEQ加载脚本 #

创建LoadServerHealth.btq文件，内容如下（根据你的Teradata环境调整参数）：

.LOGON 你的Teradata数据库地址/用户名,密码;

-- 如果目标表不存在，先创建表（列类型和CSV字段对应）
CREATE TABLE IF NOT EXISTS Server_Health_Check (
    ServerName VARCHAR(100),
    CPULoad DECIMAL(5,2),
    MemLoad DECIMAL(5,2),
    CDriveFreePercent DECIMAL(5,2),
    EDriveFreePercent DECIMAL(5,2),
    FDriveFreePercent DECIMAL(5,2),
    CheckTime TIMESTAMP(6)
) PRIMARY INDEX (ServerName);

-- 全量加载时清空表，增量加载请删除此行
DELETE FROM Server_Health_Check;

-- 导入CSV数据，指定分隔符为逗号
.IMPORT VARTEXT ',' FILE='C:\Users\HOSDUM01\Desktop\Auto\Server_health_check\ServerHealthReport.csv';

-- 插入数据到目标表
INSERT INTO Server_Health_Check (
    ServerName,
    CPULoad,
    MemLoad,
    CDriveFreePercent,
    EDriveFreePercent,
    FDriveFreePercent,
    CheckTime
) VALUES (
    :ServerName,
    :CPULoad,
    :MemLoad,
    :CDriveFreePercent,
    :EDriveFreePercent,
    :FDriveFreePercent,
    :CheckTime
);

.LOGOFF;
.EXIT;

第二步：用PowerShell或批处理执行脚本 #

方法一：PowerShell调用 #

在你的检测脚本末尾添加以下代码（注意BTEQ的路径要匹配你的客户端版本）：

$bteqPath = "C:\Program Files\Teradata\Client\16.20\bin\bteq.exe"
$btqScript = "C:\Users\HOSDUM01\Desktop\Auto\Server_health_check\LoadServerHealth.btq"

# 执行BTEQ脚本
& $bteqPath < $btqScript

# 检查执行结果
if ($LASTEXITCODE -eq 0) {
    Write-Host "CSV数据成功加载到Teradata"
} else {
    Write-Host "加载失败，错误代码：$LASTEXITCODE"
}

方法二：批处理调用 #

创建LoadToTeradata.bat文件：

@echo off
set BTEQ_PATH="C:\Program Files\Teradata\Client\16.20\bin\bteq.exe"
set BTQ_SCRIPT="C:\Users\HOSDUM01\Desktop\Auto\Server_health_check\LoadServerHealth.btq"

%BTEQ_PATH% < %BTQ_SCRIPT%

if %errorlevel% equ 0 (
    echo CSV数据成功加载到Teradata
) else (
    echo 加载失败，错误代码：%errorlevel%
)
pause

注意事项 #

• 确保运行脚本的用户拥有Teradata的连接权限，以及目标表的CREATE、DELETE、INSERT权限
• BTEQ路径要和你的Teradata客户端安装路径一致（比如版本号16.20可能需要调整）
• 增量加载时请删除BTEQ脚本中的DELETE FROM语句，或添加时间过滤逻辑

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