The SharePoint Online "User Information List" - a Graph API approach

In the past when looking for SharePoint users I would go down with Get-PnPUser to get the user list from a given SharePoint Online site, the command is quite quite a straight-forward when used interactively or inside of a script, it does what it says, returning a result set of accounts, and from the result set you can further filter out any unwanted resultt when dealing with specific users accounts from unwanted groups or any other criteria that comes to mind.

Beyond this command comes the question that made me wondered, what would take to get users without making use of PowerShell? Would be possible to achieve the same using only Graph API? Moving the needle from Windows PowerShell to let's say, Python or C++? Would this be possible to just make REST call and get the data?

Yeah, I figured the answer, and it is a simple and effective query directly against the SharePoint Online "User Information List".

The only downside for now is the current OData implemented in the Graph API version 1.0 - it is somewhat limited, and it doesn't allow me to combine very useful verbs such as $expand and $filter - which I would often use to create a single query with the desired outcome, given the current limitation from Microsoft, we will we need to interact through the results, and performs some parsing to further filter them to the desired outcome (pretty much the same with Get-PnPUser to be honest), hopefully one day Microsoft will give us the full OData implementation, and we will be able to make our requests from one query combining $expand and $filter to get what we need without parsing the results.

The final query we will be writing will look like the example below, it will be the product from two supporting queries that I will address more ahead, the below is what we will get once we dive through the additional queries:

https://graph.microsoft.com/v1.0/sites/{siteID}/lists/{usersInfoListID}/
items?$expand=fields($select=id,IsSiteAdmin,Deleted,SipAddress)        

The two supporting queries to get the data from the "User Information List" are the following:

1. A query to retrieve the {siteID} from the site containing the list of users
2. A query against the {siteID} (obtained from the first query) to retrieve the {listID} from the "User Information List" under {siteID}

If by any other means, you already have the site ID and the User Information List ID, you can skip to the end of this article, what I'm going to show below is the process of creating the two supporting queries to get these IDs.

The First Query: Site ID please!

