How i can get the logs for 2 sharepoint sites for those operations "'PageViewed','FileAccessed','FileDownloaded','FileDeleted'" using PnP Power Shell

Question

We have 2 SharePoint online sites, and we want to generate a .csv file which contain the logs for those operations 'PageViewed','FileAccessed','FileDownloaded','FileDeleted'... now i found this script @ https://github.com/eskonr/MEMPowered/blob/master/Scripts/Office365/o365-Auditlogs-sharepointsites.ps1 which I tailored to my need, as follow:-

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 = $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  
  
#For a list of audit logs, refer https://learn.microsoft.com/en-us/microsoft-365/compliance/search-the-audit-log-in-security-and-compliance?view=o365-worldwide#file-and-page-activities  
  
$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 }  

now i got this error:-

and the generated .csv file will be empty in my case.

any advice why my script is not working as intended ? and how i can generate a csv file which contain the logs for 2 SharePoint sites for specific operations and within a date range (for one day as shown in my above example)?

Answer 1

Hi @john john
You could try this code:

#Set-ExecutionPolicy RemoteSigned  
  
# Option 1 - This can be used to be prompted for credentials  
$UserCredential = Get-Credential  
  
# Create the session to Exchange Online  
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -Uri https://outlook.office365.com/powershell-liveid/  -Credential $UserCredential -Authentication Basic -AllowRedirection  
  
# Import the Exchange Online commands  
Import-PSSession $Session  
$csvFile = "c:\test\auditlog2.csv"  
  
# Setup our start and end dates to pull back events  
#$start = Get-Date  
$end = Get-Date  
$start = $end.AddDays(-1)  
$i = 1;  
$startTime = Get-Date  
  
do  
{  
    $AuditData = Search-UnifiedAuditLog -StartDate $start -EndDate $end -RecordType SharePointFileOperation -ResultSize 5000 -SessionCommand ReturnLargeSet -SessionId "ExtractLogs" -SiteIds yoursiteid  
    $ConvertedOutput = $AuditData | Select-Object -ExpandProperty AuditData | ConvertFrom-Json  
    $ConvertedOutput | SELECT CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent | Export-csv $csvFile -NoTypeInformation -Append -Force   
    Write-Host $i++ . $AuditData.Count  
    $i = $i + 1;  
}  
Until($AuditData.Count -eq 0)  
  
$endTime = Get-Date  
Write-Host $startTime - $endTime  
  
#Load SharePoint CSOM Assemblies  
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"  
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
  
#Get Site Id  
$credential = Get-Credential  
$context = New-Object Microsoft.SharePoint.Client.ClientContext("yoursiteurl")  
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($credential.UserName,$credential.Password)  
$site = $context.Site   
$context.Load($site)  
$context.ExecuteQuery();  
$site.Id  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.