Bienvenue à Blogs CodeS-SourceS Identification | Inscription | Aide

Blog Technique de Romelard Fabrice

Les dernières Actualités de Romelard Fabrice (Alias fabrice69 ou F___) principalement autour des technologies Microsoft

Actualités

  • Toutes les actualités et informations sur les technologies Microsoft principalement autour de .NET et SQL Server

Archives

Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis

Dans un cadre de gestion Office 365, il est courant de devoir surveiller ou contrôler l’activité autour de l’ensemble du tenant O365 selon des critères définis (cas d’utilisation douteuse par exemple).

Depuis le site d’administration

Le site d’administration d’Office 365 vous permet d’accéder (selon vos droits) à la partie “Security & Compliance” :

Cette page vous permet alors de fournir certains critères de recherche:

  • Activity type
  • Start & End DateTime
  • UsersID (email or O365 login)
  • File, folder, url or site

Ces options permettent alors d’exécuter cette recherche et l’expoter en CSV ou de créer une alerte .

Toutes les informations sont fournis pour ce site:

En revanche, quelques restrictions existent sur cette solution dont:

  • Nombre de lignes (ou Events) limité à 50’000 max
  • Nombre de signes exportés dans la colonne AuditData est limité aux premiers 3’060 chars

Depuis PowerShell

A partir de nombreuses sources sur le Web, je vous fourni le script PowerShell suivant utilisable comme outil de base. Il travaille directement le jeu de résultat en mémoire pour exporter en CSV un résultat compréhensible.

La commande PowerShell utilisée est détaillée ici:


