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

john john 1,021 Reputation points
2022-12-14T23:44:05.327+00:00

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:-

270696-image.png

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)?

Microsoft 365 and Office | SharePoint Server | For business
Microsoft 365 and Office | SharePoint | For business | Windows
{count} votes

1 answer

Sort by: Most helpful
  1. Yi Lu_MSFT 17,616 Reputation points
    2023-01-03T01:30:06.23+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.