merging to output tables 2

[kokser]

I am trying to figure out the best way to do this, and the only thing I can think of is merging two tables into one.

the task
Export following information for ALL AD users: Name, DN, TSAllowLogon.
For all users that have a mailbox, I also want to export the mailbox size.
All this information needs to be in a single table.

I am running on a Server and Exchange 2003, installed powershell 2 and QAD.

Script so far:

$date = get-date -uformat %d/%m/%Y
$MB = Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer koks-u549ckztx1 | select @{name="MailboxSize";expression={$_.Size}},@{name="DN";expression={(Get-QADUser $_.LegacyDN).DN}}
$AD = get-qaduser | select name,dn,tsallowlogon,@{name="Dato";expression={$date}}

The idea is to do a loop, where it will match the DN between mailboxes and users, and then gather the infomation in a single table.

I have no idea how this is suppose to work, and I wouldn't be surprised if anyone had a better idea.

I'm not so good at this, so any input is appreciated!

 

[kokser]

After thinking a lot I have come up with this. Its just a theoretical idea, I realize the commands will not work in practice, but maybe someone can help me make it work.

Foreach ($User in $AD) {
	if ($MB.DN -match $AD.dn)
		Write-Host ($AD.name, $AD.DN, $AD.tsallowlogon, $MB.size, $date)
	Next }

 

[mrmovie]

i would determine if looping through your collections has any cost other than the obvious? i have seen with the old WinNT providers looping through collections can have the effect of talking to AD all the time. it worked out alot quicker to convert the collection into a hashtable and .exists against that.

