使用PowerShell从CSV文件中的指定列逐行打印每个服务器名称

vwhgwdsa  于 2023-03-27  发布在  Shell
关注(0)|答案(1)|浏览(100)

我正在创建一个powershell脚本，它将内存使用百分比与预定义的数字进行比较。
基于此结果，将打印一行，详细说明内存使用率是低于还是高于预定义的数字。
我在尝试将每个服务器名从特定列逐行打印到DAT文件中时遇到了问题。

我的PowerShell脚本：

$day = Get-Date -format dd
$Dir = "C:\test\output"
$file = "$Dir\result_esxhost_memory_check2.dat"
$location = "C:\test\output"
$FileExist =  Test-Path -Path $location
$date = Get-Date -format "dd.MM.yy HH:mm"

#--------------------------------------------------------------------------------
#Declaring variables which stores a value from a selected column in the CSV file
#
#
#--------------------------------------------------------------------------------
$memoryusage = Import-Csv -Path C:\test\log\checks2.csv | select -ExpandProperty MemoryUsage
$name = Import-Csv -Path "C:\test\log\checks2.csv" | select -ExpandProperty Name -First 1



#--------------------------------------------------------------------------------

Clear-Content "$file"

  
    
    foreach ($row in $memoryusage) {

    
    If ( "$row" -lt [int32]"45") {
        $Line = "$name" + "|" + $date + "|MemoryUsage|NONE|OK|Memory usage of ESX Host $name is below the threshold" + " " + "($row)" }
    else {
        $Line = "$name" + "|" + $date + "|MemoryUsage|NONE|WARNING|Memory usage of ESX Host $name is above the threshold" + " " + "($row)"}

         Add-Content "$file" `n$Line
                $in = [System.IO.File]::OpenText($file)
$text = ($in.readtoend()).trim("`n")
$in.close()

$stream = [System.IO.StreamWriter]$file
$stream.write($text)
$stream.close()  
   
         
   
    }

