SharePoint Online – Get Site URL even if Get-MgReportSharePointSiteUsageDetail is not working

Microsoft have had an issue for over 1,5 years now, then admins wants to use the Get-MgReportSharePointSiteUsageDetail from Graph API to get info about all SharePoint sites within the tenant. The issue is that the Site URL field is not containing any data. In Fellowmind where I work, I have built a tool we use to get all sorts of information abouts a customers Microsoft 365 tenant and we also used the feature about Site URLs until it broke.

I found that using another Graph API endpoint (https://graph.microsoft.com/v1.0/sites), I could then get the Site URL – but this other endpoint does not output as much data as the initial report. So I modified my existing code to first get all sites with Site ID, Site URL and name for the site. I then used this in my original code block to search and find matches then building the final array for output in the report I’m building for our customers.

I wanted to share the code block if someone else needed the same and was missing inspiration about how to overcome. I must inform that I use an App registration in Entra ID with assigned permission to authenticate with. The required permissions are:
– Sites.Read.All
– Reports.Read.All

The code is written for my project, so you may need to alter some of the code to fit your needs, but at least it should give an indication on how to overcome Microsofts issue.

Connect-MgGraph -ClientId $AppId -Certificate $certificate -TenantId $MicrosoftCustomerId

$AccessToken = (Get-MsalToken -ClientId $appId -ClientCertificate $certificate -TenantId $MicrosoftCustomerId).AccessToken

$Headers = @{
    "Authorization" = "Bearer $($AccessToken)"
    "Content-type"  = "application/json"
}

$AllSharePointUsagesDetailsTable = New-Object 'System.Collections.Generic.List[System.Object]'
$SharePointSiteUsageDetailUriResponseTable = New-Object 'System.Collections.Generic.List[System.Object]'

$MgReportSharePointSiteUsageDetailUri = 'https://graph.microsoft.com/v1.0/sites?'
$MgReportSharePointSiteUsageDetailUriResponse = Invoke-RestMethod -Headers $Headers -Uri $MgReportSharePointSiteUsageDetailUri -Method GET

foreach ($URI in $MgReportSharePointSiteUsageDetailUriResponse.Value)
{

    if($URI.isPersonalSite -ne "True")
    {

    $SPSiteID = $URI.id
    $SPSiteID = $SPSiteID.Split(",")[1]

    $objSharePointSiteUsageDetailUriResponseTable = [PSCustomObject]@{
        'SiteID'		          = $SPSiteID
        'name'                  = $URI.name
        'WebURL'                = $URI.webUrl
    }
    $SharePointSiteUsageDetailUriResponseTable.add($objSharePointSiteUsageDetailUriResponseTable)
    }
}


Get-MgReportSharePointSiteUsageDetail -Period "D30" -outFile "$DefaultWorkingDirectory\AllSharePointUsages.csv"
$AllSharePointUsagesDetails = import-csv -path "$DefaultWorkingDirectory\AllSharePointUsages.csv" -Delimiter ","
Remove-Item -path "$DefaultWorkingDirectory\AllSharePointUsages.csv"

foreach ($SharePointUsage in $AllSharePointUsagesDetails)
{
    $SharePointUsageGB = $SharePointUsage."Storage Used (Byte)" / 1000000000

    $SiteURL = $SharePointSiteUsageDetailUriResponseTable | where-object {$_.SiteID -eq $SharePointUsage."Site ID"}

    $objAllSharePointUsagesDetailsTable = [PSCustomObject]@{
        'Site URL'		          = $SiteURL.WebURL
        'Owner Display Name'      = $SharePointUsage."Owner Display Name"
        'Is Deleted'              = $SharePointUsage."Is Deleted"
        'Last Activity Date'      = $SharePointUsage."Last Activity Date"
        'Storage Used GB'         = $SharePointUsageGB
        'Report Period'           = $SharePointUsage."Report Period"
    }
    $AllSharePointUsagesDetailsTable.add($objAllSharePointUsagesDetailsTable)
}