but your example code wouldnt work? you would need to do something like (

ForEach $User in $AD{
For Each $aMailbox In $MB{
if ($MB.DN -match $AD.dn)
Write-Host ($AD.name, $AD.DN, $AD.tsallowlogon, $MB.size, $date)
}

}
which would be expensive indeed.
you would want to loop through the one with the most first and compare to the one with the least...a hashtable would be faster...but i am sure you can combine the two with some sort of ADO recordset (not sure how though) you might find some examples of unbound recordsets in the vbscript forum from dilannette (spelt wrong, sorry pal)

 

[kokser]

A hashtable? Do you mean creating a new object and adding members (variables)?

 

[mrmovie]

if $MB (in the code sample i posted) was really a hashtable then instead of:

For Each $aMailbox In $MB{
if ($MB.DN -match $AD.dn)
Write-Host ($AD.name, $AD.DN, $AD.tsallowlogon, $MB.size, $date)
}


you could have

If $MB.ContainsKey($AD.dn)

i believe the .ContainsKey is more efficient than the ForEach loop.

 

[kokser]

ohh, that is very smart. I will try this and report back.

 

[mrmovie]

im sure the disconnected recordset thing is the way to go.
if you want to look at the hashtable then that is always good for other stuff anyway.

...example

#Something to hold our objects in so we can serialize it?
$dicVMHosts = @{}

Connect-VIServer $strVIServer
$vmhosts = get-vmhost | Select-Object Name, Id, CustomFields, MoRef, Summary
ForEach($vmhost In $vmhosts){
#clear variables, set up the fake class, construct
$clsVMHost = "" | Select VMWareUUID, VMWareName, VMWareNetbiosname, VMWareSCCMID, VMWareMAC, SMSSCCMID
$clsVMHost.VMWareUUID = [string]""
$clsVMHost.VMWareName = [string]""
$clsVMHost.VMWareNetbiosname = [string]""
$clsVMHost.VMWareSCCMID = [string]""
$clsVMHost.VMWareMAC = [string]""
$clsVMHost.SMSSCCMID = [string]""
#
#get-view of vmhost
$vmhostview = get-view $vmhost.Id
$clsVMHost.VMWareUUID = $vmhostview.Summary.Hardware.Uuid
$clsVMHost.VMWareName = $vmhostview.Name
#
#adding to the hashtable / dictionary
$dicVMHosts.Add($clsVMHost.VMWareUUID, $clsVMHost)
}

you then have $dicVMHosts which is a hashtable / dictionary
and you can do the .ContainsKey on the VMWareUUID which is quicker then looping through

 

[kokser]

Thank you for the example, but I don't understand how I can use it in my case.

 

[mrmovie]

overall i doubt you will save much time over the two ForEach loops, but it shows an effort has been made towards efficiency.(in other circumstances it may be of more use)



$dicMailboxes = @{}
For Each $aMailbox In $MB{
$clsMailbox = "" | Select Size, DN
$clsMailbox.Size = $aMailbox.size
$clsMailbox.DN = $aMailbox.DN
$dicMailboxes.Add($clsMailbox.DN, $clsMailbox)
}


ForEach $User in $AD{
If $dicMailboxes.ContainsKey($User.dn){
write-host $User.dn + $dicMailboxes.Item($User.dn).Size
}
}

 

[kokser]

ohhh, yes I see!

Okay I tested it, and got this error:

You cannot call a method on a null-valued expression.
At C:\export.ps1:13 char:31
+     if ($disMailboxes.ContainsKey <<<< ($User.dn)) {
    + CategoryInfo          : InvalidOperation: (ContainsKey:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

The compelte script so far:

$date = get-date -uformat %d/%m/%Y
$AD = get-qaduser | select name,dn,tsallowlogon
$MB = Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer server | select @{name="MailboxSize";expression={$_.Size}},@{name="DN";expression={(Get-QADUser $_.LegacyDN).DN}}

$dicMailboxes = @{}
ForEach ($aMailbox In $MB) {
	$clsMailbox = "" | Select Size,DN
	$clsMailbox.Size = $aMailbox.Size
	$clsMailbox.DN = $aMailbox.DN
	$dicMailboxes.Add($clsMailbox.DN, $clsMailbox)
}
ForEach ($User in $AD) {
	if ($disMailboxes.ContainsKey($User.dn)) {
		write-host $User.dn + $dicMailboxes.Item($User.dn).Size
	}
}

 

[kokser]

oh, I noticed the typo that made the error. However, anything after the + in write-host is not displayed.

 

[kokser]

Figured out the error is caused by the first loop (Pretty sure). Not sure what to do, but I will try some different things.

 

[kokser]

having spent the day revising the script, I have come up with the following that almost works:

$date = get-date -uformat %d/%m/%Y
$AD = get-qaduser | select name,dn,tsallowlogon
$MB = Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer koks-u549ckztx1 | select @{name="Size";expression={$_.Size}},@{name="DN";expression={(Get-QADUser $_.LegacyDN).DN}}

$dicMailboxes = @{}
ForEach ($aMailbox In $MB) {
	$clsMailbox = "" | Select Size,DN
	$clsMailbox.Size = $aMailbox.Size
	$clsMailbox.DN = $aMailbox.DN
	$dicMailboxes.Add($clsMailbox.DN, $clsMailbox.Size)
}
ForEach ($User in $AD) {
	if ($dicMailboxes.ContainsKey($User.dn)) {
		write-host $User.dn + $dicMailboxes.Item($User.dn).Size
	}
}

The last write-host does not give the desired result however. It only writes out the DN. Maybe I need to determine a specific key in the hashtable, i.e. the DN?

 

[mrmovie]

sorry alot of the code i sent you was just 'example'.

the way you are adding to the dictionary is just a key and a string for the value. so you can simply things, i.e. you dont need to the clsMailbox stuff, and you dont need the .Size at the end of the .Item($User.dn)...i think the below might be more like it?

ForEach ($aMailbox In $MB) {
$dicMailboxes.Add($aMailbox.DN, $aMailbox.Size)
}

ForEach ($User in $AD) {
if ($dicMailboxes.ContainsKey($User.dn)) {
write-host $User.dn + $dicMailboxes.Item($User.dn) }
}

 

[kokser]

Ah yes, that works perfect! Thank you for the help.

I will just tweak it a little and post the end result when I'm done.

 

[kokser]

Here we go

$date = get-date -uformat %d/%m/%Y
$AD = get-qaduser | select name,dn,tsallowlogon
$MB = Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer exvbe01 | select @{name="Size";expression={$_.Size}},@{name="DN";expression={(Get-QADUser $_.LegacyDN).DN}}

$dicMailboxes = @{}
ForEach ($aMailbox In $MB) {
	$dicMailboxes.Add($aMailbox.DN, $aMailbox.Size)
}
$output = @()
ForEach ($User in $AD) {
	if ($dicMailboxes.ContainsKey($User.dn)) {
		$output += new-object PSObject -property @{ Name = $user.Name; TS = $user.TsAllowLogon; DN = $User.DN; Size = $aMailbox.Size; Date = $date }
	}
}
$output | export-csv -path $filecsv -encoding ascii -notypeinformation