PowerShell脚本优化：按用户统计RAM与CPU利用率并导出CSV

需要获取任务管理器「用户」标签页中各用户的RAM和CPU利用率详情，并单独导出至CSV文件。但当前PowerShell脚本将所有用户的资源使用详情输出在同一行，无法实现按用户单独展示的效果。

原脚本 #

GC c:\List.txt | % {

$xl = New-Object -ComObject "Excel.Application"
$xl.Visible = $true
$xl.DisplayAlerts = $false #for debugging, no prompts to save, etc.
$ConvertToGB = (1024 * 1024 * 1024)

$wkbk = $xl.Workbooks.Add()
$sheet = $wkbk.WorkSheets.Item(1)
$sheet.Name = "Transposed"
$Comp = $_
If (Test-Connection $Comp -Quiet) {
    $Luser = (Get-WmiObject -class win32_process -Filter "Name='Explorer.exe'" -ComputerName $Comp |
            % {$_.GetOwner().User} | Sort-Object -Unique) -join ","
    $Mem = GWMI -Class win32_operatingsystem -computername $COMP
    New-Object PSObject -Property @{
        Server         = $Comp
        "CPU_Usage"    = "$((GWMI -ComputerName $COMP win32_processor | Measure-Object -property LoadPercentage -Average).Average)"
        "Memory_Usage" = "$('{0:N2}' -f ((($Mem.TotalVisibleMemorySize - $Mem.FreePhysicalMemory)*100)/ $Mem.TotalVisibleMemorySize)) %"
        "DiskSpace"    = "$('{0:N2}' -f (Get-WmiObject -Class win32_logicaldisk -ComputerName $COMP -Filter "DriveType = 3"| Select-Object "Size","FreeSpace"))"
        #$Comp = ($disk.Size / $ConvertToGB),($disk.FreeSpace / $ConvertToGB)
        "logged_Users" = $Luser
    }
}
Else {

}
$results = Get-Service -Name *bits*| Select Server    #,"CPU usage","Memory usage","DiskFreeSpace","logged Users" 

$column = 1
$row = 1
foreach ($psRow in $results) {
    foreach ($item in $psRow.PSObject.Properties) {
        $sheet.Cells.Item($row, $column) = $item.Name
        $column++
        #$sheet.Cells.Item($row, $column) = $item."CPU","Memory_Usage","DiskSpace","logged_Users"
        $row++
        $column--
    }

} }

$obj =New-Object PSObject -Property @{
    Server         = $Comp
    "CPU_Usage"    = "$((GWMI -ComputerName $COMP win32_processor | Measure-Object -property LoadPercentage -Average).Average)"
    "Memory_Usage" = "$('{0:N2}' -f ((($Mem.TotalVisibleMemorySize - $Mem.FreePhysicalMemory)*100)/ $Mem.TotalVisibleMemorySize)) %"
    "DiskSpace"    = Get-WmiObject -Class win32_logicaldisk -ComputerName $COMP -Filter "DriveType = 3"| Select-Object "Size","FreeSpace"
    #$Comp = ($disk.Size / $ConvertToGB),($disk.FreeSpace / $ConvertToGB)
    "logged_Users" = $Luser
}
Write-Output "DiskSpace, $($obj.DiskSpace.Size), $($obj.DiskSpace.FreeSpace)"
Write-Output "Server, $($obj.Server)"
Write-Output "Memory_Usage, $($obj.Memory_Usage)"
Write-Output "logged_Users, $($obj.logged_Users)"
Write-Output "CPU_Usage, $($obj.CPU_Usage)"
$obj | ConvertTo-Csv

原脚本输出 #

Memory_Usage : 91.90 %
CPU_Usage    : 99
Server       : 127.0.0.1
logged_Users : user1,user2,user3,userA
DiskSpace    : @{Size=718642417664; FreeSpace=317923561472}, @{Size=214747312128; FreeSpace=182724562944}, @{Size=26507997184; FreeSpace=3710320640}, 
               @{Size=1099511627776; FreeSpace=989560467456}

