locked
Get-ADGroup RRS feed

  • Question

  • HI

    I Have a Issue with the following Script. I does not constantly give the Extended properties it Is blank is one instance but work on another PC. 

    I am not sure why it is not getting the Properties?

    ###########

    # Script

    Clear-Host
    $ADServer ="Server1"
    $GetGroup = $true;

    if($GetGroup -eq $true) 
    {
     $groups = Get-ADGroup -Server $ADServer -Properties  *  -filter {GroupCategory -eq "Security" -and GroupScope -eq "Global"}  
      
    foreach ($group in $groups)
     { $count++
     
     $Group = $group.Name
          $Name = $group.Name
          $ManagedBy = $group.ManagedBy
          $whenChanged = $group.whenChanged
          $whenCreated = $group.whenCreated
          $uSNChanged = $group.uSNChanged
          $GroupCategory = $group.GroupCategory
          $GroupScope = $group.GroupScope
          $Description = $group.Description

                   Write-host 'Group = '   $Group
                   Write-host ' ManagedBy = ' $ManagedBy
                   Write-host ' whenChanged = ' $whenChanged
                   Write-host ' whenCreated = ' $whenCreated
                   Write-host ' uSNChanged = ' $uSNChanged
                   Write-host ' GroupCategory = ' $GroupCategory
                   Write-host ' GroupScope = ' $GroupScope
                   Write-host ' Description = ' $Description  

    $Members = Get-ADGroupMember  $group -Server $ADServer
    foreach ($Member in $Members)
     { $count++

               
    $LogonName = $Member.SamAccountName
                    #.LogonName
    $Description= $Member

                    Write-host ' LogonName = '       $LogonName
                    Write-host ' Description = '     $Description  
                 
    }  
            
     }

    }

    ###################

    #Output

    Group =  MIS_Pivot
     ManagedBy =  
     whenChanged =  
     whenCreated =  
     uSNChanged =  
     GroupCategory =  
     GroupScope =  
     Description =  
     LogonName =  Pietp
     Description =  CN=Piet Pompies,OU=IT Admin,OU=Staff,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za
     LogonName =  Koosb
     Description =  CN=Koos Boshoff,OU=IT Admin,OU=Staff,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za

    Wednesday, April 16, 2014 10:22 AM

Answers

  • Try it this way.  It will validate your input better and it is easier to test and debug.

    $dbconn.Open()
    $dbwrite = $dbconn.CreateCommand()
    
    $groups = Get-ADGroup -Properties *  -filter {GroupCategory -eq "Security" -and GroupScope -eq "Global"} 
    foreach ($g in $groups){
    
        $members = Get-ADGroupMember  $g
    
        foreach ($m in $members){
    
    $dbwrite.CommandText=@"
        INSERT INTO SRV.Groups([Group],[ManagedBy],[whenCreated],[whenChanged],[uSNChanged],[GroupCategory],[GroupScope],[GroupDescription],[LogonName],[Description],[Datetime])
             VALUES ('$($g.Name)','$($g.ManagedBy)','$($g.whenCreated)','$($g.whenChanged)','$($g.$uSNChanged)','$($g.GroupCategory)','$($g.GroupScope)','$($g.$GroupDescription)','$($m.SamAccountName)','$($m.Description)','$time2')
    "@
            Write-Host $dbwrite.CommandText -ForegroundColor green
            Try{
                $dbwrite.ExecuteNonQuery()
            }
            Catch{
                Write-Host $_ -ForegroundColor Black -BackgroundColor white
            }
        }
    }
    
    
    $dbconn.Close() 
     


    ¯\_(ツ)_/¯

    Wednesday, April 16, 2014 3:12 PM

