Generating The CMDB True Up Report – Pragmatic PowerShell Scripting

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.

This post assumes you have read the previous posts in this series and have the data ready to export.

# Variables from the previous posts
# $path - The full path for the output including the filename
# $CMDBservers - Has the exported ServiceNow CMDB data
# $NTNXReport - Has The Nutanix VM data and merged CMDB data
# $ADSvrs - Has the server data from AD and merged CMDB data

Layout

The report layout will consist of 4 worksheets.

CMDBData – an export of the $CMDBServers variable
ADData – an export of the $ADSvrs variable
NTNXData – an export of the $NTNXReport
Summary – Charts showing the percentages of servers in Nutanix and in AD that are found in the CMDB.

Create the Excel object:

Import-Module ImportExcel
# Start create an in memory Excel Object
$excelRpt = Open-ExcelPackage -Path $path -create
Remove report file if it exists
Remove-Item $excelReport -ErrorAction SilentlyContinue
# Add the required worksheets
Add-WorkSheet -ExcelPackage $excelRpt -WorksheetName 'CMDBData'
Add-WorkSheet -ExcelPackage $excelRpt -WorksheetName 'ADData'
Add-WorkSheet -ExcelPackage $excelRpt -WorksheetName 'NTNXData'
Add-WorkSheet -ExcelPackage $excelRpt -WorksheetName 'Summary'
# End create an in memory Excel Object

Add the data to the Excel object:

# Start add gathered data to Excel object
$excelRpt =  $CMDBServers | Export-Excel -ExcelPackage $excelRpt -WorksheetName 'CMDBData' -Autosize -AutoFilter -PassThru
$excelRpt =  $ADSvrs| Export-Excel -ExcelPackage $excelRpt -WorksheetName 'ADData' -Autosize -AutoFilter -PassThru
$excelRpt =  $NTNXReport| Export-Excel -ExcelPackage $excelRpt -WorksheetName 'NTNXData' -Autosize -AutoFilter -PassThru
# End add gathered data to Excel object

Create the Summary Data:

# Start create the parameters for the two pivots
$ntnxpivotTableParams = @{
    PivotTableName  = "NTNX_CMDB"
    Address         = $excelRpt.Summary.cells["a1"]
    SourceWorkSheet = $excelRpt.NTNXData
    PivotRows       = @("inCMDB")
    PivotData       = @{'inCMDB' = 'Count'}
    PivotTableStyle = 'Light21'
}
$adpivotTableParams = @{
    PivotTableName  = "AD_CMDB"
    Address         = $excelRpt.Summary.cells["a10"]
    SourceWorkSheet = $excelRpt.ADData
    PivotRows       = @("inCMDB")
    PivotData       = @{'inCMDB' = 'Count'}
    PivotTableStyle = 'Light21'
}
# Create the Nutanix/CMDB compare.
Add-PivotTable @ntnxpivotTableParams -PassThru -IncludePivotChart -ChartType Pie -ShowPercent -ChartColumn 5 -ChartTitle "Nutanx/CMDB Compare"
# Create the AD/CMDB compare.
Add-PivotTable @adpivotTableParams -PassThru -IncludePivotChart -ChartType Pie -ShowPercent -ChartColumn 15 -ChartTitle "AD/CMDB Compare"
# End create the parameters for the two pivots

Write out the Report and Distrubute:

# Start write the Excel object out and close it
Close-ExcelPackage -ExcelPackage $excelRpt # saves the report via the $path variable
# End write the Excel object out and close it
#Start the monthly email distribution
$SMTPServer = $SMTPServer
$MailSender = $NoReplyAddress
$MontlyEmailAddress = $MontlyDL
$DailyEmailAddress =  $DailyDL
$MonthlySubject = 'Monthly CMDB True Up Report'
$DailySubject = 'Server Inventory'
if(Test-Path $path){
   
   Send-MailMessage -To $dailyDL -From $MailSender -SmtpServer $SMTPServer -Subject $Subject -Body "See attached file. This is a scheduled daily server inventory report." -Attachments $path
   Write-EventLog -LogName Application -Source "CustomScripts" -EventId 1000 -EntryType Information -Message "Daily report emailed"
 
   If ($monthlyReport)
   {
      Write-EventLog -LogName Application -Source "CustomScripts" -EventId 1000 -EntryType Information -Message "Monthly CMDB Health Report"
      Send-MailMessage -To $dlist -From $MailSender -SmtpServer $SMTPServer - 
      Subject $MonthlySubject -Body "See attached Excel file. This is an automated scheduled monthly report." -Attachments $path
      Write-EventLog -LogName Application -Source "CustomScripts" -EventId 1000 -EntryType Information -Message "Monthly report emailed"
   }
}else {
      Write-EventLog -LogName Application -Source "CustomScripts" -EventId 1000 -EntryType Error -Message "Server Inventory was not emailed - Trouble shooting required"

Send-MailMessage -To $dailyDL -From $MailSender -SmtpServer $SMTPServer -Subject $Subject -Body "Server Inventory was not emailed - Trouble shooting required" -Attachments $path
Exit
   }

Write-EventLog -LogName Application -Source "CustomScripts" -EventId 1000 -EntryType Information -Message "Server Inventory Completed"
Exit