While reading on Reddit, I found a common thread. People need a quick way to do a Share Point File Audit. I have a PowerShell function for this in my toolbox. This tool heavily uses the Search-UnifiedAuditLog command let. The most common items I tend to audit are file modifications and deletions. This function goes through, modified, moved, renamed, downloaded, uploaded, accessed, synced, malware detection, restored from trash, locked, and finally unlocked. The Search-UnifiedAuditLog is an exchange online command at the time of this writing. Thus, you need to connect to exchange online. In this function, I am using the switch command. I will follow that structure for the breakdown. Lets first jump in with the function.
The Function
function Invoke-SharePointFileAudit {
[cmdletbinding()]
param (
[Parameter(Mandatory = $true)][validateset("Deleted", "Modified", "Moved", "Renamed", "Downloaded", "Uploaded", "Synced", "Accessed", "MalwareDetected", "Restored", "Locked", "unLocked")][string]$Type,
[parameter(Mandatory = $false)][switch]$KeepAlive,
[switch]$SharePointOnline,
[switch]$OneDrive,
[Nullable[DateTime]]$StartDate,
[Nullable[DateTime]]$EndDate,
[string]$Outfile,
[int]$ResultSize = 5000
)
Begin {
$Module = Get-Module ExchangeOnlineManagement -ListAvailable
if ($Module.count -eq 0) {Install-Module ExchangeOnlineManagement -Repository PSGallery -AllowClobber -Force}
$getsessions = Get-PSSession | Select-Object -Property State, Name
$isconnected = (@($getsessions) -like '@{State=Opened; Name=ExchangeOnlineInternalSession*').Count -gt 0
If ($isconnected -ne "false") {
try {
Connect-ExchangeOnline
}
catch {
Write-Error "Exchange Online Failed. Ending"
end
}
}
#Auto Generates Start and Finish dates
if ($Null -eq $StartDate) { $StartDate = ((Get-Date).AddDays(-89)).Date }
if ($Null -eq $EndDate) { $EndDate = (Get-Date).Date }
#Tests if end date is before start date.
if ($EndDate -lt $StartDate) { $StartDate = ((Get-Date).AddDays(-89)).Date }
if ($EndDate -gt (Get-Date).Date) { $EndDate = (Get-Date).Date }
}
Process {
switch ($Type) {
"Deleted" {
$DeletedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileDeleted,FileDeletedFirstStageRecycleBin,FileDeletedSecondStageRecycleBin,FileVersionsAllDeleted,FileRecycled" -SessionId deleted -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($DeletedRecord in $DeletedRecords) {
$JSONInfo = $DeletedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStampe = ($JSONInfo.creationtime).tolocaltime()
UserName = $DeletedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
RelativeURL = $JSONInfo.SourceRelativeUrl
FileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Modified" {
$ModifiedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileModified,FileModifiedExtended" -SessionId Modified -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($ModifiedRecord in $ModifiedRecords) {
$JSONInfo = $ModifiedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $ModifiedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
RelativeURL = $JSONInfo.SourceRelativeUrl
FileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Moved" {
$MovedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileMoved" -SessionId Moved -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($MovedRecord in $MovedRecords) {
$JSONInfo = $MovedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $MovedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
SourceRelativeURL = $JSONInfo.SourceRelativeUrl
DestinationRelativeURL = $JSONInfo.DestinationRelativeURL
FileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Renamed" {
$RenamedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileRenamed" -SessionId Renamed -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($RenamedRecord in $RenamedRecords) {
$JSONInfo = $RenamedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $RenamedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
SourceRelativeURL = $JSONInfo.SourceRelativeUrl
SourceFileName = $JSONInfo.SourceFileName
DestinationFileName = $JSONInfo.DestinationFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Downloaded" {
$DownloadedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileDownloaded" -SessionId Downloaded -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($DownloadedRecord in $DownloadedRecords) {
$JSONInfo = $DownloadedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $DownloadedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
SourceRelativeURL = $JSONInfo.SourceRelativeUrl
SourceFileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Uploaded" {
$UploadedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileUploaded" -SessionId Uploaded -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($UploadedRecord in $UploadedRecords) {
$JSONInfo = $UploadedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $UploadedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
SourceRelativeURL = $JSONInfo.SourceRelativeUrl
SourceFileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Synced" {
$SyncedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileSyncDownloadedFull,FileSyncUploadedFull" -SessionId Synced -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($SyncedRecord in $SyncedRecords) {
$JSONInfo = $SyncedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $SyncedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
SourceRelativeURL = $JSONInfo.SourceRelativeUrl
SourceFileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Accessed" {
$AccessedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileAccessed,FileAccessedExtended" -SessionId Accessed -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($AccessedRecord in $AccessedRecords) {
$JSONInfo = $AccessedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $AccessedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
SourceRelativeURL = $JSONInfo.SourceRelativeUrl
SourceFileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"MalwareDetected" {
$MalewareRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileMalwareDetected" -SessionId MalewareRecords -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($MalewareRecord in $MalewareRecords) {
$JSONInfo = $MalewareRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $MalewareRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
RelativeURL = $JSONInfo.SourceRelativeUrl
FileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Restored" {
$RestoredRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileRestored" -SessionId RestoredRecords -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($RestoredRecord in $RestoredRecords) {
$JSONInfo = $RestoredRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $RestoredRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
RelativeURL = $JSONInfo.SourceRelativeUrl
FileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"Locked" {
$LockedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "LockRecord" -SessionId Locked -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($LockedRecord in $LockedRecords) {
$JSONInfo = $LockedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $LockedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
RelativeURL = $JSONInfo.SourceRelativeUrl
FileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
"unLocked" {
$unLockedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "UnlockRecord" -SessionId UnlockRecord -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($unLockedRecord in $unLockedRecords) {
$JSONInfo = $unLockedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStamp = ($JSONInfo.creationtime).tolocaltime()
UserName = $unLockedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
RelativeURL = $JSONInfo.SourceRelativeUrl
FileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
}
}
}
end {
if (!($SharePointOnline -and $OneDrive) -or ($SharePointOnline -and $OneDrive)) {
if ($PSBoundParameters.ContainsKey("OutFile")) {
$Return | Export-Csv ./$Outfile.CSV
}
else {
$Return
}
}
elseif ($SharePointOnline) {
if ($PSBoundParameters.ContainsKey("OutFile")) {
$Return | where-object { $_.workload -like "SharePoint" } | Export-Csv ./$Outfile.CSV
}
else {
$Return | where-object { $_.workload -like "SharePoint" }
}
}
elseif ($OneDrive) {
if ($PSBoundParameters.ContainsKey("OutFile")) {
$Return | where-object { $_.workload -like "OneDrive" } | Export-Csv ./$Outfile.CSV
}
else {
$Return | where-object { $_.workload -like "OneDrive" }
}
}
if (!($KeepAlive)) {
Disconnect-ExchangeOnline -Confirm:$false -InformationAction Ignore -ErrorAction SilentlyContinue
}
}
}
The Breakdown of Share Point File Audit
I’m glad you came to the breakdown. It means you want to know how the code works. This means you truly care about learning. Thank you. This code repeats itself a few times in different ways. So, I will call out the differences, but not the likes after the first time explaining something. The first section is our Parameters.
Parameters
We have 8 Parameters, and only one of them is mandatory. Firstly, we have the Type parameter. This mandatory validate set allows you to select from a list of commands we will be using in this function.
- Deleted
- Modified
- Created
- Moved
- Renamed
- Downloaded
- Uploaded
- Synced
- Accessed
- MalwareDetected
- Restored
- Locked
- UnLocked
Afterward, we have Keep Alive. This allows us to run the command multiple times without signing back into the system. So, if you want to keep your session alive, flip that flag. Next, we have two switches. The first Switch is to pull only items edited in SharePoint itself. The next is for one drive. They are named accordingly. After that, we have a start date and an end date. These values are nullable. Basically, you don’t need them. The outfile is asking for just the name of the file. We are using the “./” to save it wherever you run the command from. Finally, we have the result size. If you want the max number of results, 5000. However, you can make this number smaller.
Begin
In our begin section, we want to test the Exchange Online Management Module. Secondly, we want to validate exchange connectivity. After that, we want to gather the date information for the start and end dates. Let’s take a look at the exchange part first.
$Module = Get-Module ExchangeOnlineManagement -ListAvailable
The Get-Module command works with PowerShell 5.1. However, I have seen PowerShell flak with this command failing to pull the information. I am going to assume your PowerShell is up to date with your current version.
if ($Module.count -eq 0) {
Install-Module ExchangeOnlineManagement -Repository PSGallery -AllowClobber -Force
}
Afterward, we want to install the exchange online management module if we don’t detect the module. We are using the count to see how many objects are inside our module variable. If it’s 0, it’s time to install. We install it from the PSGallery.
$getsessions = Get-PSSession | Select-Object -Property State, Name
$isconnected = (@($getsessions) -like '@{State=Opened; Name=ExchangeOnlineInternalSession*').Count -gt 0
Now, we test exchange connections. We use the Get-PSSession to review the current connections. Next, we test if the connections with the name “ExchangeOnlineInternalSession” is greater than zero. “isconnected” will produce a true or false statement.
If ($isconnected -ne "false") {
try {
Connect-ExchangeOnline
} catch {
Write-Error "Exchange Online Failed. Ending"
end
}
}
After which, we can test with. False, we try to connect. However, if there is an error, we end the script and let the user know. We are not using a credential object to authenticate because MFA should always be a thing.
#Auto Generates Start and Finish dates
if ($Null -eq $StartDate) { $StartDate = ((Get-Date).AddDays(-89)).Date }
if ($Null -eq $EndDate) { $EndDate = (Get-Date).Date }
#Tests if end date is before start date.
if ($EndDate -lt $StartDate) { $StartDate = ((Get-Date).AddDays(-89)).Date }
if ($EndDate -gt (Get-Date).Date) { $EndDate = (Get-Date).Date }
Afterward, we need to get the dates right. If the start date is null, we are going to pull 90 days back. We do this by using the standard. We do the same with the end date. If it’s null, we grab today’s date. Now to prevent errors, we check the start date and end date. The end date can’t be before the start date. This is similar to the end date. The end date can’t be greater than the current date. We use the if statement to resolve this.
Process
We begin the process by looking directly at our “Type” variable by using a switch command. The switch allows us to go through each “Type” and run the commands accordingly. Let’s look at one of the switch processes.
$DeletedRecords = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "FileDeleted,FileDeletedFirstStageRecycleBin,FileDeletedSecondStageRecycleBin,FileVersionsAllDeleted,FileRecycled" -SessionId deleted -SessionCommand ReturnLargeSet -ResultSize 5000
$Return = foreach ($DeletedRecord in $DeletedRecords) {
$JSONInfo = $DeletedRecord.AuditData | convertfrom-json
[pscustomobject][ordered]@{
TimeStampe = ($JSONInfo.creationtime).tolocaltime()
UserName = $DeletedRecord.UserIds
ClientIP = $JSONInfo.ClientIP
Source = $JSONInfo.EventSource
Workload = $JSONInfo.Workload
Operation = $JSONInfo.Operation
SiteURL = $JSONInfo.SiteURL
RelativeURL = $JSONInfo.SourceRelativeUrl
FileName = $JSONInfo.SourceFileName
ObjectID = $JSONInfo.ObjectId
}
}
The data that search-unifiedauditlog produces a section called “AuditData”. This section has almost every piece of information you will need. The difference between each “Type” will be the Operations, and session id. The operations target the required logs. This creates the backbone of the Share Point File Audit. The graph below will show which operations I am using. Once you gather the operation information, we need to pull the AuditData. This data will be in JSON format. We start off by looping the records with a for each loop. Then we pull the auditdata and pipe it into convertfrom-json. Next, we create our PS Custom Object. Other than Moved, the output of the other logs contains almost the same information. See the script for the information.
Operation Filters
- Deleted
- FileDeleted
- FileDeletedFirstStageRecycleBin
- FileDeletedSecondStageRecycleBin
- FileVersionsAllDeleted
- FileRecycled
- Modified
- FileModified
- FileModifiedExtended
- Moved
- FileMoved
- Renamed
- FileRenamed
- Downloaded
- FileDownloaded
- Uploaded
- FileUploaded
- Synced
- FileSyncDownloadedFull
- FileSyncUploadedFull
- Accessed
- FileAccessed
- FileAccessedExtended
- MalwareDetected
- FileMalwareDetected
- Restored
- FileRestored
- Locked
- LockRecord
- UnLocked
- UnlockRecord
End
Finally, it’s time for the end block. This is where we will present the data we have gathered. Firstly, we need to determine if the SharePoint or Onedrives were flipped or not.
if (!($SharePointOnline -and $OneDrive) -or ($SharePointOnline -and $OneDrive)) {
if ($PSBoundParameters.ContainsKey("OutFile")) {
$Return | Export-Csv ./$Outfile.CSV
} else {
$Return
}
}
Here we checking if both flags are not checked or if both flags are checked. Then we check if the user gave us a filename. If they did, we export our report to a csv file wherever we are executing the function from. However, if the user didn’t give us a filename, we just dump all the results.
elseif ($SharePointOnline) {
if ($PSBoundParameters.ContainsKey("OutFile")) {
$Return | where-object { $_.workload -like "SharePoint" } | Export-Csv ./$Outfile.CSV
}
else {
$Return | where-object { $_.workload -like "SharePoint" }
}
}
elseif ($OneDrive) {
if ($PSBoundParameters.ContainsKey("OutFile")) {
$Return | where-object { $_.workload -like "OneDrive" } | Export-Csv ./$Outfile.CSV
}
else {
$Return | where-object { $_.workload -like "OneDrive" }
}
}
if (!($KeepAlive)) {
Disconnect-ExchangeOnline -Confirm:$false -InformationAction Ignore -ErrorAction SilentlyContinue
}
Now, if the user selected either or, we present that information. We present those infos by using a where-object. Like before we ask if the user produced an outfile. Finally, we ask if keep alive was set. If it wasn’t we disconnect from the exchange.
Conclusion
In conclusion, auditing shouldn’t be difficult. We can quickly pull the info we need. I hope you enjoy this powerful little tools.