none
Adding groupType to exported .csv - PowerShell RRS feed

  • Question

  • Hello -

    I am a very new user (have only known about PowerShell for a couple of weeks) and have cobbled together the below script to pull information about all the Groups in AD.  The script works great except I would really like some help to be able to add a column to the export which includes the groupType (I need to differentiate bewteen global/local/universal security and distribution groups).  Please keep in mind I do not have access to the Active Directory Module.

    Thank you

    $Outfile = C:\Grouplist.csv
    
    $objDomain = New-Object System.DirectoryServices.DirectoryEntry
    $objOU = New-Object System.DirectoryServices.DirectoryEntry("LDAP://OU=Groups,DC=AAA,DC=BBB,DC=COM")
    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
    $objSearcher.SearchRoot = $objOU
    $objSearcher.PageSize = 1000
    $objSearcher.SearchScope = "Subtree"
    
    
    $colResults = $objSearcher.FindAll()
    
    $objCollection = $colResults | select -Expand Properties |
       select @{n='Name';e={$_.name}}, @{n='DisplayName';e={$_.displayname}}, @{n='samaccountName';e={$_.samaccountname}}, @{n='email';e={$_.mail}}, @{n='whencreated';e={$_.whencreated}}, @{n='objectcategory';e={$_.objectcategory}}, @{n='description';e={$_.description}}, @{n='info';e={$_.info}}, @{n='memberof';e={$_.memberof -replace '^cn=([^,]+).+$','$1' -join ";"}}
    
    $objCollection | export-csv -Path $Outfile -Notype
    



    Friday, September 26, 2014 5:47 PM

Answers

  • Here's one example of how to enumerate all the groups in the current domain. It uses the same terminology as ADUC for the group type and also has a progress bar.


    $ADS_GROUP_TYPE_GLOBAL_GROUP       = 0x00000002
    $ADS_GROUP_TYPE_DOMAIN_LOCAL_GROUP = 0x00000004
    $ADS_GROUP_TYPE_UNIVERSAL_GROUP    = 0x00000008
    $ADS_GROUP_TYPE_SECURITY_ENABLED   = 0x80000000
    $ScriptName = $MyInvocation.MyCommand.Name
    
    function Get-GroupType {
      param(
        [Int] $type
      )
      if ( ($type -band $ADS_GROUP_TYPE_SECURITY_ENABLED) -ne 0 ) {
        $result = "Security Group - "
      }
      else {
        $result = "Distribution Group - "
      }
      if ( ($type -band $ADS_GROUP_TYPE_GLOBAL_GROUP) -ne 0 ) {
        $result += "Global"
      }
      if ( ($type -band $ADS_GROUP_TYPE_DOMAIN_LOCAL_GROUP) -ne 0 ) {
        $result += "Domain Local"
      }
      if ( ($type -band $ADS_GROUP_TYPE_UNIVERSAL_GROUP) -ne 0 ) {
        $result += "Universal"
      }
      $result
    }
    
    write-progress $ScriptName "Enumerating groups"
    $searcher = [ADSISearcher] "(objectClass=group)"
    $searcher.SearchRoot = [ADSI] ""
    $searcher.PageSize = 500
    $searcher.PropertiesToLoad.AddRange(@("name","grouptype","distinguishedname"))
    $searchResults = $searcher.FindAll()
    $groupCounter = 0
    $groupCount = ($searchResults | measure-object).Count
    foreach ( $searchResult in $searchResults ) {
      $distinguishedName = $searchResult.Properties["distinguishedname"][0]
      new-object PSObject -property @{
        "distinguishedName" = $distinguishedName
        "name" = $searchResult.Properties["name"][0]
        "Type" = Get-GroupType $searchResult.Properties["grouptype"][0]
      } | select-object distinguishedName,name,Type
      $groupCounter++
      $groupPercent = ($groupCounter / $groupCount) * 100 -as [Int]
      $params = @{
        "Activity" = $ScriptName
        "Completed" = $groupPercent -eq 100
        "CurrentOperation" = $distinguishedName
        "PercentComplete" = $groupPercent
        "Status" = "{0}/{1} [{2:P2}]" -f
          $groupCounter,
          $groupCount,
          ($groupCounter / $groupCount)
      }
      write-progress @params
    }
    $searchResults.Dispose()
    

    The Get-GroupType function uses the usual technique for decoding a bit array (test whether bits are set using the logical and operator).


    -- Bill Stewart [Bill_Stewart]



    Monday, September 29, 2014 9:15 PM
    Moderator

