Script to get AD group and members details but group name is to long

[cvaxel]

Hi,

I found this script on the internet and it works but i have a problem when the group name is longer then 31 charaters. Is there away to rename these groups are to shorten them.

This is the script.

$result = Get-ADGroup -Properties Name -SearchBase "DC=Contac,DC=local" -Filter * | ForEach-Object { 
    $group = $_.Name
    Get-ADGroupMember -Identity $group -Recursive | 
    Where-Object {$_.objectClass -eq 'user'} |
    Get-ADUser -Properties Displayname,Name,EmailAddress |
    Select-Object @{Name = 'Group'; Expression = {$group}}, Displayname,Name,EmailAddress
 
$result | Group-Object -Property Group | ForEach-Object {
    $group = $_.Name
    $_.Group | Select-Object * -ExcludeProperty Group | 
    # save as separate csv files
    # Export-Csv -Path "C:\tmp\$group.csv" -NoTypeInformation

    # or as separate worksheets in an Excel document


    Export-Excel -Path 'C:\temp\XXXX-2023.xlsx' -WorkSheetname $group -AutoSize -title "ADGroup: $group" -TitleBold

}
}

[#2]

@cvaxelWhat's the actual problem? Is the generated string too long for an Excel Sheet?

You can check the length like this:

$group.Length

 And if it's longer than 31 character you could shorten it like this:

$group.Substring(0,31)

[#3]

@cvaxel 

Hi, Clinton.

That script might work but there's a lot wrong with it.

The 30 character limit relates explicitly to the Excel worksheet name, which cannot be longer than 30 characters. So, that's the only place you'd possibly be truncating the group name.

The issue with adjusting the group name though is that it may no longer then be unique (particularly likely if your organisation has strong naming conventions), which can then cascade into the undesirable outcome of merging what should be multiple worksheets into one. So, you have some thinking to do there as to how you want to handle that 30 character limitation for the worksheet name.

Anyhow, I'm not looking to specifically solve the 30 character limit or the worksheet merging issues for you. I'm only taking your original example script and cleaning up the structural issues.

Get-ADGroup -Properties Name -Filter * | ForEach-Object { 
    $group = $_.Name;
    
    if ($group.Length -gt 30)
    {
        $WorksheetName = $group.Substring(0, 30);
    }
    else
    {
        $WorksheetName = $group;
    }

    Get-ADGroupMember -Identity $group -Recursive | 
        Where-Object {$_.objectClass -eq 'user'} |
            Get-ADUser -Properties Displayname,Name,EmailAddress |
                Select-Object Displayname,Name,EmailAddress |
                    Export-Excel -Path 'C:\temp\XXXX-2023.xlsx' -WorkSheetname $WorksheetName -AutoSize -title "ADGroup: $group" -TitleBold
}

Cheers,

Lain