The query is made out of your tenant name followed by ".sharepoint.com" (for example: contoso.sharepoint.com), and the last parameter is site name (the actual URL, for example: https://contoso.sharepoint/sites/HR - not the friendly title name "Human Resources", just the "HR" url portion), the final query will look like the following example below:

https://graph.microsoft.com/v1.0/sites/{tenant.sharepoint.com}:/sites/{siteName}

https://graph.microsoft.com/v1.0/sites/contoso.sharepoint.com:/sites/HR        

The colon ":" is part of the query, and without this little punctuation mark, you will be puzzled wondering why HTTP 400 keeps knocking on your door ??

The query above returned a couple of fields but we are only interested on a field called "id", which is basically a long string divided into three parts by commas.

The format of this field is the following: "tenant,guid,guid", for example:

tenant.sharepoint.com,250495f0-9490-4131-9d30-a44fa313ccdd,67c74e3c-b0c5-4777-9509-40882eab1d8e        

In our example above, the {siteID} is the first guid that comes after the first comma: 250495f0-9490-4131-9d30-a44fa313ccdd

The Second Query: User Information List ID!

The second query will use the {siteID} GUID obtained from the first query, and the DisplayName property to match the wanted list, since this list lives under "/_catalogs/users" - there is no way to narrow down by Name property, it is kind of a big deal for the Graph API, and it will only work using the DisplayName.

The query will look like the following:

https://graph.microsoft.com/v1.0/sites/{siteID}/
lists?$filter=DisplayName eq 'User Information List'

https://graph.microsoft.com/v1.0/sites/250495f0-9490-4131-9d30-a44fa313ccdd/
lists?$filter=DisplayName eq 'User Information List'        

The DisplayName will vary according to the language and region of your tenant, on a M365 German tenant the list will be "Benutzerinformationen", use the URL below directly on your browser to get the current display name:

https://tenant.sharepoint.com/sites/siteName/_catalogs/users/

The query will return the list ID from the "User Information List", and once you have this last final ID, we can finally combine it with all the previous results to build the final query to get the list of users (one query to rule them all!)

Show me the Users!

Once you have the IDs from Site and User Information List, you can finally build your query and get the list of users:

https://graph.microsoft.com/v1.0/sites/{siteID}/lists/{usersInfoListID}/
items?$expand=fields($select=id,IsSiteAdmin,Deleted,SipAddress)        

The query above returns all the users, including groups and system accounts from the specified {siteID}, the example below shows a typical result:

The current OData implementation available for Graph is not fully available, we can't combine $filter and $expand to get the job done in one single call, it leaves us to parse each row examining the contents of SipAddress, it will return a valid email address for an account belonging to an actual user, which we can use to filter out nasty things such as "System Account", "NT Service\SPSearch", "Everyone", etc.

In the parsing, you can use Deleted to check whether or not the account has been deleted from the site, and the property IsSiteAdmin is another optional property to identities the Site Administrators, in the example above, I've included also the Title property, just to show the names and make the example a little easier to understand but this property is really not necessary and adding this will just increase the returning payload.

The ID property is the prize you are after, it gives you back the ID for the given user in the site, this is the information you will need to use for submitting updates or creating new records when there is a person field on your request.

SharePoint Online Users have their own ID apart from Azure Active Directory, for example "John Doe" in Azure is "21cdd51b-0ffd-4e53-8851-f175ebeaaf21", but in SharePoint Online he is referred to "24", the reason for this apparent confusion dates back to the times of SharePoint Server, and this is a story for another time.

Filtering out the unwanted results using the properties above will result in something like the result set below:

In the example above I've excluded the deleted accounts, the administrators, and the groups from the results, having only the mere mortal user accounts from the site.

The Sauce ???

I hope this article and all the examples here were helpful in any way, please share your thoughts and let me know if this helps you!

The code below is the PowerShell version for all the work described above, just copy and paste into PowerShell ISE, change the necessary $Token_Body fields to match your environment and see the results for yourself!

clear-host

$RootSite = "tenant-name-goes-here.sharepoint.com"
$Site? ? ?= "HR"

$Token_Body = @{
? ? ? ? ? ? ? ? ? ? "tenant"? ? ? ? = "--- your tenant guid goes here ---"
? ? ? ? ? ? ? ? ? ? "grant_type"? ? = "client_credentials"
? ? ? ? ? ? ? ? ? ? "client_id"? ? ?= "--- your azure application id goes here ---"
? ? ? ? ? ? ? ? ? ? "client_secret" = "--- your azure application secret goes here ---"
? ? ? ? ? ? ? ? ? ? "resource"? ? ? = "https://graph.microsoft.com/"
? ? ? ? ? ? ? ?}

$Token_Params = @{
? ? ? ? ? ? ? ? ? ? "URI"? ? ? ? ?= "https://login.microsoftonline.com/$($Token_Body.tenant)/oauth2/token"
? ? ? ? ? ? ? ? ? ? "Body"? ? ? ? = $Token_Body
? ? ? ? ? ? ? ? ? ? "ContentType" = "application/x-www-form-urlencoded"
? ? ? ? ? ? ? ? ? ? "Method"? ? ? = "POST"
? ? ? ? ? ? ? ? ?}

$Token_GraphAPI = Invoke-RestMethod @Token_Params


#? ?Site ID for $site

$requestSite = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$($RootSite):/sites/$($Site)" `
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?-Headers @{"Authorization" = "Bearer $($Token_GraphAPI.access_token)"} `
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?-ContentType "application/json; charset=utf-8" -Method GET


$siteID = $requestSite.id.Split(",")[1]


#? ?User Information List ID

$requestUsersList = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$($siteID)/lists?`$filter=DisplayName eq 'User Information List'"? `
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -Headers @{"Authorization" = "Bearer $($Token_GraphAPI.access_token)"}? `
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -ContentType "application/json; charset=utf-8" -Method GET

$usersListID = $requestUsersList.value.id


#? ?Getting the list of users from the SharePoint User Information List

$requestUsers = Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/sites/$($siteID)/lists/$($usersListID)/items?`$expand=fields(`$select=id,IsSiteAdmin,Deleted,SipAddress)"? `
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -Headers @{"Authorization" = "Bearer $($Token_GraphAPI.access_token)"}? `
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -ContentType "application/json; charset=utf-8" -Method GET


#? ?The final product is just a list of IDs, we won't need anything else when creating new records for a field person

$users = $requestUsers.value.fields | ? { $_.IsSiteAdmin -eq $false -and $_.Deleted -eq $false -and $_.SipAddress? -ne $null } | select id