Ms Graph powewrshell to export user licenses and other attributes entry

Comp_tech 20 Reputation points
2023-05-05T10:18:23.9666667+00:00

Hi everyone,

I am working on a MS Graph PowerShell script to export targeted groups members and I am having issues with pulling all the information I need in a single CSV file so I hope someone can help me to achieve it.

What I am aiming to achieve is

1- Export targeted more than 1 group (9 groups total) in a singled CSV

2- Include userid, upn, display name, Group name, License, Company name and onPremisesExtensionAttributes (ExtensionAttribute9, ExtensionAttribute10, ExtensionAttribute11, ExtensionAttribute12)

Also if a user have more than 2 licenses assigned I want them to be in a different row rather than having them in a single row. Outcome will be like attached image

Capture

I have wrote 2 different scripts but I am not really sure how to combine them and target more than 1 groups

#This is for exporting some basic information for every user

Get-MgUser -All -Property ID,DisplayName,UserPrincipalName,companyName,onPremisesExtensionAttributes | Select-Object ID,DisplayName,UserPrincipalName,companyName -ExpandProperty onPremisesExtensionAttributes ExtensionAttribute9, ExtensionAttribute10, ExtensionAttribute11, ExtensionAttribute12 | Export-CSV "C:\_Scripts\$(get-date -f dd-MM-yyyy).csv" -NoTypeInformation	

#This is exporting a single group members

$users = @()
foreach ($member in $members) {
    $user = Get-MgUser -UserId $member.Id
    $licenses = $null
    $licenses = (Get-MgUserLicenseDetail -UserId $User.id).SkuPartNumber -join ", "
    Write-Host "Licenses found for $($User.DisplayName): $licenses"
    $users += New-Object PSObject -Property @{
                ID   = $user.ID
                USERPRINCIPALNAME = $user.Mail               
                Name = $user.DisplayName
                Group = $group.DisplayName                  
                Licenses = $licenses}                             
                 
}
$group = Get-MgGroup -Filter "DisplayName eq '************'"
$members = Get-MgGroupMember -GroupId ********
$users | Format-Table
$users | Export-CSV "C:\_Scripts\test.csv" -NoTypeInformation

Thanks

