azure - 如何使用 Powershell 从 csv 文件中仅读取一列

Question

我正在编写 powershell 脚本，我正在尝试从 csv 文件中仅读取“用户”列，然后将其转换为他们的 Microsoft AzureAD 显示名称，但不知道如何执行此操作，因此如果我可以获得任何帮助或建议。

我的 csv 文件如下所示

C:\AuditLogSearch$($CurDate) 最终审核日志记录.csv

User       Date &Time       Activity
-------------------------------------
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="fd9c9f9ebd9a909c9491d39e9290" rel="noreferrer noopener nofollow">[email protected]</a>   11/22/2021     play soccer
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6308060a23040e020a0f4d000c0e" rel="noreferrer noopener nofollow">[email protected]</a>   10/22/2021     play football
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d0b4b5b690b7bdb1b9bcfeb3bfbd" rel="noreferrer noopener nofollow">[email protected]</a>   11/22/2021     play soccer
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5e3a3b381e39333f3732703d3133" rel="noreferrer noopener nofollow">[email protected]</a>   09/22/2021     play Baseball
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="28505152684f45494144064b4745" rel="noreferrer noopener nofollow">[email protected]</a>   08/22/2021     play soccer
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="9cf7f0f1dcfbf1fdf5f0b2fff3f1" rel="noreferrer noopener nofollow">[email protected]</a>   19/22/2021     play football

function Connect-AzureActiveDirectory {
    $pso = New-PSSessionOption -SkipCACheck:$true -SkipCNCheck:$true -SkipRevocationCheck:$true -OperationTimeout 180000 -ProxyAccessType AutoDetect
    Connect-AzureAD -Credential $credential -ConnectionUri https://outlook.office365.com/PowerShell-LiveID -PSSessionOption $pso
    $host.ui.RawUI.WindowTitle = "Exchange Online - NAM Production Beta MFA"
    Write-Host "Connected to Azure AD"      
}


function Convert-UserColumn {
    $ImportFile = "C:\AuditLogSearch\$($CurDate) Final Audit-Log-Records.csv"
    $ExportFile = "C:\AuditLogSearch\FinalFile.csv"

// I'm struggling with the logic here

    $list = @() foreach ($u in $ImportFile.User) {
        $list += Get-AzureDisplayName -mail $u
        
    }

    Get-AzureADUser -ObjectId 

   | Export-Csv $ExportFile -NoTypeInformation
}

尝试使最终导出 csv 文件应如下所示C:\AuditLogSearch\FinalFile.csv

User       Date &Time       Activity
-------------------------------------
Jonathan Sparkle   11/22/2021     play soccer

Randy Mod       10/22/2021     play football
Hanah P         11/22/2021     play soccer
Hanah P         09/22/2021     play Baseball
Cristiano Ronaldo   08/22/2021     play soccer
Leo Messi    19/22/2021     play football

Answer 1

您只需更新导入的 CSV 的 User 属性上的值，无需创建 $list = @() 来保存结果。

假设 $ImportFile.User 包含 Azure AD 用户的有效 UserPrincipalNames，您遇到的问题将如下所示(绝对不需要函数):

$ImportFile = Import-Csv "C:\AuditLogSearch\$($CurDate) Final Audit-Log-Records.csv"
$ExportFile = "C:\AuditLogSearch\FinalFile.csv"

foreach ($u in $ImportFile) {
    $u.User = (Get-AzureADUser -ObjectId $u.User).DisplayName
}

$ImportFile | Export-Csv $ExportFile -NoTypeInformation

<小时/>

我正在尝试更改“事件”列值/文本。例如，如果文本 =“踢足球”，则将其更改为“踢足球”，或者如果文本 =“踢足球”，则将其更改为“打橄榄球”，依此类推。

这需要 if 条件或 switch 。即:

# Using this as an Example
$csv = @'
User,Date & Time,Activity
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5233303112353f333b3e7c313d3f" rel="noreferrer noopener nofollow">[email protected]</a>,11/22/2021,play soccer
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ee858b87ae89838f8782c08d8183" rel="noreferrer noopener nofollow">[email protected]</a>,10/22/2021,play football
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e6828380a6818b878f8ac885898b" rel="noreferrer noopener nofollow">[email protected]</a>,11/22/2021,play soccer
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="1e7a7b785e79737f7772307d7173" rel="noreferrer noopener nofollow">[email protected]</a>,09/22/2021,play Baseball
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="037b7a7943646e626a6f2d606c6e" rel="noreferrer noopener nofollow">[email protected]</a>,08/22/2021,play soccer
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="23484f4e63444e424a4f0d404c4e" rel="noreferrer noopener nofollow">[email protected]</a>,19/22/2021,play football
'@ | ConvertFrom-Csv

foreach($line in $csv)
{
    $line.Activity = switch($line.Activity)
    {
        'play soccer' { 'play futball'; break }
        'play football' { 'play rugby'; break }
        Default { $_ } # If no matches, leave it as is
    }
}

$csv | Format-Table

User          Date & Time Activity
----          ----------- --------
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0362616043646e626a6f2d606c6e" rel="noreferrer noopener nofollow">[email protected]</a> 11/22/2021  play futball
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c5aea0ac85a2a8a4aca9eba6aaa8" rel="noreferrer noopener nofollow">[email protected]</a> 10/22/2021  play rugby
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e5818083a58288848c89cb868a88" rel="noreferrer noopener nofollow">[email protected]</a> 11/22/2021  play futball
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="debabbb89eb9b3bfb7b2f0bdb1b3" rel="noreferrer noopener nofollow">[email protected]</a> 09/22/2021  play Baseball
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="255d5c5f654248444c490b464a48" rel="noreferrer noopener nofollow">[email protected]</a> 08/22/2021  play futball
<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="32595e5f72555f535b5e1c515d5f" rel="noreferrer noopener nofollow">[email protected]</a> 19/22/2021  play rugby