英文:
Select row with latest timestemp based on 3 columns using Powershell
问题
以下是翻译好的部分：
现在，从上表中，我想要获取每个主机名的最新时间戳的行。
在上面的示例中，它应返回以下行作为输出：
| 主机名 | 状态 | 时间 |
|---|---|---|
| Host1 | 活动 | 2023年2月13日 13:24:50 |
| Host2 | 已退休 | 2023年2月10日 13:17:29 |
关于如何实现这一目标有什么建议吗？
英文:
Sample input:
| Hostnames | Status | Time |
|---|---|---|
| Host1 | Retired | 1/31/2023 9:27:06 PM |
| Host2 | Active | 2/1/2023 11:45:48 AM |
| Host1 | Active | 2/10/2023 4:59:27 AM |
| Host1 | Active | 2/13/2023 1:24:50 PM |
| Host2 | Retired | 2/10/2023 1:17:29 PM |
Now, from above table, I would like to get rows of each hostname with latest timestemp.
In above example, it should return below rows as output:
| Hostnames | Status | Time |
|---|---|---|
| Host1 | Active | 2/13/2023 1:24:50 PM |
| Host2 | Retired | 2/10/2023 1:17:29 PM |
Any suggestions on how this can be achieved?
答案1
得分: 1
在变量$inputData中导入了数据后，最简单的方法是首先根据属性Hostnames对数据进行分组，然后对每个分组按照将Time值转换为真实的DateTime对象后进行排序。
类似这样的代码：
$newData = $inputData | Group-Object Hostnames | ForEach-Object {
$_.Group | Sort-Object {[datetime]$_.Time} | Select-Object -Last 1
}
# 在屏幕上显示
$newData | Format-Table -AutoSize
# 写入CSV文件
$newData | Export-Csv -Path 'X:\Somewhere\LatestStatus.csv' -NoTypeInformation
在屏幕上显示时的输出如下：
Hostnames Status Time
--------- ------ ----
Host1 Active 2/13/2023 1:24:50 PM
Host2 Retired 2/10/2023 1:17:29 PM
希望这对你有所帮助。
英文:
Assuming you have that data imported in a variable $inputData, the easiest way is to group that on property Hostnames first and then sort each group on the Time value converted to real DateTime object.
Something like this
$newData = $inputData | Group-Object Hostnames | ForEach-Object {
$_.Group | Sort-Object {[datetime]$_.Time} | Select-Object -Last 1
}
# display on screen
$newData | Format-Table -AutoSize
# write to csv file
$newData | Export-Csv -Path 'X:\Somewhere\LatestStatus.csv' -NoTypeInformation
Output when displayed on screen:
Hostnames Status Time
--------- ------ ----
Host1 Active 2/13/2023 1:24:50 PM
Host2 Retired 2/10/2023 1:17:29 PM
答案2
得分: 0
用于测试，我从 CSV 导入了数据，然后添加了一个带有日期时间的列，以便进行排序。如下所示：
$data = @'
"主机名","状态","时间"
"主机1","已退役","2023年1月31日 9:27:06 PM"
"主机2","活跃","2023年2月1日 11:45:48 AM"
"主机1","活跃","2023年2月10日 4:59:27 AM"
"主机1","活跃","2023年2月13日 1:24:50 PM"
"主机2","已退役","2023年2月10日 1:17:29 PM"
'@
$table = $data | ConvertFrom-Csv
foreach($row in $table)
{
$row | Add-Member -NotePropertyName DateTime -NotePropertyValue ([DateTime]$row.Time)
}
$table = $table | Sort-Object -Property DateTime -Descending
$groups = $table | Group-Object -Property HostNames
$results = $groups | foreach { $_.Group[0] }
$results
英文:
For testing I imported the data from CSV and then added a column with DateTime so I could sort. See following :
<!-- begin snippet: js hide: false console: true babel: false -->
$data = @'
"Hostnames","Status","Time"
"Host1","Retired","1/31/2023 9:27:06 PM"
"Host2","Active","2/1/2023 11:45:48 AM"
"Host1","Active","2/10/2023 4:59:27 AM"
"Host1","Active","2/13/2023 1:24:50 PM"
"Host2","Retired","2/10/2023 1:17:29 PM"
'@
$table = $data | ConvertFrom-Csv
foreach($row in $table)
{
$row | Add-Member -NotePropertyName DateTime -NotePropertyValue ([DateTime]$row.Time)
}
$table = $table | Sort-Object -Property DateTime -Descending
$groups = $table | Group-Object -Property HostNames
$results = $groups | foreach { $_.Group[0] }
$results
<!-- end snippet -->
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论