When working with Infrastructure and services, you would come across situations, for which you know standard solutions but due to cost reasons, you chose manual workarounds. This post would fall among those situations.
Microsoft Sharepoint services came out as a great alternative for File shares to the companies during Covid 19. Not only many automation options via Power Automate (MS Flow earlier) but it came with built-in backup via versioning (life saver I tell you). For those who were not willing to work with Web-Only option, OneDrive was another built-in capability with Windows 10 itself and was able to imitate good old file share mapping to user machines.
But there was a catch with OneDrive…..
Being a new tool, understanding the backend technology was a learning curve and during the journey many business people ended up mass deleting the content from SharePoint site thinking that they are just removing the files from their own machines or stopping the sync only. If caught in time, then SharePoint Administrator would restore the content from First stage or second stage recycle bin (if retention policies not there) or even educate the user accordingly but if not noticed in time then it might become headache. To be fair, Microsoft has many services, which can detect such scenarios and even take actions automatically if capable (like Azure Sentinel, Cloud App security and Retention Policies) but most of them come with considerable cost.
So, the question is, can our good old friend PowerShell help us in detecting such situations at least in some way so that damage may be minimized? Why not?
The below given code would detect if one of the risky operation (move, rename or delete) happens at least for 100 count for files or folders and would send email to given recipients. This script would need to be scheduled for running every 1 5 mins (as PowerShell is limited to read only 5000 records so runs should be narrow). I have kept the line commented but there is an option in the code to send the email to particular recipients as well from whose accounts the actions are done.
# Author : Nitish Kumar
# To Automate Sharepoint Mass Deletion Monitoring
# version 1.0 | 02/09/2021
# version 1.1 | 02/09/2021 | updated to exclude OneDrive sites and better output
# version 1.2 | 03/09/2021 | updated to improve mail functionality and formatting output, also added option to add multiple recipients
# Connection to O365 tenant with a service account with Basic Authentication and no MFA | View Audit logs RBAC role required
$Manual = 0
function ConnectO365 () {
If ([System.IO.File]::Exists("C:\temp\myCred_${env:USERNAME}_${env:COMPUTERNAME}.xml")){
$UserCredential = Import-CliXml -Path "C:\temp\myCred_${env:USERNAME}_${env:COMPUTERNAME}.xml"
} Else {
$Answer = Read-host "Want to create credentials file to re-use (Y/N)"
If ($Answer.ToUpper() -eq "Y") {
Get-Credential -Message "Provide O365 admin credentials" | Export-CliXml -Path "C:\temp\myCred_$($env:USERNAME)_$($env:COMPUTERNAME).xml"
Write-Host "`nCredentials file created." -Foregroundcolor GREEN -Backgroundcolor White
$UserCredential = Import-CliXml -Path "C:\temp\myCred_${env:USERNAME}_${env:COMPUTERNAME}.xml"
} Else {
write-host "`nThese credentials would not be saved for later run." -Foregroundcolor RED -Backgroundcolor YELLOW
$UserCredential = Get-Credential }
}
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Return $Session
}
# Mail configuration
$SMTPServer = "mx.xyz.abc"
$SenderAddress = "aaaaa@xyz.abc"
$RecipientAddressTo = @("aaaaa@xyz.abc") # For multiple recipients, use format like this @("aaaaa@xyz.abc","bbbbbb@xyz.abc")
$RecipientAddressCc = @("ccccc@xyz.abc")
$RecipientAddressBCC = @("dddddd@xyz.abc")
$Subject = "Suspicious Sharepoint Activities"
# Stylesheet for Table formatting in mail body
$style = "<style>BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px; }"
$style = $style + "TD{border: 1px solid black; padding: 5px; }"
$style = $style + "</style>"
# Audit logs to be checked for how many minutes
$ScheduleFrequency = 30
# Threshold for alertinig
$Threshold = 100
# Connect to O365 for Audit log check
Try {
$s = ConnectO365
Import-PSSession $s
} Catch {
Send-MailMessage -From $SenderAddress -To $RecipientAddressTo -Cc $RecipientAddressCc -Bcc $RecipientAddressBCC -Subject "Error: Sharepoint Mass Deletion Monitoring not working" -Body $_.Exception.Message -SmtpServer $SMTPServer -BodyAsHtml -UseSsl
# For manual ad-hoc run
Connect-ExchangeOnline
$Manual = 1
}
Try {
# Audit log query to find out potentially harmful operations
$DataRows = Search-UnifiedAuditLog -StartDate (Get-Date).AddMinutes(-($ScheduleFrequency)) -EndDate (Get-date) -RecordType SharePointFileOperation -Operations FileDeleted, FileMoved, FileRenamed, FolderDeleted, FolderRenamed, FolderMoved -ResultSize 5000 | Select-Object UserIds, Operations, @{l="SharepointSite";e={($_.AuditData | ConvertFrom-Json ).SiteUrl}} |?{$_.SharepointSite -notlike "https://xyz-my.sharepoint.com/personal/*"}| Group-Object UserIds, SharepointSite, Operations | ?{$_.Count -ge $Threshold} | Select-Object @{l="User";e={($_.Name -split ", ")[0]}}, @{l="SharepointSiteName";e={($_.Name -split ", ")[1]}} , @{l="OperationType";e={($_.Name -split ", ")[2]}}, Count
} Catch {
$ErrorMessage = $_.Exception.Message
Write-Host $ErrorMessage -Foregroundcolor RED
Send-MailMessage -From $SenderAddress -To $RecipientAddressTo -Cc $RecipientAddressCc -Subject "Error: Sharepoint Mass Deletion Monitoring not working" -Body $_.Exception.Message -SmtpServer $SMTPServer -BodyAsHtml -UseSsl # -Bcc $RecipientAddressBCC
}
# Log the sessions which completed with errors
If($DataRows.count -gt 0){
ForEach ($Data in $DataRows) {
$Message = "Below actions seem to be of concern, please validate ... "
$Message += "`r`n"
$body = "<b>Dear Team</b><br><br>"
$body += $Message
$body += "<br><br>"
$body += $Data | ConvertTo-Html -Head $style | Out-String
$body += "<br><br><b>Regards</b><br>"
$body += "Monitoring Team <br>"
#$RecipientAddressTo += $Data.User # Enable this one if you need to send emails to particular users as well
Send-MailMessage -From $SenderAddress -To $RecipientAddressTo -Cc $RecipientAddressCc -Subject $Subject -Body $Body -SmtpServer $SMTPServer -BodyAsHtml -UseSsl # -Bcc $RecipientAddressBCC
}
}
If ($Manual -eq 0){ Remove-PSSession $s } Else { Disconnect-ExchangeOnline -Confirm:$false -InformationAction Ignore -ErrorAction SilentlyContinue }
Let me know in comments, how you find this script or if you have any similar or different use case.
Someone asked that what’s do if a large number of files got deleted.. here goes another piece of code, which can target files deleted by certain individual in a certain period of time
Import-Module Pnp.PowerShell
$SiteUrl = "https://xyz.sharepoint.com/sites/abc/"
Connect-PnPOnline -Url $SiteUrl -UseWebLogin
$TargetUser = "Nitish Kumar"
$RecycleBinItem = Get-PnPRecycleBinItem | ?{$_.DeletedByName -eq $TargetUser -AND $_.DeletedDate -ge (Get-Date).AddDays(-2)} #| Select-Object Title, DeletedbyName, DeletedDate, DirName | Export-csv -nti $env:userprofile\desktop\DeletedIitems.csv # Enable last section only if you need to export the data
#$RecycleBinItem = Get-PnPRecycleBinItem -Secondstage | ?{$_.DeletedByName -eq $TargetUser -AND $_.DeletedDate -ge (Get-Date).AddDays(-2)} #| Select-Object Title, DeletedbyName, DeletedDate, DirName | Export-csv -nti $env:userprofile\desktop\DeletedIitems.csv # Enable this one if need to check second stage recycle bin
Write-host "Total of $($RecycleBinItem.Count) files to restore... `r"
$i = 0
foreach ($item in $RecycleBinItem)
{
$i++
Write-Progress -Activity "Restoring Files deleted by $($TargetUser)" -Status "Restore file $i $($item.Title)"
Restore-PnPRecycleBinItem -Identity $item -Force
}
Nitish Kumar's Blog 