Export SharePoint Online Site Usage Information to CSV using Office 365 Management API

This PowerShell script retrieves usage data for all SharePoint Online sites in your tenant over the past 30 days, including information on unique visitors, views, unique views, users, time, most visited times, which pages or documents are visited most, who visited, when visited, time spent on each page or document, and more. The script uses the Office 365 Management API to authenticate with Azure AD and retrieve usage information, and exports the results to a CSV file that can be used for further analysis. Additional properties such as URL, title, duration, last modified date, site template, and list item IDs can be included in the exported data by modifying the script.

#SharePointOnline #Office365ManagementAPI #PowerShell #UsageAnalytics #DataExport #AzureAD #CSV #SiteUsageInformation #PageUsageInformation #DocumentUsageInformation #Reporting #Analytics #DataAnalysis #Office365 #Microsoft365

# Define variables for your environmen
$clientId = "YOUR_CLIENT_ID"
$clientSecret = "YOUR_CLIENT_SECRET"
$tenantId = "YOUR_TENANT_ID"
$startDate = (Get-Date).AddDays(-30)
$endDate = Get-Date

# Authenticate with Azure AD and get an access token
$authUrl = "https://login.microsoftonline.com/$tenantId/oauth2/token"
$body = @{
    grant_type    = "client_credentials"
    client_id     = $clientId
    client_secret = $clientSecret
    resource      = "https://manage.office.com"
}
$response = Invoke-RestMethod -Uri $authUrl -Method POST -Body $body
$accessToken = $response.access_token

# Get the ActivityData API endpoint URL
$activityDataUrl = "https://manage.office.com/api/v1.0/$tenantId/activity/feed/subscriptions/content"
$headers = @{ Authorization = "Bearer $accessToken" }
$response = Invoke-RestMethod -Uri $activityDataUrl -Headers $headers
$activityDataApiEndpoint = $response.value[0].contentUri

# Get the list of all SharePoint Online sites in your tenant
$sitesUrl = "https://$tenantId-admin.sharepoint.com/_api/SP.SiteManager/enumerateSites?$select=Url"
$response = Invoke-RestMethod -Uri $sitesUrl -Headers @{ Authorization = "Bearer $accessToken" }
$sites = $response.value | Select-Object -ExpandProperty Url

# Loop through each site and retrieve usage information
$results = foreach ($site in $sites) {
    Write-Host "Retrieving usage data for site $($site)..."
    $siteUrlEncoded = [System.Web.HttpUtility]::UrlEncode($site)
    $usageUrl = "https://manage.office.com/api/v1.0/$tenantId/activity/feed/ServiceLog?contentType=Audit.SharePoint&PublisherIdentifier=$siteUrlEncoded&StartTime=$startDate&EndTime=$endDate"
    $response = Invoke-RestMethod -Uri $usageUrl -Headers @{ Authorization = "Bearer $accessToken" }
    $response.value
}

# Export the results to a CSV file
$results | Select-Object -Property CreationTime, UserKey, ItemType, ObjectId, SiteUrl, ClientIP, Activity, ActivityDisplayName, Url, Title, Duration, PageViewType, ItemModifiedDate, LastActivityTime, SiteTemplate, ListItemUniqueId, ListId, ListItemId, FileType | Export-Csv -Path "SharePointUsageReport.csv" -NoTypeInformationt        

Here's a sample of what the exported CSV file might look like, including some of the additional properties:

"CreationTime","UserKey","ItemType","ObjectId","SiteUrl","ClientIP","Activity","ActivityDisplayName","Url","Title","Duration","PageViewType","ItemModifiedDate","LastActivityTime","SiteTemplate","ListItemUniqueId","ListId","ListItemId","FileType
"2022-05-20T19:53:07","[email protected]","Document","12345","https://contoso.sharepoint.com/teams/Sales","192.168.0.1","Viewed","Viewed Document","https://contoso.sharepoint.com/teams/Sales/Documents/MarketingReport.docx","Marketing Report.docx","00:02:30","Web","2022-05-19T16:21:12","2022-05-20T19:53:07","STS#3","4c5e0e6d-5587-4f35-b7de-f84dd9f4d920","a3b6d8e6-0587-408d-a4a7-6faf2993c6f2","11","docx"
"2022-05-20T20:12:34","[email protected]","Page","54321","https://contoso.sharepoint.com/teams/Marketing","192.168.1.1","Viewed","Viewed Page","https://contoso.sharepoint.com/teams/Marketing/SitePages/Home.aspx","Home.aspx","00:01:15","Mobile","2022-05-20T08:43:22","2022-05-20T20:12:34","SITEPAGEPUBLISHING#0","00000000-0000-0000-0000-000000000000","00000000-0000-0000-0000-000000000000","00000000-0000-0000-0000-000000000000","aspx""        

In this example, there are two rows of data. The first row shows that John Doe viewed a document with object ID 12345 on May 20, 2022 at 19:53:07, from client IP address 192.168.0.1. The URL and title of the document are included in the exported data, as well as the duration of the activity, the page view type (i.e., web), the last modified date of the item, and the site template, list item unique ID, list ID, list item ID, and file type.

The second row shows that Jane Doe viewed a page with object ID 54321 on May 20, 2022 at 20:12:34, from client IP address 192.168.1.1. The URL and title of the page are included, along with the duration of the activity, the page view type (i.e., mobile), and the last modified date of the item.