SharePoint sites and data enterprise cost allocation

[M365Power]

From an admin standpoint how could you manage cost centers / cost allocation to SharePoint Online Farm? Would be ideal to add a custom column in the SharePoint admin center to the sites so that cost can be managed per business unit and when needing to purchase more space to cost allocate. Is this possible with any other tools Microsoft has such as SharePoint Syntax or Azure Purview? Thank you. 

[#2]

From an admin standpoint, managing cost centers and cost allocation to SharePoint Online can be challenging, as there is no native feature in SharePoint Online to add a custom column in the SharePoint admin center to the sites. However, there are some possible workarounds that I've used that you can try, such as:

- Using PowerShell scripts to create and update site properties with cost center information, and then use the SharePoint Online Management Shell cmdlets to get site usage reports and filter by site properties.

Then use Microsoft Graph API to access and update site properties with cost center information, and then use the Microsoft Graph reports API to get site usage reports and filter by site properties. You can then display this in a PowerBI report.

If you don't want to go this route then third party is your only option to provide cost management features for SharePoint Online, I've used such Rencore, ShareGate, AvePoint, before at clients. These tools can help you track and allocate costs by site, site collection, or tenant level, and provide reports and dashboards for cost analysis and optimization.

I hope this answer helps you with your query. If you have any further questions, please feel free to ask. 🙂

[#3]

Thank you for your reply. The first options do seem complex and unless documented well I am sure we will not be able to go that route. Do you have any reference documents suggesting this path? Second options, we do have ShareGate but not familiar how we would accomplish with what we typically use only for migrations -any reference material or links much appreciated. Thank you.

[#4]

> The first options do seem complex and unless documented well I am sure we will not be able to go that route

To be honest it isn't, you're tagging who owns which site when created for example and then querying the data. And displaying it in PowerBI to make it easier to see and transform the data in a form of a dashboard, you can export it to a random excel or SharePoint list etc if you also want.


>we do have ShareGate but not familiar how we would accomplish with what we typically use only for migrations

Check out reporting:

https://sharegate.com/reporting

[#5]

Ok I think I understand a bit more now that you mention ShareGate reporting. Instead of trying to boil the ocean and asking Microsoft to make a change to the SharePoint Online farm column so we can track in live production or purchasing an additional Microsoft product such as Azure Purview or SharePoint Syntax; export information and maybe use the site owner as a way to assign a cost center ID to associated with but in an Excel or MS List manually tracking. Am I understanding this correctly now?

[#6]

@M365Power 

Yes and no!

So Microsoft won't make that change, I know several people for large organisations that have tried and gotten denied. Azure Purview and SharePoint Syntax don't have the possibility to to get this information.

So the option is to build something yourself or buy, Luckily it's pretty simple do build.

1. If your AD is up to date and proper department and you don't have orphaned sites etc.:

So first you need a script that get's all the sites and it's get's the owner. And then you do a lookup to the owner to see the department. Here's a script I created that you can use:

# This PowerShell script will get all SharePoint Online sites, their data usage, their owner and department of the owner and export the results to a CSV file.

# Load SharePoint Online Management Shell
Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking

# Connect to SharePoint Online Admin Center
Connect-SPOService -Url https://contoso-admin.sharepoint.com

# Get all site collections
$Sites = Get-SPOSite -Limit All

# Create an empty array to store the results
$Results = @()

# Loop through each site collection
foreach ($Site in $Sites) {

    # Get the site URL, data usage and owner
    $SiteUrl = $Site.Url
    $SiteUsage = $Site.StorageUsageCurrent
    $SiteOwner = $Site.Owner

    # Get the owner's department from Azure AD
    $Owner = Get-AzureADUser -ObjectId $SiteOwner
    $OwnerDepartment = $Owner.Department

    # Create a custom object with the properties
    $Result = [PSCustomObject]@{
        SiteUrl = $SiteUrl
        SiteUsage = $SiteUsage
        SiteOwner = $SiteOwner
        OwnerDepartment = $OwnerDepartment
    }

    # Add the object to the array
    $Results += $Result
}

# Export the array to a CSV file
$Results | Export-Csv -Path "C:\Temp\Sites.csv" -NoTypeInformation

2. If the AD isn't properly (aka department isn't tagged)

Then follow my previous post. This tags the site in-case the owner quits, orphaned sites etc. (reason I wrote it first)

[#7]

Thank you very much - I'll will give this a try.

[#8]

The results of script above gives an error for each site it is trying to run against - is something missing?
$Owner = Get-AzureADUser -ObjectId $SiteOwner
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-AzureADUser], AadNeedAuthenticationException
+ FullyQualifiedErrorId : Microsoft.Open.Azure.AD.CommonLibrary.AadNeedAuthenticationException,Microsoft.Open.Azur
eAD16.PowerShell.GetUser

Get-AzureADUser : Cannot bind argument to parameter 'ObjectId' because it is an empty string.

[#9]

$SiteOwner is empty, so your Site proabably doesn't have any owner. You need to do add a check for that 🙂

Side note: Always make sure to have two owners on all sites/group. This is best practice

[#10]

Ok so I see the issue here - it is not picking up on the 365 Group Owner group. Is there a way to pick up on the 365 Group owner group?

[#11]

@M365Power 

This will get M365 groups. I'll have you try to see what you need to merge between this and previous script to get the output you want   Ping me if you get stuck

# Define the output file path
$OutputFile = "C:\temp\M365Groups.csv"

# Get all M365 groups
$Groups = Get-MgGroup -All

# Create an empty array to store the results
$Results = @()

# Loop through each group
foreach ($Group in $Groups) {

  # Get the group owner
  $Owner = Get-MgGroupOwner -GroupId $Group.Id

  # Create a custom object with the group and owner details
  $Object = [PSCustomObject]@{
    "Group Name" = $Group.DisplayName
    "Group Email" = $Group.Mail
    "Group Type" = $Group.GroupTypes -join ", "
    "Owner Name" = $Owner.DisplayName
    "Owner Email" = $Owner.Mail
  }

  # Add the object to the results array
  $Results += $Object
}

# Export the results to a CSV file
$Results | Export-Csv -Path $OutputFile -NoTypeInformation

# Write a message to indicate the script is done
Write-Host "The script has completed. The output file is located at $OutputFile