Function Split-O365AuditLogs-FromO365 ()
{
    #Get the content to process
    Write-host " -----------------------------------------" -ForegroundColor Green

    [string]$username = "YourAdminAccount@yourtenant.onmicrosoft.com"
    [string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt"
    $secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath)
    $UserCredential = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd

    #This will prompt the user for credential
#    $UserCredential = Get-Credential

    $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-LiveID/ -Credential $UserCredential -Authentication Basic -AllowRedirection
    Import-PSSession $Session

    [DateTime]$startDate = "01/01/2019 00:00" #Format: mm/dd/yyyy hh:MM
    [DateTime]$endDate = "01/11/2019 23:59" #Format: mm/dd/yyyy hh:MM
    $SpecifiedUserIDs = "Youremailtoscan@yourtenant.com", "Youremailtoscan2@yourtenant.com" #syntax: "<value1>","<value2>",..."<valueX>".

    $scriptStart=(get-date)
     $sessionName = (get-date -Format 'u')+'o365auditlog'
    # Reset user audit accumulator
    $aggregateResults = @()
    $i = 0 # Loop counter
    Do {
        Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "SpecifiedUserIDs=", $SpecifiedUserIDs
        $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs
        if ($currentResults.Count -gt 0)
        {
            Write-Host ("  Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName )
            # Accumulate the data
             $aggregateResults += $currentResults
            # No need to do another query if the # recs returned <1k - should save around 5-10 sec per user
            if ($currentResults.Count -lt 1000)
            {
                $currentResults = @()
             }
            else
            {
                $i++
             }
        }
    } Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- #
   
    [int]$IntemIndex = 1
    $data=@()
    foreach ($line in $aggregateResults)
    {
        Write-host "  ItemIndex:", $IntemIndex, "- Creation Date:", $line.CreationDate, "- UserIds:", $line.UserIds, "- Operations:", $line.Operations
        Write-host "      > AuditData:", $line.AuditData
        $datum = New-Object -TypeName PSObject
         $Converteddata = convertfrom-json $line.AuditData
       
        $datum | Add-Member -MemberType NoteProperty -Name Id -Value $Converteddata.Id
        $datum | Add-Member -MemberType NoteProperty -Name CreationTimeUTC -Value $Converteddata.CreationTime
        $datum | Add-Member -MemberType NoteProperty -Name CreationTime -Value $line.CreationDate
        $datum | Add-Member -MemberType NoteProperty -Name Operation -Value $Converteddata.Operation
        $datum | Add-Member -MemberType NoteProperty -Name OrganizationId -Value $Converteddata.OrganizationId
         $datum | Add-Member -MemberType NoteProperty -Name RecordType -Value $Converteddata.RecordType
        $datum | Add-Member -MemberType NoteProperty -Name ResultStatus -Value $Converteddata.ResultStatus
        $datum | Add-Member -MemberType NoteProperty -Name UserKey -Value $Converteddata.UserKey
        $datum | Add-Member -MemberType NoteProperty -Name UserType -Value $Converteddata.UserType
        $datum | Add-Member -MemberType NoteProperty -Name Version -Value $Converteddata.Version
        $datum | Add-Member -MemberType NoteProperty -Name Workload -Value $Converteddata.Workload
        $datum | Add-Member -MemberType NoteProperty -Name UserId -Value $Converteddata.UserId
        $datum | Add-Member -MemberType NoteProperty -Name ClientIPAddress -Value $Converteddata.ClientIPAddress
        $datum | Add-Member -MemberType NoteProperty -Name ClientInfoString -Value $Converteddata.ClientInfoString
        $datum | Add-Member -MemberType NoteProperty -Name ClientProcessName -Value $Converteddata.ClientProcessName
        $datum | Add-Member -MemberType NoteProperty -Name ClientVersion -Value $Converteddata.ClientVersion
         $datum | Add-Member -MemberType NoteProperty -Name ExternalAccess -Value $Converteddata.ExternalAccess
        $datum | Add-Member -MemberType NoteProperty -Name InternalLogonType -Value $Converteddata.InternalLogonType
        $datum | Add-Member -MemberType NoteProperty -Name LogonType -Value $Converteddata.LogonType
        $datum | Add-Member -MemberType NoteProperty -Name LogonUserSid -Value $Converteddata.LogonUserSid
        $datum | Add-Member -MemberType NoteProperty -Name MailboxGuid -Value $Converteddata.MailboxGuid
        $datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerSid -Value $Converteddata.MailboxOwnerSid
        $datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerUPN -Value $Converteddata.MailboxOwnerUPN
         $datum | Add-Member -MemberType NoteProperty -Name OrganizationName -Value $Converteddata.OrganizationName
        $datum | Add-Member -MemberType NoteProperty -Name OriginatingServer -Value $Converteddata.OriginatingServer
        $datum | Add-Member -MemberType NoteProperty -Name SessionId -Value $Converteddata.SessionId
   
        $data += $datum
        $IntemIndex += 1
     }
    $datestring = (get-date).ToString("yyyyMMdd-hhmm")
    $fileName = ("C:\AuditLogs\CSVExport\" + $datestring + ".csv")
    
    Write-Host (" >>> writing to file {0}" -f $fileName)
    $data | Export-csv $fileName -NoTypeInformation

    Remove-PSSession $Session
}

Split-O365AuditLogs-FromO365


Vous pouvez utiliser ce script et l’adapter selon vos besoins.

Attention:

La limitation du champs AuditData reste présente même PowerShell, mais Microsoft Support connait le problème et travaille avec le groupe produit pour le fixer

Liens additionnels:

Fabrice Romelard

Version anglaise:

Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
Posted: lundi 28 janvier 2019 16:13 par ROMELARD Fabrice

Commentaires

ROMELARD Fabrice a dit :

Après avoir travaillé et repensé au cas, le script a été amélioré pour supporter les erreurs de conversion JSON.

Désormais, même en cas d'erreur, le champs "AuditDataRaw" permet de stocker la valeur mal formattée ce qui permet tout de même d'avoir les informations en cas de besoin de statistique.

Voici la nouvelle version:

----

Function Split-O365AuditLogs-FromO365 ()

{

   #Get the content to process

Write-host " -----------------------------------------" -ForegroundColor Green

<#

[string]$username = "AdminAccount@yourtenant.com"

[string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt"

$secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath)

$UserCredential = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd

#>

#This will prompt the user for credential

$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-LiveID/ -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $Session

[bool]$specifyPeriod = $true # if false it will be the default period time specified $DefaultPeriodToCheck (last XX days)

[int]$DefaultPeriodToCheck = -7 #last 7 days by default

[DateTime]$startDate = "01/01/2019 00:00" #"01/01/2019 00:00" #Format: mm/dd/yyyy hh:MM #(get-date).AddDays(-1)

[DateTime]$endDate = "01/31/2019 23:59" #"01/11/2019 23:59" #Format: mm/dd/yyyy hh:MM #(get-date)

[bool]$specifyUserIDs = $false

$SpecifiedUserIDs = "UserIdEmailAddress@yourtenant.com" #syntax: "

","",...""

[bool]$specifyRecordTypes = $false

$RecordTypeValues = "SharePoint" #Only one field to put, biggest products: "OneDrive" "SharePoint" "Sway" "PowerBI" "MicrosoftTeams" "MicrosoftStream"

# Possible values:

$scriptStart=(get-date)

$sessionName = (get-date -Format 'u')+'o365auditlog'

# Reset user audit accumulator

$aggregateResults = @()

$i = 0 # Loop counter

Do {

Write-host " -----------------------------------------" -ForegroundColor Yellow

if($specifyPeriod -eq $true)

{

if($specifyUserIDs -eq $true)

{

if ($specifyRecordTypes -eq $true)

{

Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- SpecifiedUserIDs=", $SpecifiedUserIDs, "- RecordType=", $RecordTypeValues -ForegroundColor Magenta

$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs -RecordType $RecordTypeValues

}

else

{

Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- SpecifiedUserIDs=", $SpecifiedUserIDs -ForegroundColor Magenta

$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs

}

}

else

{

if ($specifyRecordTypes -eq $true)

{

Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- RecordType=", $RecordTypeValues -ForegroundColor Magenta

$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType $RecordTypeValues

}

else

{

Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate -ForegroundColor Magenta

$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000

}

}

}

else

{

$enddate = get-date

$startDate = $enddate.AddDays($DefaultPeriodToCheck) #default period is the last week

if($specifyUserIDs -eq $true)

{

if ($specifyRecordTypes -eq $true)

{

Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- SpecifiedUserIDs=", $SpecifiedUserIDs, "- RecordType=", $RecordTypeValues -ForegroundColor Magenta

$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs -RecordType $RecordTypeValues

}

else

{

Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- SpecifiedUserIDs=", $SpecifiedUserIDs -ForegroundColor Magenta

$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs

}

}

else

{

if ($specifyRecordTypes -eq $true)

{

Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- RecordType=", $RecordTypeValues -ForegroundColor Magenta

$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType $RecordTypeValues

}

else

{

Write-host "  >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate -ForegroundColor Magenta

$currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000

}

}

}

if ($currentResults.Count -gt 0)

{

Write-Host ("  Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName )

# Accumulate the data

$aggregateResults += $currentResults

# No need to do another query if the # recs returned <1k - should save around 5-10 sec per user

if ($currentResults.Count -lt 1000)

{

$currentResults = @()

}

else

{

$i++

}

}

} Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- #

[int]$IntemIndex = 1

$data=@()

   foreach ($line in $aggregateResults)

   {

Write-host "  ItemIndex:", $IntemIndex, "- Creation Date:", $line.CreationDate, "- UserIds:", $line.UserIds, "- Operations:", $line.Operations

#Write-host "      > AuditData:", $line.AuditData

$datum = New-Object -TypeName PSObject

try

{

$Converteddata = convertfrom-json $line.AuditData

$datum | Add-Member -MemberType NoteProperty -Name Id -Value $Converteddata.Id

$datum | Add-Member -MemberType NoteProperty -Name CreationTimeUTC -Value $Converteddata.CreationTime

$datum | Add-Member -MemberType NoteProperty -Name CreationTime -Value $line.CreationDate

$datum | Add-Member -MemberType NoteProperty -Name Operation -Value $Converteddata.Operation

$datum | Add-Member -MemberType NoteProperty -Name OrganizationId -Value $Converteddata.OrganizationId

$datum | Add-Member -MemberType NoteProperty -Name RecordType -Value $Converteddata.RecordType

$datum | Add-Member -MemberType NoteProperty -Name ResultStatus -Value $Converteddata.ResultStatus

$datum | Add-Member -MemberType NoteProperty -Name UserKey -Value $Converteddata.UserKey

$datum | Add-Member -MemberType NoteProperty -Name UserType -Value $Converteddata.UserType

$datum | Add-Member -MemberType NoteProperty -Name Version -Value $Converteddata.Version

$datum | Add-Member -MemberType NoteProperty -Name Workload -Value $Converteddata.Workload

$datum | Add-Member -MemberType NoteProperty -Name UserId -Value $Converteddata.UserId

$datum | Add-Member -MemberType NoteProperty -Name ClientIPAddress -Value $Converteddata.ClientIPAddress

$datum | Add-Member -MemberType NoteProperty -Name ClientInfoString -Value $Converteddata.ClientInfoString

$datum | Add-Member -MemberType NoteProperty -Name ClientProcessName -Value $Converteddata.ClientProcessName

$datum | Add-Member -MemberType NoteProperty -Name ClientVersion -Value $Converteddata.ClientVersion

$datum | Add-Member -MemberType NoteProperty -Name ExternalAccess -Value $Converteddata.ExternalAccess

$datum | Add-Member -MemberType NoteProperty -Name InternalLogonType -Value $Converteddata.InternalLogonType

$datum | Add-Member -MemberType NoteProperty -Name LogonType -Value $Converteddata.LogonType

$datum | Add-Member -MemberType NoteProperty -Name LogonUserSid -Value $Converteddata.LogonUserSid

$datum | Add-Member -MemberType NoteProperty -Name MailboxGuid -Value $Converteddata.MailboxGuid

$datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerSid -Value $Converteddata.MailboxOwnerSid

$datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerUPN -Value $Converteddata.MailboxOwnerUPN

$datum | Add-Member -MemberType NoteProperty -Name OrganizationName -Value $Converteddata.OrganizationName

$datum | Add-Member -MemberType NoteProperty -Name OriginatingServer -Value $Converteddata.OriginatingServer

$datum | Add-Member -MemberType NoteProperty -Name SessionId -Value $Converteddata.SessionId

$datum | Add-Member -MemberType NoteProperty -Name LogonError -Value $Converteddata.LogonError

$datum | Add-Member -MemberType NoteProperty -Name Subject -Value $Converteddata.Subject

$datum | Add-Member -MemberType NoteProperty -Name ObjectId -Value $Converteddata.ObjectId

$datum | Add-Member -MemberType NoteProperty -Name SiteUrl -Value $Converteddata.SiteUrl

$datum | Add-Member -MemberType NoteProperty -Name SourceRelativeUrl -Value $Converteddata.SourceRelativeUrl

$datum | Add-Member -MemberType NoteProperty -Name AuditDataRaw -Value $line.AuditData

}

catch

{

Write-host "  =====>>>> JSON FORMAT NOT CORRECT " -ForegroundColor Red

Write-host "  =====>>>> AuditData:", $line.AuditData  -ForegroundColor Yellow

[guid]$NewGuid = [guid]::newguid()

$datum | Add-Member -MemberType NoteProperty -Name Id -Value $NewGuid

$datum | Add-Member -MemberType NoteProperty -Name CreationTimeUTC -Value $line.CreationDate

$datum | Add-Member -MemberType NoteProperty -Name CreationTime -Value $line.CreationDate

$datum | Add-Member -MemberType NoteProperty -Name UserId -Value $line.UserIds

$datum | Add-Member -MemberType NoteProperty -Name Operation -Value $line.Operations

$datum | Add-Member -MemberType NoteProperty -Name AuditDataRaw -Value $line.AuditData

}

$data += $datum

$IntemIndex += 1

}

$datestring = (get-date).ToString("yyyyMMdd-hhmm")

$fileName = ("C:\AuditLogs\CSVExport\" + $datestring + ".csv")

Write-host " -----------------------------------------" -ForegroundColor Green

Write-Host (" >>> writing to file {0}" -f $fileName) -ForegroundColor Green

$data | Export-csv $fileName -NoTypeInformation

Write-host " -----------------------------------------" -ForegroundColor Green

Remove-PSSession $Session

}

cls

Split-O365AuditLogs-FromO365

---

Bonne journée

Fab

# janvier 31, 2019 10:07
Les commentaires anonymes sont désactivés

Les 10 derniers blogs postés

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01

- SharePoint Online: Script PowerShell pour supprimer une colonne dans tous les sites d’une collection par Blog Technique de Romelard Fabrice le 11-27-2018, 18:01

- Office 365: Script PowerShell pour supprimer des comptes utilisateur de collections de sites SharePoint Online par Blog Technique de Romelard Fabrice le 11-19-2018, 16:47

- Retrouvez-moi aux Microsoft experiences18 ! par Blog de Jérémy Jeanson le 11-06-2018, 22:25

- Office 365: Script PowerShell pour extraire les Teams associés à un utilisateur spécifié par Blog Technique de Romelard Fabrice le 11-06-2018, 13:37

- Office 365: Extraire les pages publiées dans un site SharePoint Online de publishing en CSV par Blog Technique de Romelard Fabrice le 11-06-2018, 11:04

- Office 365: Comment créer un jeu de Pages Modernes dans un Modern Site SharePoint Online en utilisant PowerShell depuis un fichier CSV par Blog Technique de Romelard Fabrice le 10-17-2018, 12:50