All replies

  • Get-ADGroup will only return extended properties that aren't blank.

    What happens if you run :-

    Get-ADGroup "MIS_Pivot" -properties *

    Does it match what the script returns?

    Why does the start of your script contain...

    $GetGroup = $true;
    
    if($GetGroup -eq $true) 

    Why the IF statement?  It's always going to be true!


    Wednesday, April 16, 2014 10:36 AM
  • Output of "Get-ADGroup "MIS_Pivot" -properties *" is working. But in contecs on my script dont work

    CanonicalName                   : assmangchrome.co.za/MachadoWorks/Groups/MIS_Pivot
    CN                              : MIS_Pivot
    Created                         : 04/10/2007 15:36
    createTimeStamp                 : 04/10/2007 15:36
    Deleted                         : 
    Description                     : 
    DisplayName                     : 
    DistinguishedName               : CN=MIS_Pivot,OU=Groups,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za
    dSCorePropagationData           : {08/01/2014 13:19, 13/09/2011 15:46, 18/02/2011 16:28, 18/02/2011 12:11...}
    GroupCategory                   : Security
    GroupScope                      : Global
    groupType                       : -2147483646
    HomePage                        : 
    instanceType                    : 4
    isDeleted                       : 
    LastKnownParent                 : 
    ManagedBy                       : 
    member                          : {CN=C-Band,OU=Groups,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za, 
                                      CN=SBU_Managers,OU=Groups,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za, CN=Renet Smit,OU=Raw 
                                      Materials,OU=Staff,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za, CN=Meshack Simelane,OU=Raw 
                                      Materials,OU=Staff,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za...}
    MemberOf                        : {}
    Members                         : {CN=C-Band,OU=Groups,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za, 
                                      CN=SBU_Managers,OU=Groups,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za, CN=Renet Smit,OU=Raw 
                                      Materials,OU=Staff,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za, CN=Meshack Simelane,OU=Raw 
                                      Materials,OU=Staff,OU=MachadoWorks,DC=assmangchrome,DC=co,DC=za...}
    Modified                        : 03/09/2013 13:04
    modifyTimeStamp                 : 03/09/2013 13:04
    Name                            : MIS_Pivot
    nTSecurityDescriptor            : System.DirectoryServices.ActiveDirectorySecurity
    ObjectCategory                  : CN=Group,CN=Schema,CN=Configuration,DC=assmangchrome,DC=co,DC=za
    ObjectClass                     : group
    ObjectGUID                      : 2daffd97-4eef-4c30-831b-cb8e04dc93fc
    objectSid                       : S-1-5-21-2404105980-2389996589-4221434500-1840
    ProtectedFromAccidentalDeletion : False
    SamAccountName                  : MIS_Pivot
    sAMAccountType                  : 268435456
    sDRightsEffective               : 15
    SID                             : S-1-5-21-2404105980-2389996589-4221434500-1840
    SIDHistory                      : {}
    uSNChanged                      : 161638448
    uSNCreated                      : 16593
    whenChanged                     : 03/09/2013 13:04
    whenCreated                     : 04/10/2007 15:36

    #############

    $GetGroup = $true; if($GetGroup -eq $true)

    This is only a subset of a bigger Script.


    Wednesday, April 16, 2014 11:21 AM
  • You have failed to explain what "not working" means. As noted, all on objects will not have the extended property. It will only be present on objects that have had a value assigned. If the object does not exists it can cause an error.

    Your code is not any help in understanding your problem.

    You are reassigning values the outputting them using Write-Host.  This is not how we would do this in PowerShell and is hard to manage.


    ¯\_(ツ)_/¯

    Wednesday, April 16, 2014 12:40 PM
  • The following will give you consistent output. Try it until you understand how it works:

    Get-ADGroup -Server $ADServer -Properties  *  -filter {GroupCategory -eq "Security" -and GroupScope -eq "Global"} |
           Select-Object Name,ManagedBy,whenChanged,whenCreated,uSNChanged,GroupCategory,GroupScope,Description |
           Format-List
    
    You should not specify a server for this. It can create issues.  Windows will find the best AD server available.


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, April 16, 2014 12:52 PM
    Wednesday, April 16, 2014 12:50 PM
  • HI

    This works I just need every one of this columns in a Variable so i can Write it to a SQL Database.

    Name,ManagedBy,whenChanged,whenCreated,uSNChanged,GroupCategory,GroupScope,Description

    I am sorry if i am not clear in what i whant to do. I need all grooups with the following properties "Name,ManagedBy,whenChanged,whenCreated,uSNChanged,GroupCategory,GroupScope,Description" and all the group memebers.

    I then take this information an log it a SQL database where i can use SQL Reports to for Auditing 

    Regards

    Wednesday, April 16, 2014 1:40 PM
  • HI

    This works I just need every one of this columns in a Variable so i can Write it to a SQL Database.

    Name,ManagedBy,whenChanged,whenCreated,uSNChanged,GroupCategory,GroupScope,Description

    I am sorry if i am not clear in what i whant to do. I need all grooups with the following properties "Name,ManagedBy,whenChanged,whenCreated,uSNChanged,GroupCategory,GroupScope,Description" and all the group memebers.

    I then take this information an log it a SQL database where i can use SQL Reports to for Auditing 

    Regards

    The values are already in variables.  You are taking them out and putting them into text.

    How are you putting them into the database.  Are you trying to create a text file?  a CSV? What is you mechanism? TO get a CSV is easy:

    Get-ADGroup -Properties  *  -filter {GroupCategory -eq "Security" -and GroupScope -eq "Global"} |
           Select-Object Name,ManagedBy,whenChanged,whenCreated,uSNChanged,GroupCategory,GroupScope,Description |
           Export-Csv data.csv 
    


    ¯\_(ツ)_/¯

    Wednesday, April 16, 2014 2:11 PM
  • HI

    This is what i am trying to do 

    if($GetGroup -eq $true) 
    {
    #$groups = Get-ADGroup -Server $ADServer -Filter *
        $groups = Get-ADGroup -Server $ADServer -Properties *  -filter {GroupCategory -eq "Security" -and GroupScope -eq "Global"} 
        

        $dbconn.Open()
    foreach ($group in $groups)
     { $count++
     
     $Group = $group.Name
          $Name = $group.Name
          $ManagedBy = $group.ManagedBy
          $whenChanged = $group.whenChanged
          $whenCreated = $group.whenCreated
          $uSNChanged = $group.uSNChanged
          $GroupCategory = $group.GroupCategory
          $GroupScope = $group.GroupScope
          $Description = $group.Description

                   Write-host 'Group = '   $Group
                   Write-host '  LogonName = ' $LogonName
                   Write-host ' ManagedBy = ' $ManagedBy
                   Write-host ' whenChanged = ' $whenChanged
                   Write-host ' whenCreated = ' $whenCreated
                   Write-host ' uSNChanged = ' $uSNChanged
                   Write-host ' GroupCategory = ' $GroupCategory
                   Write-host ' GroupScope = ' $GroupScope
                   Write-host ' Description = ' $Description  

    $Members = Get-ADGroupMember  $group -Server $ADServer
    foreach ($Member in $Members)
     { $count++

    $LogonName = $Member.SamAccountName
                    #.LogonName
    $Description= $Member

                    $dbwrite = $dbconn.CreateCommand()
                    
                    $dbwrite.CommandText = "INSERT INTO SRV.Groups ([Group],[ManagedBy],[whenCreated],[whenChanged],[uSNChanged],[GroupCategory],[GroupScope],[GroupDescription],[LogonName],[Description],[Datetime])
                                VALUES ('$Group','$ManagedBy','$whenCreated','$whenChanged','$uSNChanged','$GroupCategory','$GroupScope','$GroupDescription','$LogonName','$Description','$time2')"

                    $dbwrite.ExecuteNonQuery()

                          
                    
                 
    }  
            
     }
    $dbconn.Close() 
    }

    Wednesday, April 16, 2014 2:50 PM
  • This will work better:

    $dbwrite.CommandText=@"
         INSERT INTO SRV.Groups([Group],[ManagedBy],[whenCreated],[whenChanged],[uSNChanged],[GroupCategory],[GroupScope],[GroupDescription],[LogonName],[Description],[Datetime])
                 VALUES ('$Group','$ManagedBy','$whenCreated','$whenChanged','$uSNChanged','$GroupCategory','$GroupScope','$GroupDescription','$LogonName','$Description','$time2')
    "@

    Note that it cannot be indented because of the "@.


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, April 16, 2014 2:55 PM
    Wednesday, April 16, 2014 2:55 PM
  • What are you going to do with all of the null values that will be returned.  Can all fields accept nulls?  If so you need to alter the insert to handle missing values.

    If you use a parameterized SQL command this will work much better.  YOu could also create dummy values for the null fields.  Paramaterized queries allow for doing this in the parameter.


    ¯\_(ツ)_/¯

    Wednesday, April 16, 2014 2:58 PM
  • Try it this way.  It will validate your input better and it is easier to test and debug.

    $dbconn.Open()
    $dbwrite = $dbconn.CreateCommand()
    
    $groups = Get-ADGroup -Properties *  -filter {GroupCategory -eq "Security" -and GroupScope -eq "Global"} 
    foreach ($g in $groups){
    
        $members = Get-ADGroupMember  $g
    
        foreach ($m in $members){
    
    $dbwrite.CommandText=@"
        INSERT INTO SRV.Groups([Group],[ManagedBy],[whenCreated],[whenChanged],[uSNChanged],[GroupCategory],[GroupScope],[GroupDescription],[LogonName],[Description],[Datetime])
             VALUES ('$($g.Name)','$($g.ManagedBy)','$($g.whenCreated)','$($g.whenChanged)','$($g.$uSNChanged)','$($g.GroupCategory)','$($g.GroupScope)','$($g.$GroupDescription)','$($m.SamAccountName)','$($m.Description)','$time2')
    "@
            Write-Host $dbwrite.CommandText -ForegroundColor green
            Try{
                $dbwrite.ExecuteNonQuery()
            }
            Catch{
                Write-Host $_ -ForegroundColor Black -BackgroundColor white
            }
        }
    }
    
    
    $dbconn.Close() 
     


    ¯\_(ツ)_/¯

    Wednesday, April 16, 2014 3:12 PM
  • I would prefer:

    $dbwrite =$dbconn.CreateCommand()
    $p1=$dbwrite.CreateParameter()

    We can then remove the SQL from the loop and have a very fast and clean method of controlling null values.  If the value is null just skip assigning it.

    if($g.Descritpion){$p1.Value = $g.Description}

    I am pretty sure we can just assign the nulls and they will work without testing assuming the parameter either allows nulls or has had a  default set.

    This will eliminate the issues with the missing values.




    ¯\_(ツ)_/¯

    Wednesday, April 16, 2014 3:18 PM
  • Here is a better example:

    $cstr='Integrated Security=SSPI;Initial Catalog=DBTest;Data Source=.\sqlexpress'
    $conn= new-object System.Data.SqlClient.SqlConnection($cstr)
    $cmd=$conn.CreateCommand() 
    $cmd.CommandType = 4
    $cmd.CommandText = "......."
    $cmd.Parameters.Add('@FileID',3,1)
    $cmd.Parameters.Add('@body',1,1,-1)

    Here is a discussion on the use of parameters: http://social.technet.microsoft.com/Forums/scriptcenter/en-US/6e3ba193-b92c-489f-aba3-e8e32648ea3b/i-need-help-with-powershell-and-adodb-commands-with-parameters?forum=ITCG


    ¯\_(ツ)_/¯

    Wednesday, April 16, 2014 3:22 PM
  • This is what it will  look like once you set up the parameters.  Your error will be very targeted and will not cascade.

    $dbconn.Open()
    $dbwrite = $dbconn.CreateCommand()
    $dbwrite.CommandText=@"
        INSERT INTO SRV.Groups([Group],[ManagedBy],[whenCreated],[whenChanged],[uSNChanged],[GroupCategory],[GroupScope],[GroupDescription],[LogonName],[Description],[Datetime])
             VALUES (@group,@ManagedBy,@whenCreated,@whenChanged,@uSNChanged,@GroupCategory,@GroupScope,@GroupDescription,@SamAccountName,@Description,@time2)
    "@
    # create parmaters here
    
    $groups = Get-ADGroup -Properties *  -filter {GroupCategory -eq "Security" -and GroupScope -eq "Global"} 
    foreach ($g in $groups){
    
        $members = Get-ADGroupMember  $g
    
        foreach ($m in $members){
            Try{
                $dbwrite.Paramaters['@Group'].Value=$g.Name
                ....
                $dbwrite.ExecuteNonQuery()
            }
            Catch{
                Write-Host $_ -ForegroundColor Black -BackgroundColor white
            }
        }
    }
    
    
    $dbconn.Close() 
    
    Once you master this technique then you can push things into a database really easily.  You should also consider using stored procedures for security and flexibility.  It makes this even easier.


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, April 16, 2014 3:30 PM
    Wednesday, April 16, 2014 3:29 PM
  • jrv 

    Thanks for you help and assistance in this regards. It is always nice when there is people uot there willing to help.

    Regards

    Thursday, April 17, 2014 6:53 AM
  • jrv 

    Thanks for you help and assistance in this regards. It is always nice when there is people uot there willing to help.

    Regards

    You are welcome.  The issues with SQL are that few Admins use SQL because it is a complex system.  It is worth learning about if you need to do anything complex.  Running raw text inserts is usually not very reliable or fast.


    ¯\_(ツ)_/¯

    Thursday, April 17, 2014 12:25 PM