Required PowerShell Modules:
- ImportExcel: Douglas Finke has created an amazing module for creating Excel workbooks, even when Excel is not installed on the system you run the script from. This is a must have for your pragmatic PowerShell tool box. I use it all the time. I’ve linked to it’s page on the PowerShell gallery.
Approach:
The CMDB Name field and the AD DNSHostName attribute can be used to match the CMDB data to the AD computer objects. The first step is to add CMDB properties to the existing objects that hold the VM data.
# Start add CMDB properties to the AD data
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_FQDN' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'InCMDB' -Value 'N'
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDBStatus' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_First_Discovered' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_Last_Discovered' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_Model' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_Maker' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_Site' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_sys_class_name' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_cpu_count' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_cpu_core_count' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_RecordCount' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_OS' -Value ''
$adSvrs | Add-Member -MemberType NoteProperty -name 'CMDB_IP_Address' -Value ''
# End add CMDB properties to the AD data
# Start merging CMDB data with the AD data
ForEach($srv in $adSvrs){
ForEach($r in $CMDBServers){
if($($srv.DNSHostName) -eq $($r.Name)) {
$srv.CMDB_FQDN = $r.fqdn
$srv.InCMDB = 'Y'
$srv.CMDBStatus = $r.CMDBStatus
$srv.CMDB_First_Discovered = $r.first_discovered
$srv.CMDB_Last_Discovered = $r.last_discovered
$srv.CMDB_Model = $r.model
$srv.CMDB_Maker = $r.Maker
$srv.CMDB_Site = $r.Site
$srv.CMDB_CPU_Count = $r.cpu_count
$srv.CMDB_CPU_Core_Count = $r.cpu_core_count
$srv.CMDB_OS = $r.os
$srv.CMDB_RecordCount = $r.recordCount
$srv.CMDB_sys_class_name = $r.sys_class_name
}
}
}
# End merging CMDB data with the AD data