All replies

  • Adding the groupType attribute to your code should be easy, so I assume the problem is that you want to convert the numeric value into the corresponding string. If so, you can use a function similar to below:

    Function GetType($Type)
    {
        Switch ($Type)
        {
            -2147483643 {Return "Built-in/Security"}
            -2147483646 {Return "Global/Security"}
            -2147483644 {Return "Local/Security"}
            -2147483640 {Return "Universal/Security"}
            1 {Return "Built-in/Distribution"}
            2 {Return "Global/Distribution"}
            4 {Return "Local/Distribution"}
            8 {Return "Universal/Distribution"}
        }
    }

    -----



    Richard Mueller - MVP Directory Services


    Friday, September 26, 2014 7:36 PM
    Moderator
  • Just add GroupType to the select.  YOU need to decode it as it is an integer flag field.

    http://msdn.microsoft.com/en-us/library/cc223142.aspx


    ¯\_(ツ)_/¯

    Friday, September 26, 2014 7:42 PM
  • Thank you for the response.

    I have tried adding @{n='GroupType';e={$_.groupType}} to the select but it returns blank fields so there isn't any numeric value to decode.

    Friday, September 26, 2014 8:50 PM
  • Works fine for me.  Try it this way:

    $colResults | select -Expand Properties |Select @{N='GroupType';E={$_.grouptype}} | ft -auto


    ¯\_(ツ)_/¯

    Friday, September 26, 2014 9:00 PM
  •  I removed the $colCollection and now it works.

    I am unsure how to use the GetType function above for only one select item in order to convert the values.  Right now I have just added a formula to my exported data in excel to decode. 

    Thank you!

    Monday, September 29, 2014 5:26 PM
  • I also am unable to retrieve groupType with the code above. I tried a few other integer attributes, like USNCreated and instanceType, with the same blank result. All string attributes are fine. I am using PowerShell V2. Maybe this is a bug fixed in later versions.

    Richard Mueller - MVP Directory Services

    Monday, September 29, 2014 8:51 PM
    Moderator
  • Here's one example of how to enumerate all the groups in the current domain. It uses the same terminology as ADUC for the group type and also has a progress bar.


    $ADS_GROUP_TYPE_GLOBAL_GROUP       = 0x00000002
    $ADS_GROUP_TYPE_DOMAIN_LOCAL_GROUP = 0x00000004
    $ADS_GROUP_TYPE_UNIVERSAL_GROUP    = 0x00000008
    $ADS_GROUP_TYPE_SECURITY_ENABLED   = 0x80000000
    $ScriptName = $MyInvocation.MyCommand.Name
    
    function Get-GroupType {
      param(
        [Int] $type
      )
      if ( ($type -band $ADS_GROUP_TYPE_SECURITY_ENABLED) -ne 0 ) {
        $result = "Security Group - "
      }
      else {
        $result = "Distribution Group - "
      }
      if ( ($type -band $ADS_GROUP_TYPE_GLOBAL_GROUP) -ne 0 ) {
        $result += "Global"
      }
      if ( ($type -band $ADS_GROUP_TYPE_DOMAIN_LOCAL_GROUP) -ne 0 ) {
        $result += "Domain Local"
      }
      if ( ($type -band $ADS_GROUP_TYPE_UNIVERSAL_GROUP) -ne 0 ) {
        $result += "Universal"
      }
      $result
    }
    
    write-progress $ScriptName "Enumerating groups"
    $searcher = [ADSISearcher] "(objectClass=group)"
    $searcher.SearchRoot = [ADSI] ""
    $searcher.PageSize = 500
    $searcher.PropertiesToLoad.AddRange(@("name","grouptype","distinguishedname"))
    $searchResults = $searcher.FindAll()
    $groupCounter = 0
    $groupCount = ($searchResults | measure-object).Count
    foreach ( $searchResult in $searchResults ) {
      $distinguishedName = $searchResult.Properties["distinguishedname"][0]
      new-object PSObject -property @{
        "distinguishedName" = $distinguishedName
        "name" = $searchResult.Properties["name"][0]
        "Type" = Get-GroupType $searchResult.Properties["grouptype"][0]
      } | select-object distinguishedName,name,Type
      $groupCounter++
      $groupPercent = ($groupCounter / $groupCount) * 100 -as [Int]
      $params = @{
        "Activity" = $ScriptName
        "Completed" = $groupPercent -eq 100
        "CurrentOperation" = $distinguishedName
        "PercentComplete" = $groupPercent
        "Status" = "{0}/{1} [{2:P2}]" -f
          $groupCounter,
          $groupCount,
          ($groupCounter / $groupCount)
      }
      write-progress @params
    }
    $searchResults.Dispose()
    

    The Get-GroupType function uses the usual technique for decoding a bit array (test whether bits are set using the logical and operator).


    -- Bill Stewart [Bill_Stewart]



    Monday, September 29, 2014 9:15 PM
    Moderator
  • So I guess you all gave up.  Here is how to do this on all versions of PowerShell.  Can you see the one major change that makes it possible.  Not the use of a hash to decode but the way the data is extracted.

    $GroupType=@{
             -2147483643='Built-in/Security'
             -2147483646='Global/Security'
             -2147483644='Local/Security'
             -2147483640='Universal/Security'
             1='Built-in/Distribution'
             2='Global/Distribution'
             4='Local/Distribution'
             8='Universal/Distribution'
         }
    
    $props=@(
            @{n='Name';e={$_.name}}, 
            @{n='DisplayName';e={$_.displayname}}, 
            @{n='samaccountName';e={$_.samaccountname}}, 
            @{n='email';e={$_.mail}}, @{n='whencreated';e={$_.whencreated}}, 
            @{n='description';e={$_.description}},
            @{n='GroupType';E={$Grouptype[$_.grouptype[0]]}}
    )
    
    $searcher=[adsisearcher]'objectclass=group'
    $searcher.SearchRoot='LDAP://ou=MyBusiness,dc=TESTNET,DC=local'
    $searcher.FindAll() |
        select -Expand Properties |
        select $props



    ¯\_(ツ)_/¯



    • Edited by jrv Monday, September 29, 2014 10:22 PM
    Monday, September 29, 2014 10:20 PM
  • Doing queries this way eliminates many of the "wrapper" issues.

    $GroupType=@{
             -2147483643='Built-in/Security'
             -2147483646='Global/Security'
             -2147483644='Local/Security'
             -2147483640='Universal/Security'
             1='Built-in/Distribution'
             2='Global/Distribution'
             4='Local/Distribution'
             8='Universal/Distribution'
         }
    
    $props=@(
            @{n='Name';e={$_.Properties.name}}, 
            @{n='DisplayName';e={$_.Properties.displayname}}, 
            @{n='samaccountName';e={$_.Properties.samaccountname}}, 
            @{n='email';e={$_.Properties.mail}}, @{n='whencreated';e={$_.Properties.whencreated}}, 
            @{n='description';e={$_.Properties.description}},
            @{n='GroupType';E={$Grouptype[$_.Properties.grouptype]}}
    )
    
    $searcher=[adsisearcher]'objectclass=group'
    $searcher.SearchRoot='LDAP://ou=MyBusiness,dc=TESTNET,DC=local'
    $searcher.FindAll() | select $props
    

    In all cases I recommend avoiding using translated VBScript techniques.  THey are most unnecessary and do not work well with the [adsisearcher] due to the extra wrappers.


    ¯\_(ツ)_/¯

    Monday, September 29, 2014 10:56 PM
  • I don't recommend comparing the groupType attribute numerically. It is a bit array. It is more robust, code-wise, to test whether individual bits are set (i.e., treat the value as bit array, not a number). See the Get-GroupType function in my sample script.


    -- Bill Stewart [Bill_Stewart]

    Tuesday, September 30, 2014 2:15 PM
    Moderator