Arrays/Hash Tables - Arggh!

This topic contains 13 replies, has 3 voices, and was last updated by  Karson Van Meeteren 2 years, 4 months ago.

  • Author
    Posts
  • #27720

    Karson Van Meeteren
    Participant

    I'm leveraging the Get-QADObject in attempt to gather a CSV with ADGroup,email address as the columns.

    Before that, I think I need to structure the data in a way to make the Export-CSV possible.

    My current script gets the AD Security group displayname as an array, then goes on for each member of that group and gathers the email, also as an array.

    I've got a bad case of scripters-block going on, so any advice on how to proceed from here, whether it be making a hash table, or something else, would be great to get over this hurdle, ultimately getting a nice pretty CSV to email to our development team 🙁

    Thanks!

    Add-PSSnapin Quest.ActiveRoles.ADManagement
    $emails = @()
    $group = @()
    $objects = Get-QADObject "SECURITY GROUP NAME" | Select-Object *
    foreach ($object in $objects)
    {
    	#Formatting CN to just the group name
    	$group += $object.displayname -replace "(CN=)(.*?),.*", '$2'
    
    	foreach ($item in $object.member)
    	{
    		$objectdetails = Get-QADObject $item | select name, memberof
    		if ($objectdetails | Where-Object { $_.name -like "*@*" })
    		{
    			#external users are in AD as a "contact" with their email as their name
    			$emails += Get-QADObject $item | Select-Object -ExpandProperty name
    		}
    		
    		else
    		{
    			$emails += Get-QADObject $item | Select-Object -ExpandProperty email
    		}
    	}
    }
    
    $group
    $emails
    

    Output screenshot:
    http://content.screencast.com/users/kars85/folders/Snagit/media/07892e4a-bb6e-4635-92a4-54b9c09e2c51/07.21.2015-08.31.png

  • #27721

    Don Jones
    Keymaster

    You need to output an object, since that's what Export-CSV can consume.

    VERY broadly:

    $data = @{ column1='value';
               column2='value'}
    $object = New-Object -Type PSObject -Prop $data
    Write-Output $object
    

    Will create an object having properties Column1 and Column2; if piped to Export-CSV, that'll result in a two-column file.

    But the point is to export one object per line of the CSV. Normally, you'd build this in a function. In pseudo-code:

    function Get-GroupInfo {
      # output one object per desired line of CSV
      # objects have properties corresponding to desired columns
    }
    
    Get-GroupInfo | Export-CSV
    

    Does that help at all?

  • #27722

    Karson Van Meeteren
    Participant

    It does. Thank you very much for chiming in to get the gears rolling again. I will implement your suggestions into the script and report back with any additional questions, or the completed script for others to reference if they have the same problem.

  • #27725

    Karson Van Meeteren
    Participant

    I got the hash table working 🙂 The only issue now is generating a new line in the CSV when I foreach's to a new AD security group. As it stands, I am only able to get it on one row.

    Basically the CSV structure I would like to achive is:

    ADGroup,EmailAdress
    Group 1, asdf@msn,com,asdf@reddit.com, etc...
    Group 2, asdfasdf@msn.com, asdfasdf@reddit.com, etc...

    I've tried a variety of things, which I'd be more than willing to try and repost, if helpful

    Add-PSSnapin Quest.ActiveRoles.ADManagement
    $emails = @()
    $group = @()
    $hash = @{ }
    $objects = Get-QADObject "SECURITY GROUP" | Select-Object *
    $psobjectcollection = @()
    foreach ($object in $objects)
    {
    	
    	$group += $object.displayname -replace "(CN=)(.*?),.*", '$2'
    	foreach ($item in $object.member)
    	{
    		$objectdetails = Get-QADObject $item | select name, memberof
    		if ($objectdetails | Where-Object { $_.name -like "*@*" })
    		{
    			#external collaborators are in AD as a "contact" with their email as their name
    			$emails += Get-QADObject $item | Select-Object -ExpandProperty name
    			$hash = [ordered]@{
    			
    			ADGroup			   = "$group `r`n"
    			emailAddress       = (@($emails) -join ',')
    			}
    		}#end if
    		else
    		{
    			$emails += Get-QADObject $item | Select-Object -ExpandProperty email
    			$hash = [ordered]@{
    			ADGroup 		   = "$group`r`n"
    			emailAddress 	   = (@($emails) -join ',')
    			}
    		}#end else
    		$PSObject = New-Object -TypeName System.Management.Automation.PSObject -Property $hash
    		$PSObjectCollection += $PSObject
    	}#end nested foreach
    }#end foreach
    
    
    $PSObject | Export-Csv c:\Users\kvanmeeteren\Desktop\WI.csv -NoTypeInformation
    
  • #27726

    Don Jones
    Keymaster

    So, two things – and these aren't really optional, because you need to work with PowerShell the way it wants to be worked with.

    First, wrap all this in a function. That's important, so you can call the function, it can emit multiple objects to the pipeline, and you can pipe those to Export-CSV. What you're doing now is not going to work.

    Second, you're creating $PSObject in the correct location – just Write-Output it to the pipeline RIGHT THERE.

    Your problem is that you're attempting to accumulate a bunch of stuff ($PSObjectCollection), but that's literally the whole point of PowerShell's pipeline. You've written a VBScript in PowerShell language, and you're fighting the shell. Don't fight it. Fortunately, the fix over to shell-style is fairly easy.

    function Get-ADGroupInfo {
    Add-PSSnapin Quest.ActiveRoles.ADManagement
    $emails = @()
    $group = @()
    $hash = @{ }
    $objects = Get-QADObject "SECURITY GROUP" | Select-Object *
    foreach ($object in $objects)
    {
    	
    	$group += $object.displayname -replace "(CN=)(.*?),.*", '$2'
    	foreach ($item in $object.member)
    	{
    		$objectdetails = Get-QADObject $item | select name, memberof
    		if ($objectdetails | Where-Object { $_.name -like "*@*" })
    		{
    			#external collaborators are in AD as a "contact" with their email as their name
    			$emails += Get-QADObject $item | Select-Object -ExpandProperty name
    			$hash = [ordered]@{
    			
    			ADGroup			   = "$group"
    			emailAddress       = (@($emails) -join ',')
    			}
    		}#end if
    		else
    		{
    			$emails += Get-QADObject $item | Select-Object -ExpandProperty email
    			$hash = @{
    			ADGroup 		   = "$group"
    			emailAddress 	   = (@($emails) -join ',')
    			}
    		}#end else
    		$PSObject = New-Object -TypeName System.Management.Automation.PSObject -Property $hash
    		Write-Output $PSObject
    	}#end nested foreach
    }#end foreach
    }
    
    Get-ADGroupInfo | Export-Csv c:\Users\kvanmeeteren\Desktop\WI.csv -NoTypeInformation
    

    [ordered] is utterly unnecessary, here. Export-CSV will figure it out. And you don't want that carriage return in the CSV data. Again, let Export-CSV do its job.

    This is what "Learn PowerShell Toolmaking in a Month of Lunches" is all about, BTW.

  • #27728

    Karson Van Meeteren
    Participant

    So close, yet so far. Normally I wrap my scripts up in functions when I near completion of it. Today, I learned that's not the right way to do things to allow the shell to really shine, especially for what it is.

    Appreciate your pointers, as always, Don. Especially for the fact that you don't just blast out the corrected script right out of the gate, rather you let folks work towards the right solution themselves!

    I've got 4 of your books on the bookshelf at my desk – one autographed from one of your visits to Central Iowa. Might be time to dust it off!

  • #27729

    Rob Simmers
    Participant

    If you have Quest tools installed, why are you not using Get-QADGroupMember?

  • #27730

    Karson Van Meeteren
    Participant

    @Rob

    I could have, and may end up using it at some point for the -indirect of nested security groups. Or, I might be able to use the -indirectmemberof switch with Get-QADObject, too. I don't know, yet.

    To me, it almost seems like a six of one, half dozen of another cmdlet at first glance? I'm not sure of any obvious enhancements for my purpose that Get-QADGroupMember would give me without digging into it a little.

  • #28138

    Karson Van Meeteren
    Participant

    Back into looking into this. I've found an issue where the emailAddress in the hash table never empties, it keeps building from the previous entries information.

    For example, the CSV starts with:

    email1, group1
    email1, group1
    email2, group1
    email1, group1
    email2, group1
    email3,group1
    etc...

    I've tried everything I can think of to stop this from happening, like clearing $emails out after it loops through via foreach, but it doesn't work.

  • #28139

    Don Jones
    Keymaster

    You'd probably have to share some of the current code – it's a little difficult to debug logic errors when you can't see the logic :).

  • #28143

    Karson Van Meeteren
    Participant

    A little has changed since my OP, but here's where I'm at 🙂

    function Get-LIMSGroupInfo
    {
    	Add-PSSnapin Quest.ActiveRoles.ADManagement
    #	$emails = @()
    	$hash = @{ }
    	$objects = Get-QADObject "sanitized*" | Select-Object name, memberof, email, displayname, member -First 2
    	$psobjectcollection = @()
    	foreach ($object in $objects)
    	{
    		$group = $object.displayname -replace "(CN=)(.*?),.*", '$2'
    		Write-Host "Working on group '$group'" -ForegroundColor Blue
    		
    		foreach ($item in $object.member)
    		{
    			$emails = @()
    			$objectdetails = Get-QADObject $item | select name, memberof, email -Unique
    			if ($objectdetails | Where-Object { $_.name -like "*@*" })
    			{
    				#external collaborators are in AD as a "contact" with their email as their name
    				$emails += Get-QADObject $item | Select-Object -ExpandProperty name 
    				Write-Host "Valid email found! '$emails'" -ForegroundColor Green
    				$hash = @{
    					emailAddress = "$emails"#(@($emails) -join ',')
    					ADGroup = "$group"
    				}
    			}#end if
    			elseif ($objectdetails | Where-Object { $_.email -like "*@*" })
    			{
    				$emails += Get-QADObject $item | Select-Object -ExpandProperty email
    				Write-Host "Valid email found! '$emails'" -ForegroundColor Green
    				$hash = @{
    					emailAddress = "$emails"#(@($emails) -join ',')
    					ADGroup = "$group"
    				}
    			}#end elseif
    			else 
    			{
    				Write-Host "No user email found" -ForegroundColor Yellow
    			}#end else
    			
    		}#end nested foreach
    		$PSObject = New-Object -TypeName System.Management.Automation.PSObject -Property $hash
    		$PSObjectCollection += $PSObject
    		Write-Output $PSObjectCollection
    	}#end foreach
    }#end function
    
    
    $date = ((get-date).toString('MM-dd-yyyy'))
    Get-LIMSGroupInfo | Export-Csv D:\Powershell\temp\Labware\sanitized_Users_$date.csv -NoTypeInformation
    

    The write-host part of my script outputs the correct, non-duplicate info, but the hash/CSV, not so much.

  • #28145

    Don Jones
    Keymaster

    One logic error: In a case where you hit your Else{} construct, you're not populating $hash. That means you're going to output a new object using the previous iteration's $hash. If the goal is to output no object at all, then you need to move the New-Object and Write-Output lines (3 lines of code total) into the If{} and ElseIf{} blocks, or construct some other conditional logic.

    I'd also suggest stepping through the code in ISE, using debugging and F11 (I think) to step one line of code at a time. That way you can "see" the code execute, and check variable contents as you go, to see where you're going wrong.

  • #28146

    Karson Van Meeteren
    Participant

    Hopefully I'm understanding this – not really sure at this point. What I've done to follow your suggestions is add

    		$PSObject = New-Object -TypeName System.Management.Automation.PSObject -Property $hash
    		$PSObjectCollection += $PSObject
    		Write-Output $PSObjectCollection
    

    within both the if & else statement blocks. If this is correct, I'll have to do some reading as to why it doesn't work building the object once at the end in the else statement.

    I've also set the breakpoint in Powershell Studio at the #end function line to see what variable contents I have for $psobjectcollection after the function runs. It is correct, only having 7 lines of unique emails. However, the CSV is redundant like I previously mentioned (screenshot below).

    If the $psobjectcollection defined in the function has correct data, why is piping to Export-CSV acting all goofy duplicating lines instead of just splatting it from the pipeline?


    http://content.screencast.com/users/kars85/folders/Snagit/media/0cd9078f-594b-4ad2-bb39-3c41d5805211/08.03.2015-13.27.png

  • #28148

    Karson Van Meeteren
    Participant

    Just as an FYI, if I get rid of the function wrapper, then just pipe $psobjectcollection to Export-CSV, I get the right output.

    My mind is about numb at this point trying to troubleshoot what wrapping it in a function has to do with the CSV going nuts.

    This works great:

    #function Get-LIMSGroupInfo
    #{
    	Add-PSSnapin Quest.ActiveRoles.ADManagement
    	#	$emails = @()
    	$hash = @{ }
    	$objects = Get-QADObject "sanitized*" | Select-Object name, memberof, email, displayname, member
    	$psobjectcollection = @()
    	foreach ($object in $objects)
    	{
    		$group = $object.displayname -replace "(CN=)(.*?),.*", '$2'
    		Write-Host "Working on group '$group'" -ForegroundColor Blue
    		foreach ($item in $object.member)
    		{
    			$emails = @()
    			$objectdetails = Get-QADObject $item | select name, memberof, email -Unique
    			if ($objectdetails | Where-Object { $_.name -like "*@*" })
    			{
    				#external collaborators are in AD as a "contact" with their email as their name
    				$emails += Get-QADObject $item | Select-Object -ExpandProperty name
    				Write-Host "Valid email found! '$emails'" -ForegroundColor Green
    				$hash = @{
    					emailAddress = "$emails"#(@($emails) -join ',')
    					ADGroup = "$group"
    				}
    			}#end if
    			elseif ($objectdetails | Where-Object { $_.email -like "*@*" })
    			{
    				$emails += Get-QADObject $item | Select-Object -ExpandProperty email
    				Write-Host "Valid email found! '$emails'" -ForegroundColor Green
    				$hash = @{
    					emailAddress = "$emails"#(@($emails) -join ',')
    					ADGroup = "$group"
    				}
    				$PSObject = New-Object -TypeName System.Management.Automation.PSObject -Property $hash
    				$PSObjectCollection += $PSObject
    
    			}#end elseif
    			else
    			{
    				Write-Host "No user email found" -ForegroundColor Yellow
    			}#end else
    		}#end nested foreach
    
    	}#end foreach
    Write-Output $PSObjectCollection
    #}#end function
    
    
    $date = ((get-date).toString('MM-dd-yyyy'))
    $psobjectcollection| Export-Csv d:\Powershell\temp\Labware\sanitized_$date.csv -NoTypeInformation
    

You must be logged in to reply to this topic.