Ms Graph powewrshell to export user licenses and other attributes entry

Question

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

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

Answer 1

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.

Answer 2

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

Answer 3

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.