期望CSV格式 #

logged users,Memory Usage in MB,CPU Usage %
User1,25300,8
User2,33658,15
User3,48793,7
UserA,23564,5

以下是修改后的PowerShell脚本，核心逻辑是按用户分组统计所有进程的CPU和内存占用，并输出符合要求的CSV格式：

# 读取目标服务器列表
$servers = Get-Content -Path "c:\List.txt"

# 存储所有结果的数组
$allResults = @()

foreach ($comp in $servers) {
    # 先测试服务器连通性
    if (-not (Test-Connection -ComputerName $comp -Quiet -Count 1)) {
        Write-Warning "服务器 $comp 无法连通，跳过"
        continue
    }

    try {
        # 获取所有进程，包含进程所有者信息
        $processes = Get-WmiObject -Class Win32_Process -ComputerName $comp -ErrorAction Stop | 
            Where-Object { $_.GetOwner().User -ne $null } |  # 过滤掉无所有者的系统进程
            ForEach-Object {
                $owner = $_.GetOwner()
                [PSCustomObject]@{
                    UserName = $owner.User
                    CpuUsage = $_.UserModeTime + $_.KernelModeTime  # 累计CPU时间，用于计算占比
                    MemUsage = $_.WorkingSetSize  # 内存占用（字节）
                }
            }

        # 按用户分组统计
        $userStats = $processes | Group-Object -Property UserName | ForEach-Object {
            # 计算内存占用（转换为MB）
            $totalMemMB = [math]::Round(($_.Group.MemUsage | Measure-Object -Sum).Sum / 1MB, 0)
            
            # 计算CPU使用率：先获取总CPU时间，再计算占比（基于采样间隔）
            $totalCpuTime = ($_.Group.CpuUsage | Measure-Object -Sum).Sum
            $cpuUsage = if ($totalCpuTime -eq 0) {
                0
            } else {
                # 采样间隔设为1秒，计算相对占比
                $sampleInterval = 10000000  # 1秒对应的WMI时间单位（100纳秒）
                $cpuCores = (Get-WmiObject -Class Win32_Processor -ComputerName $comp -ErrorAction Stop | Measure-Object -Property NumberOfCores -Sum).Sum
                [math]::Round(($totalCpuTime / ($sampleInterval * $cpuCores)) * 100, 0)
            }

            # 返回用户统计对象
            [PSCustomObject]@{
                "logged users"        = $_.Name
                "Memory Usage in MB"  = $totalMemMB
                "CPU Usage %"         = $cpuUsage
                "Server"              = $comp  # 可选：保留服务器标识，不需要可删除
            }
        }

        # 将当前服务器的结果加入总数组
        $allResults += $userStats
    } catch {
        Write-Error "处理服务器 $comp 时出错：$_"
        continue
    }
}

# 导出到CSV文件
$allResults | Export-Csv -Path "C:\UserResourceStats.csv" -NoTypeInformation -Encoding UTF8

Write-Host "导出完成，文件路径：C:\UserResourceStats.csv"

关键说明 #

• 进程所有者获取：通过GetOwner()方法获取每个进程的所属用户，过滤掉无所有者的系统进程，确保统计的是真实登录用户的资源占用。
• 内存统计：累计同一用户所有进程的WorkingSetSize（工作集内存），转换为MB单位并取整，和任务管理器用户标签页的内存统计逻辑一致。
• CPU统计：通过累计进程的UserModeTime和KernelModeTime计算CPU总占用时间，结合服务器CPU核心数和1秒采样间隔，转换为百分比，还原任务管理器的CPU统计逻辑。
• CSV导出：使用Export-Csv直接输出结构化数据，避免手动拼接导致的格式错误，确保CSV文件可直接用Excel等工具打开。
• 错误处理：添加了连通性测试和异常捕获，确保脚本在部分服务器不可用时仍能继续运行，同时输出错误信息便于排查。

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