CODEWITHSUNDEEP
powershellactive-directory
Rob
Rob

Reputation: 221

Get-ADGroup and Get-ADGroupMember in table format for SQL

I'm looking for assistance in getting writing a query that will pull the members for each one of the groups in the query below.

Get-ADGroup -Filter 'GroupCategory -eq "Security"' -Properties ManagedBy |
  Where-Object {$_.ManagedBy -gt 0 -or $_.Notes -like "*Approval*"} |
  Select-Object Name

I would like the table to look something like this (Sorry about the bad table formatting):

GroupName | GroupMember
Sec_Domain Admins | Username 1
Sec_Domain Admins | Username 2
Sec_EnterpriseAdmins | Username 1
Sec_Enterprise Admins | Username 3

I'm having a hard time joining Get-ADGroup and Get-ADGroupMember together to output a table with the group and group members in a table format for uploading into SQL.

Upvotes: 0

Views: 1545

Answers (1)

Bill_Stewart
Bill_Stewart

Reputation: 24575

First, I would use an LDAP filter to express your desired query more efficiently:

(&(groupType:1.2.840.113556.1.4.803:=2147483648)(managedBy=*)(notes=*approval*))

This means "security groups where the managedBy attribute is populated and the notes attribute contains the string 'approval'". This reduces the number of results returned from the server and reduces the need to filter results using Where-Object.

Second, you can output a separate object for each string in the managedBy attribute, and output everything to CSV using the | character as a delimiter. Here is a full example:

Get-ADGroup -LDAPFilter "(&(groupType:1.2.840.113556.1.4.803:=2147483648)(managedBy=*)(notes=*approval*))" -Properties managedBy | ForEach-Object {
  $group = $_
  $managedBy = $_.managedBy
  foreach ( $dn in $managedBy ) {
    New-Object PSObject -Property @{
      "name" = $group.Name
      "managedBy" = $dn
    } | Select-Object name,managedBy
  }
} | Export-Csv "Demo.csv" -Delimiter "|" -NoTypeInformation

The Select-Object after the New-Object is only there to reorder the properties for the CSV output and is not strictly required.

Upvotes: 3

Related Questions