question

EugenieMeng-7024 avatar image
0 Votes"
EugenieMeng-7024 asked EugenieMeng-7024 rolled back

Retrieve user details from Microsoft 365 admin center

Hi,

I have a question on how to retrieve the user details from M365 admin center. I understand there is this "Export users" function from the portal. But I need to establish an automated data feed from Microsoft365 admin center into SQL server DB for reporting purpose. We are mainly interested in understanding which user has what licenses.
127837-image.png

I was wondering if Graph API is the right type of API to retrieve such user details. I tested https://graph.microsoft.com/v1.0/users but it doesn't give License info from the users (e.g. Unlicensed vs Power BI etcs)

Apart from API option, is there a report subscription function from this M365 portal? So that the user details can be sent as a csv file to my email on a weekly/monthly basis.

Thanks for any suggestions.


microsoft-graph-reports
image.png (34.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Danstan-MSFT avatar image
0 Votes"
Danstan-MSFT answered EugenieMeng-7024 rolled back

In your case you should try List Users https://graph.microsoft.com/v1.0/users?$select=assignedLicenses,id which will return assigned license details.

To get it to work with email delivery, you can have an app that has access to subscribedsku and make it aware of them. Then you make your app call GET https://graph.microsoft.com/v1.0/users?$select=assignedLicenses,id which returns something like:

 {
     "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users(assignedLicenses,id)",
     "value": [
         {
             "@odata.id": "https://graph.microsoft.com/v2/...",
             "id": "a74...",
             "assignedLicenses": []
         },
         {
             "@odata.id": "https://graph.microsoft.com/v2/...",
             "id": "a20..",
             "assignedLicenses": [
                 {
                     "disabledPlans": [],
                     "skuId": "c42b9cae-ea4f-4ab7-9717-81576235ccac"
                 }
             ]
         }
     ]
 }

With the subscribedsku and user assignedLicenses, you can have the report. You can also checkout PowerAutomate as this can work well if you call Graph from there but I am not sure because I am still new to PowerAutomate.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Danstan

Thank you very much for sharing this.

I am able to test https://graph.microsoft.com/v1.0/users?$select=assignedLicenses,id, but I am having trouble interpreting the results, the assignedLicenses either returns as empty square bracket [], or returns "disabledPlans" and "skuid" from the JSON file. I had a look at the documentation (https://docs.microsoft.com/en-us/graph/api/resources/assignedlicense?view=graph-rest-1.0) but I am still confused, as I believe I only want to know the assigned licenses (please see below screenshot as an example) that are currently active, not the licenses that are disabled. Could you please help me understand why I can only see the disabledPlans from the return? Clearly there must be something that I've missed or misunderstood.
128908-image.png

Maybe I need both the subscribedsku and user assignedLicenses to figure the licenses assigned to each user, I will try running subscribedsku on Monday when the admin grants the right API permissions for reading the data. Please let me know what your thoughts are and point out any mistakes I have. BTW I am gonna use SSIS to automate the data feed process.

Thank you very much.


0 Votes 0 ·
image.png (9.4 KiB)