#GetActiveDirectoryGroups
Import-Module ActiveDirectory
#Get-ADGroup -Filter {samaccountname -eq "Citrix_Test_Users" -ResultSetSize 1  -Properties * | SELECT SamAccountName,Name,Description,DistinguishedName,CanonicalName,GroupCategory,GroupScope,whenCreated
#Get-ADGroupMember -Identity "Citrix_Test_Users" | SELECT -property SamAccountName
#Get-ADGroupMember -Filter {samaccountname -eq "Citrix_Test_Users" -and objectClass -eq "user"}  | SELECT -property SamAccountName
#Get-ADGroupMember -Filter {samaccountname -eq "Citrix_Test_Users"}  | SELECT -property SamAccountName
#GetActiveDirectoryUsers
###################################################################################################
# Powershell to grab all active directory groups incrementally
#inserting or updating them into a SQL table.
###################################################################################################
function Get-String ($obj){
    if ([string]::IsNullOrEmpty($obj))
    {
        return ""
    }
    else
    {
        return $obj.ToString();
    }
}

###################################################################################################
#SQL Connection Setup
###################################################################################################
$SQLServer = "localhost";
$SQLDBName = "DBA_Utilities";
$SqlQuery = @"
UPDATE [DBA_Utilities].[dbo].[GetActiveDirectoryGroups]
SET 
[DisplayName]         = @DisplayName,
[Description]         = @Description,
[DistinguishedName]   = @DistinguishedName,
[CanonicalName]       = @CanonicalName,
[GroupCategory]       = @GroupCategory,
[GroupScope]          = @GroupScope,
[CreatedDate]         = @CreatedDate,
[DWUpdatedDate]       = getdate()
WHERE sAMAccountName = @sAMAccountName
IF @@ROWCOUNT = 0
BEGIN
  INSERT INTO [DBA_Utilities].[dbo].[GetActiveDirectoryGroups] 
          ([sAMAccountName],[DisplayName],[Description],[DistinguishedName],[CanonicalName],[GroupCategory],[GroupScope],[CreatedDate])
    SELECT @sAMAccountName ,@DisplayName ,@Description ,@DistinguishedName ,@CanonicalName ,@GroupCategory ,@GroupScope ,@CreatedDate 

END
"@
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True";
$SqlConnection.Open();

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.Connection = $SqlConnection;
$SqlCmd.CommandType = [System.Data.CommandType]::Text ;

##$SqlCmd.CommandText = "SELECT COUNT(*) FROM [DBA_Utilities].[dbo].[ActiveDirectoryGroups]";
##$RecordCount = $SqlCmd.ExecuteScalar();

$SqlCmd.CommandText = $SqlQuery;
    
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@sAMAccountName",      [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DisplayName",         [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Description",         [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DistinguishedName",   [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CanonicalName",       [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@GroupCategory",       [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@GroupScope",          [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CreatedDate",         [Data.SQLDBType]::VarChar, 128))) | Out-Null

###################################################################################################
#Group Members Setup
###################################################################################################
[string]$MembersQuery = @"
  DECLARE @GCanonicalName  varchar(128),
          @GSamAccountName varchar(128),
          @SamAccountName  varchar(128),
          @ObjectClass     varchar(128);
          
  SELECT  @GCanonicalName  = 'param1',
          @GSamAccountName = 'param2',
          @SamAccountName  = 'param3',
          @ObjectClass     = 'param4';
  
  UPDATE [DBA_Utilities].[dbo].[GetActiveDirectoryGroupMembers]
  SET DWUpdatedDate = getdate()
  WHERE [GroupCanonicalName]  = @GCanonicalName
    AND [GroupSamAccountName] = @GSamAccountName 
    AND [SamAccountName]      = @SamAccountName
    AND [ObjectClass]         = @ObjectClass
    
  IF @@ROWCOUNT = 0
    BEGIN                        
      INSERT INTO [DBA_Utilities].[dbo].[GetActiveDirectoryGroupMembers] 
              ([GroupCanonicalName],[GroupSamAccountName],[SamAccountName],[ObjectClass])
        SELECT @GCanonicalName,@GSamAccountName ,@SamAccountName,@ObjectClass  
          WHERE NOT EXISTS(SELECT * 
                           FROM [DBA_Utilities].[dbo].[GetActiveDirectoryGroupMembers] 
                           WHERE [GroupSamAccountName] = @GSamAccountName 
                             AND [SamAccountName]      = @SamAccountName
                             AND [ObjectClass]         = @ObjectClass)
    END --IF                         
"@

$MembersCmd = New-Object System.Data.SqlClient.SqlCommand;
$MembersCmd.Connection = $SqlConnection;
$MembersCmd.CommandType = [System.Data.CommandType]::Text ;


###################################################################################################
#Get the Results From AD
###################################################################################################
#$Results = Get-ADGroup -Filter * -Server "BigBang.com" -ResultSetSize 10  -Properties * | SELECT SamAccountName,Name,Description,DistinguishedName,CanonicalName,GroupCategory,GroupScope,whenCreated
$Results = Get-ADGroup -Filter *  -Properties * | select -property SamAccountName,Name,Description,DistinguishedName,CanonicalName,GroupCategory,GroupScope,whenCreated
 
   foreach ($item in $Results) {
    $SqlCmd.Parameters[0].Value  = Get-String($item.SamAccountName)
    $SqlCmd.Parameters[1].Value  = Get-String($item.Name)
    $SqlCmd.Parameters[2].Value  = Get-String($item.Description)
    $SqlCmd.Parameters[3].Value  = Get-String($item.DistinguishedName)
    $SqlCmd.Parameters[4].Value  = Get-String($item.CanonicalName)
    $SqlCmd.Parameters[5].Value  = Get-String($item.GroupCategory)
    $SqlCmd.Parameters[6].Value  = Get-String($item.GroupScope)
    $SqlCmd.Parameters[7].Value  = Get-String($item.whenCreated)
	$SqlCmd.ExecuteNonQuery() | Out-Null;;
 
    #now the details! members info!
    #$MemberResults = Get-ADGroupMember -Filter {Samaccountname -eq $item.SamAccountName -and objectClass -eq "user"} | SELECT -property SamAccountName
    [string]$can = $item.CanonicalName.ToString()
    [string]$grp = $item.SamAccountName.ToString()
    #$MemberResults = Get-ADGroupMember -Server "BigBang.com" -Identity $grp | SELECT -property SamAccountName,objectClass
    $MemberResults = Get-ADGroupMember  -Identity $grp | SELECT -property SamAccountName,objectClass
    
     #write-host $item.SamAccountName
     foreach ($MemberItem in $MemberResults) {

        [string]$s = Get-String($MemberItem.SamAccountName)
        [string]$o = Get-String($MemberItem.objectClass);
         #write-host $grp '|' $s '|' $o
         
         [string] $sq = $MembersQuery;
         $sq = $sq -Replace "param1",$can.Replace("'","''")
         $sq = $sq -Replace "param2",$grp.Replace("'","''")
         $sq = $sq -Replace "param3",$s.Replace("'","''")
         $sq = $sq -Replace "param4",$o.Replace("'","''")
         #write-host $sq
        $MembersCmd.CommandText = $sq; 
        $MembersCmd.ExecuteNonQuery() | Out-Null;

        }
    }
        if ($SqlConnection.State -eq "Open") {$SqlConnection.Close()}
 