PowerShell脚本读取SharePoint日志报错：ConvertFrom-Json参数为空

我编写了一个PowerShell脚本，用于读取Office 365中两个SharePoint站点的审计日志并生成CSV文件，但运行时遇到了以下错误。

脚本代码 #

Read-Host -Prompt "Enter your tenant password" -AsSecureString | ConvertFrom-SecureString | Out-File "O365.key"

#Get the script path
$dir = ""

#Get the current date
$date = (Get-Date -f dd-MM-yyyy-hhmmss)

#Store the outputfile
$CSVFile = "$dir\Auditlogs_$date.csv"

#Details for sending email
$From = "*****@*****.com"
$To = "*****@*****.com"
$CC ="*****@*****.com"
$smtp = "outlook.office365.com"
$Subject = "Sharepoint audit logs"

#Whom to notify incase of connection to exchange online module fails. This will be the task owner.
$notify="*****@*****.com"

$Body = "Hi Team, Please find the audit logs for the following sharepoint sites for the last x days.

Thanks, O365 Team "

#Create a backup folder and move all the old files to it.
$destination = "$dir\Backup"
$Move = Get-ChildItem -Path "$dir\Auditlogs_*.csv" #| Sort-Object LastWriteTime -Descending | Select-Object -Skip 1

foreach ($file in $Move) {
  $parent = Split-Path $file.FullName -Parent
  Move-Item $file.FullName -Destination $destination -Force
}

#store the o365 credentials
$TenantUname = "*****@*****.com"

#Run the following single line to store the password of account that will be used to connect to o365 exchange online
#Read-Host -Prompt "Enter your tenant password" -AsSecureString | ConvertFrom-SecureString | Out-File "foldername\O365.key"

#Replace the o365 key file that you stored the password.
$TenantPass = cat "O365.key" | ConvertTo-SecureString
$TenantCredentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $TenantUname,$TenantPass

#make sure the exchangeonline module is installed. The script will fail if module not installed.
Import-Module ExchangeOnlineManagement

try { 
    Connect-ExchangeOnline #-UseWebLogin #-Credential $TenantCredentials 
} catch [System.Exception] { 
    $ErrorMessage = $_.Exception.Message
    $FailedItem = $_.Exception.ItemName
    $WebReqErr = $error[0] | Select-Object * | Format-List -Force

    Write-Error "An error occurred while attempting to connect to the requested service.  $ErrorMessage"
    Send-MailMessage -From $From -To  $notify -SmtpServer $smtp -Subject "Failed to connect to exchnage online" -Body "Please check ." 
}

#list of sharepoint sites (* means all sub sites as well)
$SiteURLs = @("https://m******.sharepoint.com/sites/*",
"https://m*******.sharepoint.com/sites/hr-hr/*")

#List of audit logs
$Operations = @('PageViewed','FileAccessed','FileDownloaded','FileDeleted')

#audit logs for 1 days from today's date
$startDate=(Get-Date).AddDays(-1)

#Number of iterations ()
$daysToSkip=3

$endDate=Get-Date #today's date

#iteration start for 3 days
while ($startDate -lt $endDate) {
    $startdate1=$startDate
    $startDate = $startDate.AddDays($daysToSkip)
    $enddate1=$startDate

    $FileAccessLog = Search-UnifiedAuditLog -StartDate $startDate1
    -EndDate $EndDate1 -Operations $Operations -ResultSize 5000 -ObjectIds $SiteURLs

    $FileAccessLog.auditdata | ConvertFrom-Json | Select-Object CreationTime,UserId,Operation,ObjectID,SiteUrl,SourceFileName,ClientIP | `
    Export-Csv $CSVFile -NoTypeInformation -Force -Append
}

if ((import-csv $CSVFile).Length -gt 0)    {
    Send-MailMessage -From $From -To $To -SmtpServer $smtp -Subject $Subject -Body $Body -Attachments $CSVFile 
}

错误信息 #

ConvertFrom-Json : Cannot bind argument to parameter 'InputObject' because it is null.
At line:10 char:28
+ $FileAccessLog.auditdata | ConvertFrom-Json | Select-Object CreationT ...
+                            ~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [ConvertFrom-Json], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ConvertFromJsonCommand

解决方案 #

1. 空值检查，避免ConvertFrom-Json报错 #

当Search-UnifiedAuditLog没有返回任何日志时，$FileAccessLog会是null，导致$FileAccessLog.auditdata为空，传给ConvertFrom-Json就会触发错误。添加空值判断：

$FileAccessLog = Search-UnifiedAuditLog -StartDate $startDate1 -EndDate $EndDate1 -Operations $Operations -ResultSize 5000 -ObjectIds $SiteURLs

# 先检查是否有结果，再处理JSON转换
if ($FileAccessLog -and $FileAccessLog.auditdata) {
    $FileAccessLog.auditdata | ConvertFrom-Json | Select-Object CreationTime,UserId,Operation,ObjectID,SiteUrl,SourceFileName,ClientIP | `
    Export-Csv $CSVFile -NoTypeInformation -Force -Append
}

2. 修复日期循环逻辑 #

原脚本的日期循环逻辑存在问题：$startDate初始为昨天，$daysToSkip=3，第一次循环的结束日期会是昨天+3天（后天），但$endDate是今天，导致循环不会正确执行。如果目标是获取最近1天的日志，直接去掉循环即可：

# 直接获取最近1天的日志，无需循环
$startDate = (Get-Date).AddDays(-1)
$endDate = Get-Date
$FileAccessLog = Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -Operations $Operations -ResultSize 5000 -ObjectIds $SiteURLs

3. 设置正确的脚本路径 #

原脚本中$dir = ""会导致CSV文件和备份路径指向当前工作目录的根，建议设置为脚本所在目录，并确保备份文件夹存在：

# 获取脚本所在目录
$dir = $PSScriptRoot
$date = (Get-Date -f dd-MM-yyyy-hhmmss)
$CSVFile = "$dir\Auditlogs_$date.csv"

# 确保备份文件夹存在
$destination = "$dir\Backup"
if (-not (Test-Path $destination)) {
    New-Item -ItemType Directory -Path $destination | Out-Null
}

4. 启用凭据登录（可选） #

原脚本注释掉了凭据参数，若要使用保存的O365.key文件登录，取消Connect-ExchangeOnline的-Credential参数注释：

Connect-ExchangeOnline -Credential $TenantCredentials

5. 处理日志分页（避免丢失数据） #

Search-UnifiedAuditLog的-ResultSize最大为5000，若日志超过5000条，需要分页获取：

$results = @()
$skip = 0
do {
    $batch = Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -Operations $Operations -ResultSize 5000 -Skip $skip -ObjectIds $SiteURLs
    if ($batch) {
        $results += $batch
        $skip += 5000
    }
} while ($batch.Count -eq 5000)
$FileAccessLog = $results

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