Microsoft Graph
Microsoft Graph
A Microsoft programmability model that exposes REST APIs and client libraries to access data on Microsoft 365 services.
11,073 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,222 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Patchfox 3,796 Reputation points
    2023-05-06T15:47:53.65+00:00

    Hi Comp_tech, I want to help you with this question.

    To export each license for a specific user in a specific group you have to loop through each Object to flatten the output.

    I would do it this way:

    $groups = @(Group1, Group2, ....)
    
    $users = @()
    
    foreach ($group in $groups) {
        $groupObj = Get-MgGroup -Filter "Displayname $($group)"
        foreach ($member in $members) {
            $user = Get-MgUser -UserId $member.Id -Property ID,DisplayName,UserPrincipalName,companyName,onPremisesExtensionAttributes | Select-Object ID,DisplayName,UserPrincipalName,companyName -ExpandProperty onPremisesExtensionAttributes ExtensionAttribute9, ExtensionAttribute10, ExtensionAttribute11, ExtensionAttribute12 
            $licenses = $null
            $licenses = (Get-MgUserLicenseDetail -UserId $User.id).SkuPartNumber 
            foreach ($license in $licenses) {
                Write-Host "Licenses found for $($User.DisplayName): $license"
                $users += New-Object PSObject -Property @{
                    ID                = $user.ID
                    USERPRINCIPALNAME = $user.Mail               
                    Name              = $user.DisplayName
                    Group             = $groupObj.DisplayName                  
                    Licenses          = $license
                    Company           = $user.CompanyName
                    ExtensionAttribute9 = $user.ExtensionsAttribute9
                    [AND SO ON...]
                }   
            }   
        }
    }
    
    

    Please keep in mind that this solution will eventually return multiple objects for each user if they are in multiple groups.


    If the reply was helpful, please don’t forget to upvote or accept it as an answer, thank you.

    1 person found this answer helpful.

  2. Rich Matheisen 45,416 Reputation points
    2023-05-10T15:43:23.9566667+00:00

    I think you've forgotten the need for the Get-MGGroup and Get-MGGroupMember cmdlets:

    $GroupsWithSpaces = @"
    SG M365 Business-Premium Sales
    SG M365 Business-Premium Accounts
    "@
    $groups = $GroupsWithSpaces -split "`r`n"
    
    $users = @()
    
    foreach ($group in $groups) {
        $g = Get-MgGroup -Filter "DisplayName eq '$group'"
        if ($g) {   # this won't check to see if the variable contains more than one group!!!
            $members = Get-MgGroupMember -GroupId $g.ID
                foreach ($member in $members) {
                    $user = Get-MgUser -UserId $member.Id -Property ID, DisplayName, UserPrincipalName, companyName, onPremisesExtensionAttributes | Select-Object ID, DisplayName, UserPrincipalName, companyName -ExpandProperty onPremisesExtensionAttributes ExtensionAttribute9, ExtensionAttribute10, ExtensionAttribute11, ExtensionAttribute12 
                    $licenses = $null
                    $licenses = (Get-MgUserLicenseDetail -UserId $User.id).SkuPartNumber 
                    foreach ($license in $licenses) {
                        Write-Host "Licenses found for $($User.DisplayName): $license"
                        $users += [PSCustomObject]@{
                            ID                   = $user.ID
                            USERPRINCIPALNAME    = $user.Mail               
                            Name                 = $user.DisplayName
                            Group                = $groupObj.DisplayName                  
                            Licenses             = $license
                            Company              = $user.CompanyName
                            ExtensionAttribute9  = $user.ExtensionsAttribute9
                            ExtensionAttribute10 = $user.ExtensionsAttribute10
                            ExtensionAttribute11 = $user.ExtensionsAttribute11
                            ExtensionAttribute12 = $user.ExtensionsAttribute12
                        }   
                    }   
                }
        }
    }
    $users | Format-Table
    $users | Export-Csv "C:\_Scripts\Business-Premium.csv" -NoTypeInformation
    

  3. Rich Matheisen 45,416 Reputation points
    2023-05-11T15:31:22.3966667+00:00

    Try this:

    $GroupsWithSpaces = @"
    Group1
    Group2
    "@
    $groups = $GroupsWithSpaces -split "`r`n"
    
    $users = @()
    
    foreach ($group in $groups) {
        $g = Get-MgGroup -Filter "DisplayName eq '$group'"
        if ($g) {   # this won't check to see if the variable contains more than one group!!!
            $members = Get-MgGroupMember -GroupId $g.ID
                foreach ($member in $members) {
                    $user = Get-MgUser -UserId $member.Id -Property ID, DisplayName, UserPrincipalName, companyName | 
                        Select-Object ID, DisplayName, UserPrincipalName, companyName       # What's the purpose of using Select-Object here at all?
                                                                                            # Also, you're not selecting the property you need below! 
                                                                                            # $user has everything you need
                                                                                            # and you're creating a PSCustomObject with the properties 
                                                                                            # you want below!
                    $licenses = $null
                    $licenses = (Get-MgUserLicenseDetail -UserId $User.id).SkuPartNumber 
                    foreach ($license in $licenses) {
                        Write-Host "Licenses found for $($User.DisplayName): $license"
                        $users += [PSCustomObject]@{
                            ID                   = $user.ID
                            USERPRINCIPALNAME    = $user.Mail           # 'Mail' isn't a property you've selected. Get this from *Get-MgUser* cmdlet        
                            Name                 = $user.DisplayName
                            Group                = $g.DisplayName       # previously used "$groupObj" instead of "$g"
                            Licenses             = $license
                            Company              = $user.CompanyName
                            
                        }   
                    }   
                }
        }
    }
    $users | Format-Table
    $users | Export-Csv "C:\_Scripts\Business-Premium.csv" -NoTypeInformation
    
    

    You really need to learn how to troubleshoot/debug problems.

    0 comments No comments