Combining PowerCLI data with data from other systems

[TheVMinator]

I have some PowerCLI data on VMs that comes in way such as this:

$vmlist = get-vm | select name, memoryGB, numCPU

and so on.

Based on the NAME of these VMs, I have a bunch of other info from other systems I need to aggregate into my report.

For example, I need to take the name of this VM, go over to Active directory, and get a bunch of other information on this computer name such as the OU it is in.  I'd like to add this information as custom properties to each VM object that exists in the $vmlist array created above.

What is the cleanest way to accomplish this?  Can I take the $vmlist array, and use it to query Active directory, and then add a custom property to $vmlist such as OrganizationalUnit?

[jpsider]

yes, you can absolutely do this.

$vmlist = get-vm | select name, memoryGB, numCPU
foreach ($vm in $vmlist) {
     #add your Active Directory query here
     #you can add the information to the object, or create new objects once you are in this loop.

}

[TheVMinator]

Thank you for your reply.  Yes I could do a separate query to AD for every vm object.  The problem there is that for 1000 VMs in my array, the report would take forever. 

What I was envisioning was something like: a way to take the hostnames from vmlist, use them to run one query against active directory and create a second array that has the AD hostnames and associated computer account info.

Then take the $vmlist array, and cycle through it, and add the data from the second array. 

This way I need only one query to vCenter, and one query to AD...

[jpsider]

I guess I might be missing something, I'm not sure how (without some type of loop) you could query against AD and add information to an object.

I suppose you could :

1. Query vCenter and create an Array

2. Query AD and create an Array

3. Match Array items and mush them together in a 3rd Array?

4. Deliver the report from the 3rd array

Chick or Egg?  I guess programatically they are both possible so long as the VM-name and the Hostname in AD match. Or you will need to get the IP from vCenter, query DNS  to get the hostname to compare the values in the 2 array's.

Am I way off?

[TheVMinator]

That's right - the vmname in vCenter and the computer name in AD are going to be the same.

So with the two arrays - you should be able to use that value as a common key and combine the arrays, adding the properties you want from both.  In my case I'd like to add custom properties to the $vmlist array.  Searching as I think LucD might have way to do this posted somewhere

[jpsider]

take a look at this post I found.  It talks about joining/creating a union of the hash tables that were created.

arrays - Union and Intersection in Powershell? - Stack Overflow

After reviewing I don't think its what you are looking for.

You might have better luck writing each query out to .csv, sorting by name and just copying the data in to one .xls/csv. (lazy way?)

I guess it all depends on how you want to use the data.  I've not seen an easy way to merge two things like this easily (without just using one to create the query for the other).

[LucD · Accepted solution]

The Get-AD cmdlets have a filter which rather limited, so you could use PowerShell's more performant -match operator on the returned result.

The script first collects all the VM names and creates a RegEx expresion with the OR ('|') operator between the names.

The script thus combines vSphere and AD in 1 result

$names = (Get-VM | %{$_.Name}) -join '|'

Get-ADComputer -Filter "*" -Properties DistinguishedName |

where{$_.Name -match $names} |

Select Name,@{N='OU';E={$first,$rest = $_.DistinguishedName -split ',';$rest -join ','}}

[TheVMinator]

OK thanks!