脚本运行时，DAT文件中的结果如下：

Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (33.14%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (25.23%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (54.23%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (37.57%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (46.76%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (48.94%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (61.30%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (43.27%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (52.16%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (1.74%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (1.78%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (26.95%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (39.59%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (1.89%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (39.40%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (33.16%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (1.90%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (23.37%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (23.73%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (25.26%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (12.39%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (44.62%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (23.60%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (12.82%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (12.84%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (75.82%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (73.86%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (57.83%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (1.77%)
Server1|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1 is above the threshold (6.30%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (11.74%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (21.01%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (18.65%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (28.65%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (22.22%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (1.88%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (23.21%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (12.36%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (23.45%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (23.83%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (23.63%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (14.96%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (16.05%)
Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (15.25%)

请注意：我只在$name变量的末尾添加了-First 1，以保持数据的整洁，因为它将简单地将CSV文件中“Name”列的每一行中的每个服务器名称添加到DAT文件的每一行。

CSV文件如下所示：

"Name","ConnectionState","PowerState","CpuUsage","MemoryUsage","Version"
"Server1","Connected","PoweredOn","2.64%","33.14%","7.0.3"
"Server2","Connected","PoweredOn","1.85%","25.23%","7.0.3"
"Server3","Connected","PoweredOn","10.99%","54.23%","7.0.3"
"Server2esx401","Connected","PoweredOn","8.95%","37.57%","7.0.3"
"Server2esx402","Connected","PoweredOn","3.96%","46.76%","7.0.3"
"Server1esx403","Connected","PoweredOn","18.66%","48.94%","7.0.3"
"Server1esx402","Connected","PoweredOn","27.51%","61.30%","7.0.3"
"Server1esx401","Connected","PoweredOn","21.83%","43.27%","7.0.3"
"Server1esx502","Connected","PoweredOn","6.74%","52.16%","7.0.3"
"Server1esx503","Connected","PoweredOn","0.15%","1.74%","7.0.3"
"Server1esx505","Connected","PoweredOn","0.31%","1.78%","7.0.3"
"Server1esx501","Connected","PoweredOn","2.01%","26.95%","7.0.3"
"Server1esx504","Connected","PoweredOn","4.28%","39.59%","7.0.3"
"Server1esx802","Connected","PoweredOn","0.21%","1.89%","7.0.3"
"Server1esx803","Connected","PoweredOn","2.67%","39.40%","7.0.3"
"Server1esx801","Connected","PoweredOn","1.35%","33.16%","7.0.3"
"Server1esx804","Connected","PoweredOn","0.17%","1.90%","7.0.3"
"Server1esx406","Connected","PoweredOn","1.67%","23.37%","7.0.3"
"Server1esx405","Connected","PoweredOn","1.80%","23.73%","7.0.3"
"Server1esx404","Connected","PoweredOn","1.17%","25.26%","7.0.3"
"Server1esx302","Connected","PoweredOn","0.74%","12.39%","7.0.3"
"Server1esx305","Connected","PoweredOn","1.14%","44.62%","7.0.3"
"Server1esx301","Connected","PoweredOn","1.11%","23.60%","7.0.3"
"Server1esx303","Connected","PoweredOn","0.53%","12.82%","7.0.3"
"Server1esx304","Connected","PoweredOn","0.46%","12.84%","7.0.3"
"Server1esx902","Connected","PoweredOn","12.35%","75.82%","7.0.3"
"Server1esx901","Connected","PoweredOn","28.83%","73.86%","7.0.3"
"Server2esx501","Connected","PoweredOn","5.19%","57.83%","7.0.3"
"Server2esx503","Connected","PoweredOn","0.17%","1.77%","7.0.3"
"Server2esx502","Connected","PoweredOn","1.65%","6.30%","7.0.3"
"Server2esx505","Connected","PoweredOn","2.31%","11.74%","7.0.3"
"Server2esx504","Connected","PoweredOn","2.11%","21.01%","7.0.3"
"Server2esx804","Connected","PoweredOn","1.40%","18.65%","7.0.3"
"Server2esx802","Connected","PoweredOn","2.30%","28.65%","7.0.3"
"Server2esx801","Connected","PoweredOn","1.46%","22.22%","7.0.3"
"Server2esx803","Connected","PoweredOn","0.39%","1.88%","7.0.3"
"Server2esx301","Connected","PoweredOn","1.68%","23.21%","7.0.3"
"Server2esx304","Connected","PoweredOn","0.32%","12.36%","7.0.3"
"Server2esx302","Connected","PoweredOn","1.55%","23.45%","7.0.3"
"Server2esx303","Connected","PoweredOn","0.37%","23.83%","7.0.3"
"Server2esx305","Connected","PoweredOn","0.94%","23.63%","7.0.3"
"Server2esx406","Connected","PoweredOn","1.10%","14.96%","7.0.3"
"Server2esx405","Connected","PoweredOn","1.02%","16.05%","7.0.3"
"Server2esx404","Connected","PoweredOn","0.97%","15.25%","7.0.3"

最后，我希望DAT文件逐行显示CSV文件“Name”列中的每个服务器名。

我正在寻找的输出示例：

Server1|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server1 is below the threshold (33.14%)
Server2|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server2 is below the threshold (25.23%)
Server3|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server3 is above the threshold (54.23%)
Server2esx401|21.03.23 12:46|MemoryUsage|NONE|OK|Memory usage of ESX Host Server2esx401 is below the threshold (37.57%)
Server2esx403|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server2esx403 is above the threshold (46.76%)
Server1esx403|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1esx403 is above the threshold (48.94%)
Server1esx402|21.03.23 12:46|MemoryUsage|NONE|WARNING|Memory usage of ESX Host Server1esx402 is above the threshold (61.30%)

希望这能解释我在找什么。
谢谢你，非常感谢任何帮助！
迈克

csv

1条答案

按热度按时间
b5buobof

b5buobof1#

你在每一行上都得到所有服务器名称的原因是你的$name变量是一个集合。它是CSV文件中“name”列的全部内容。所以当你把这个变量放到一个字符串中时，它会把整个服务器名称集放进去。
首先，你可以简化导入CSV的方式。考虑到你要做的事情，你不需要两次导入，也不需要使用-ExpandProperty标志。
简单地使用$csvData = Import-Csv -Path "YourPath"就足以让你将整个CSV文件放入一个对象中，其中CSV列名现在是对象属性名。如果你只需要CSV文件中的名称和memoryUsage，那么你可以这样做：

$csvData = Import-CSV -Path "YourPath" | Select-Object -Property Name, MemoryUsage

通过不使用-ExpandProperty，生成的$csvData对象将包含命名属性，使它们更容易在循环中引用。
最后，我们可以构建所需的字符串：

ForEach($row in $csvData){
   #skipping the conditional logic for clarity
   $line = $row.name + '|' + $date + '|' + $row.MemoryUsage + etc...
}

顺便说一句，我更喜欢使用一种更显式的字符串连接形式。这当然不是一种标准，但我喜欢它的外观：

$line = [string]::concat($row.Name, '|', $date, '|MemoryUsage|NONE|OK|Memory usage of ESX Host ', $row.Name, ' is below the threshold ', $row.MemoryUsage)
赞(0）分享  回复(0）举报  2023-03-27
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com