wiki.getshifting.com

--- Sjoerd Hooft's InFormation Technology ---

User Tools

Site Tools

Trace: • Cheatsheet PowerShell • Terraform Module for PostgreSql Flexible Server • Fix: Editing Excel Documents With Powershell as Scheduled Job Fails • PowerShell: Create an Excel report with Server Status and Compliancy
scriptpowershellserverstatus

PowerShell: Create an Excel report with Server Status and Compliancy

Summary: This script is what I use to make one large inventory of all or VMs and physical objects in Active Directory. It creates a nice view in Excel on topics that are important. It allows for vCenter and Active Directory to document itself using the notes and description fields. Please notice so that if an object lives in both vCenter and Active Directory the virtual objects takes precedence.
Date: Around 2014
Refactor: 6 April 2025: Checked links and formatting.

, , ,

Note> To make an extra report on a new property follow these steps:

  • Add the property and configure it correctly to the VMstatus and PSstatus functions
  • In the function ExcelAddRawData add the new property twice
  • In the function excelfile add the report number and name three times

The Script

# get an overall view of all servers, as well as physical as virtual. Combine all data and make it usefull.
# Use multiple vcenters to include all vCenter information
# Use domaincontrollers to include physical systems from multiple domains
# Note that the account is run from shoulde be able to access all these systems
 
# Variables
$scriptname = [System.IO.Path]::GetFilenameWithoutExtension($MyInvocation.MyCommand.Path.ToString())
$scriptlocation = Split-Path $myinvocation.mycommand.path
$timestamp = Get-Date -format "yyyyMMdd-HH.mm"
$weekdate = Get-Date -uformat %V
$date = Get-Date
$vcenters = @("vcenter01.production.domain","vcenter01.getshifting.local")
$domaincontrollers = @("dc01.production.domain","dc01.getshifting.local")
$MailServer = "mailserver.geshifting.local"
$toemail = "sjoerd.hooft_ getshifting.com", "tom_ getsfifting.com"
$fromemail = "scripts_ getshifting.com"
$mailpassword = ConvertTo-SecureString "anonymous" -AsPlainText -Force
$mailcreds = New-Object -typename system.management.automation.pscredential -argumentlist "anonymous",$mailpassword
$csvfilevm = "$scriptlocation\$scriptname.vm.csv"
$csvfileps = "$scriptlocation\$scriptname.ps.csv"
$csvfilecombi = "$scriptlocation\$scriptname.combi.csv"
$csvfilecombiuniq = "$scriptlocation\$scriptname.combi.uniq.csv"
$excelfile = "$scriptlocation\$scriptname.xlsx"
# Use srmds to exclude SRM placeholder objects from the list
$srmds= "BCP_SRM_MGMT"
 
# Start-transcript
start-transcript -path "$scriptlocation\$scriptname.txt"
 
# Remove Files last run
Remove-Item $csvfilevm
Remove-Item $csvfileps
Remove-Item $csvfilecombi
Remove-Item $csvfilecombiuniq
Remove-Item $excelfile
 
# Add VMware snapin
if(-not (Get-PSSnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue)){
   Add-PSSnapin VMware.VimAutomation.Core}
 
# Add Active Directory Module
Import-module ActiveDirectory
 
# Functions
# Email functions
Function Send-Email ($subject, $info, $attachment){
   Send-MailMessage -To $toemail -From $fromemail -SmtpServer $mailserver -Subject $subject -Body $info -Credential $mailcreds -Attachments "$attachment"}
 
Function OSMapping ($guestos){
	if ($guestos -match "Windows NT"){Return "Windows NT"}
	elseif ($guestos -match "Windows 2000"){Return "Windows 2000 Server"}
	elseif ($guestos -match "Windows Server 2003"){Return "Windows Server 2003"}
	# Note the "(" needs to be escaped or powershell will try to parse it and return a "Not enough )'s" error
	elseif ($guestos -match "Windows Server 2008 \("){Return "Windows Server 2008"}
	elseif ($guestos -match "Windows Server? 2008 E"){Return "Windows Server 2008"}
	elseif ($guestos -match "Windows Server? 2008 S"){Return "Windows Server 2008"}
	elseif ($guestos -match "Windows Server 2008 R2"){Return "Windows Server 2008 R2"}
	elseif ($guestos -match "Windows Server 2012"){Return "Windows Server 2012"}
	elseif ($guestos -match "Windows XP"){Return "Windows XP"}
	elseif ($guestos -match "Windows 7"){Return "Windows 7"}
	elseif ($guestos -match "Red Hat"){Return "Red Hat or CentOS"}
	elseif ($guestos -match "CentOS"){Return "Red Hat or CentOS"}
	elseif ($guestos -match "Solaris"){Return "Solaris"}
	elseif ($guestos -match "Debian"){Return "Debian or Ubuntu"}
	elseif ($guestos -match "Ubuntu"){Return "Debian or Ubuntu"}
	elseif ($guestos -match "Other Linux"){Return "Other Linux"}
	elseif ($guestos -match "Other 2.6.x Linux"){Return "Other Linux"}
	else {Return "Unknown"}
}
 
Function AppMapping ($app){
    # Servers Systems, for workstations see below
	if     ($app -eq "aas"){Return "CryptoCard"}
	elseif ($app -eq "ctx"){Return "Citrix Environment"}
	elseif ($app -eq "exc"){Return "Exchange / Domino"}
	elseif ($app -eq "gws"){Return "General Web Services"}
    	elseif ($app -eq "mom"){Return "Microsoft SCOM"}
	elseif ($app -eq "prx"){Return "Proxy"}
	elseif ($app -eq "wus"){Return "Windows Updates"}
	# Workstations
	elseif ($app -eq "dew"){Return "Workstation FrankFurt"}
	elseif ($app -eq "nlw"){Return "Workstation The Hague"}
	elseif ($app -eq "ukw"){Return "Workstation London"}
	else {Return $app}
}
 
Function TypeMapping ($type){
	if ($type -eq "t"){Return "Terminal Server"}
	elseif ($type -eq "a"){Return "Application Server"}
	elseif ($type -eq "d"){Return "Database Server"}
	elseif ($type -eq "f"){Return "File Server"}
	elseif ($type -eq "c"){Return "Application Control Server"}
	elseif ($type -eq "m"){Return "Mailbox"}
	elseif ($type -eq "p"){Return "Print Server"}
	elseif ($type -eq "w"){Return "Web Server"}
	elseif ($type -eq "g"){Return "Gateway Server"}
	else {Return "Not Defined"}
}
 
Function VMStatus ($vcenters){
 
# Create an array to collect all audit information
$myCol = @()
 
ForEach ($vcenter in $vcenters){
 
	# Connect to vCenter
	Connect-VIServer $vcenter
 
	# Get Information for all VMs
		ForEach ($VM in (Get-VM)){
 
		$ds = @(get-datastore -vm $VM | foreach {$_.name})
		if (-not ($ds -match $srmds)){
 
			$vmview = Get-VM $VM | Get-View
			# Get Network Information
			$nictype = Get-NetworkAdapter -VM $VM | ForEach-Object {$_.Type}
			# Get multiple scsi adapters
			$scsitype = Get-ScsiController -VM $VM | Foreach-Object {$_.Type}
 
			$VMInfo = "" |select-Object VMName,Application,ServerType,DomainName,NICType,SCSIType,CPU,MBRAM,Datastore,GBProvisioned,GBUsage,HWLevel,GuestOS,PowerState,ToolsVersion,ToolsStatus,LastLogonDate,ExtraInformation
 
			# Name Information
			$VMInfo.VMName = $vmview.Name
			# Convert to lower case for sorting purposes later
			$VMInfo.VMName = ($VMInfo.VMName).ToLower()
			# Define Application and ServerType
			if ([string]$VMInfo.VMName.Substring(0,1) -eq "_"){
				$app = [string]$VMInfo.VMName.Substring(1,3)
				$type = [string]$VMInfo.VMName.Substring(7,1)
				}
			elseif ([string]$VMInfo.VMName.Substring(3,1) -eq "-"){
				$app = [string]$VMInfo.VMName.Substring(0,7)
				$type = [string]$VMInfo.VMName.Substring(14,1)
				}
			else {
				$app = [string]$VMInfo.VMName.Substring(0,3)
				$type = [string]$VMInfo.VMName.Substring(6,1)
				}
			# App Information
			$VMInfo.Application = AppMapping $app
			# Type Information
			$VMInfo.ServerType = TypeMapping $type
			# Domain Information
			$HostName = $vmview.Guest.HostName
			$domain = ($hostname -split '\.')[1]
			if ($domain -eq $null){$VMInfo.DomainName = "NoDomain"}
			else {$VMInfo.DomainName = $domain}
			# VM Hardware Information
			# NIC
			$VMInfo.NICType = [String]$nictype
			# SCSI
			$VMInfo.SCSIType = [String]$scsitype
			# VM Hardware (Use [int] to round the GB usage attributes)
			$VMInfo.CPU = $VM.numCpu
			$VMInfo.MBRAM = $VM.MemoryMB
			$VMInfo.Datastore = [string]$ds
			$VMInfo.GBProvisioned = [int]$vm.ProvisionedSpaceGB
			$VMInfo.GBUsage = [int]$vm.UsedSpaceGB
			# HW Level
			$VMInfo.HWLevel = $vmview.Config.Version
			# VMware Tools
			$VMInfo.ToolsVersion = $vmview.Guest.ToolsVersion
			$VMInfo.ToolsStatus = $vmview.Guest.ToolsStatus
			# OS
			$vmRunningOS = $vmview.Guest.GuestFullname
			$vmSelectedOS = $vmview.Summary.Config.GuestFullName
			if ($vmRunningOS -eq $null){$guestos = $vmSelectedOS}
			else {$guestos = $vmRunningOS}
			$VMInfo.GuestOS = OSMapping $guestos
			# Powerstate
			$VMInfo.PowerState = $VM.PowerState
			# Last Logon data
			$VMInfo.LastLogonDate = "Exists as VM"
			$VMInfo.ExtraInformation = $VM.Notes
 
			$myCol += $VMInfo
			}
		}
	Disconnect-VIServer	* -confirm:$false
	}
 
$myCol |Export-csv -NoTypeInformation $csvfilevm
 
}
 
Function PSStatus ($domaincontrollers){
 
# Create an array to collect all audit information
$myCol = @()
 
ForEach ($dc in $domaincontrollers){
 
	# Get Information for all VMs
		ForEach ($ps in (get-adcomputer -filter "*" -server $dc)){
 
		# Get Detailed Information
		# $pserver = Get-ADcomputer $ps -server $dc -properties *
		$pserver = Get-ADcomputer $ps -server $dc -properties Name,DNSHostName,OperatingSystem,LastLogonDate,Description
 
		$VMInfo = "" |select-Object VMName,Application,ServerType,DomainName,NICType,SCSIType,CPU,MBRAM,Datastore,GBProvisioned,GBUsage,HWLevel,GuestOS,PowerState,ToolsVersion,ToolsStatus,LastLogonDate,ExtraInformation
 
		# Name Information
		$VMInfo.VMName = $pserver.Name
		# Convert to lower case for sorting purposes later
		$VMInfo.VMName = ($VMInfo.VMName).ToLower()
		# Define Application and ServerType
		if ([string]$VMInfo.VMName.Substring(0,1) -eq "_"){
			$app = [string]$VMInfo.VMName.Substring(1,3)
			$type = [string]$VMInfo.VMName.Substring(7,1)
			}
		elseif ([string]$VMInfo.VMName.Substring(3,1) -eq "-"){
			$app = [string]$VMInfo.VMName.Substring(0,7)
			$type = [string]$VMInfo.VMName.Substring(14,1)
			}
		else {
			$app = [string]$VMInfo.VMName.Substring(0,3)
			$type = [string]$VMInfo.VMName.Substring(6,1)
			}
		# App Information
		$VMInfo.Application = AppMapping $app
		# Type Information
		$VMInfo.ServerType = TypeMapping $type
		# Domain Information
		$HostName = $pserver.DNSHostName
		$domain = ($hostname -split '\.')[1]
		if ($domain -eq $null){$VMInfo.DomainName = "NoDomain"}
		else {$VMInfo.DomainName = $domain}
		# VM Hardware Information
		# NIC
		$VMInfo.NICType = "Physical Server"
		# SCSI
		$VMInfo.SCSIType = "Physical Server"
		# VM Hardware
		$VMInfo.CPU = "Physical Server"
		$VMInfo.MBRAM = "Physical Server"
		$VMInfo.Datastore = "Physical Server"
		$VMInfo.GBProvisioned = "Physical Server"
		$VMInfo.GBUsage = "Physical Server"
		# HW Level
		$VMInfo.HWLevel = "Physical Server"
		# VMware Tools
		$VMInfo.ToolsVersion = "Physical Server"
		$VMInfo.ToolsStatus = "Physical Server"
		# OS
		$vmRunningOS = $pserver.OperatingSystem
		$vmSelectedOS = $pserver.OperatingSystem
		if ($vmRunningOS -eq $null){$guestos = $vmSelectedOS}
		else {$guestos = $vmRunningOS}
		$VMInfo.GuestOS = OSMapping $guestos
		# Last Logon data, see http://social.technet.microsoft.com/wiki/contents/articles/22461.understanding-the-ad-account-attributes-lastlogon-lastlogontimestamp-and-lastlogondate.aspx for more information regarding the used last login property.
		$lastlogon = $pserver.lastlogondate
		$VMinfo.LastLogonDate = ($date - $LastLogon).Days
		# Powerstate, if a physical server has been off for more than 35 days it is considered to be powered off or removed from the domain.
		if ($VMinfo.LastLogonDate -lt "35"){$VMInfo.PowerState = "PoweredOn"}
		else {$VMInfo.PowerState = "PoweredOff"}
		$VMInfo.ExtraInformation = $pserver.Description
 
		$myCol += $VMInfo
		}
	}
 
$myCol |Export-csv -NoTypeInformation $csvfileps
 
}
 
Function ExcelChart ($report, $chartcount, $startrow, $endrow){
# Determine chart range
$endrow = $currentrow - 1
# Set the chartrange using variables by separating the : from the $ with {} on the left hand side
$chartRange = $sheet.Range("a${startrow}:b$endrow")
# Select the range for the chart to automatically recognize the correct Y and X axis
$chartRange.Select()
#Add a chart to the workbook
$chart = $sheet.Shapes.AddChart().Chart
# Use xlBarClustered chart with nice layout
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$chart.chartType=$xlChart::xlBarClustered
$chart.ApplyLayout(6)
$chart.ChartStyle = 3
# Set chart range (not required because chartrange was selected before chart creation)
# $chart.SetSourceData($chartRange)
# Setting height and positioning
if ($chartcount -gt "12"){$sheet.shapes.item("Chart 1").Height = 500}
Else {$sheet.shapes.item("Chart 1").Height = 300}
$sheet.shapes.item("Chart 1").Width = 700
$sheet.shapes.item("Chart 1").Top = 50
$sheet.shapes.item("Chart 1").Left = 100
# Set Chart Title
$chart.HasTitle = $True
$chart.ChartTitle.Text = "$report"
# Hide the axis title below the chart
$chart.Axes(2,1).HasTitle = $False
# Show values in chart
$chart.ApplyDataLabels()
 
# Now autofit everything
$usedRange = $sheet.UsedRange
$usedRange.EntireColumn.AutoFit() | Out-Null
}
 
Function ExcelReport($report, $value){
# Create Report 1
# Connect to report sheet
$sheet = $workbook.Worksheets.Item($report)
# Get All possible option and sort them alphabetically:
$ourOptions = @(import-csv $csvfilecombiuniq | select $value -unique | sort $value)
$chartcount = ($ourOptions).Count
$startrow = 5
$currentrow = $startrow
ForEach ($option in $ourOptions){
	$optionname = $option.$value
	# We always need a array to be in the variable, otherwise there is no count if there's a single value
	$countOptionOn = @(import-csv $csvfilecombiuniq | where {$_.$value -eq $option.$value -and $_.Powerstate -eq "PoweredOn"}).Count
	$countOptionOff = @(import-csv $csvfilecombiuniq | where {$_.$value -eq $option.$value -and $_.Powerstate -eq "PoweredOff"}).Count
	# Entering data for Powered on VMs
	if ($countOptionOn -ne 0){
		$sheet.Cells.Item($currentrow,1) = "PoweredOn - $optionname"
		$sheet.Cells.Item($currentrow,2) = $countOptionOn
		$currentrow ++
	}
	# Entering data for Powered off VMs
	if ($countOptionOff -ne 0){
		$sheet.Cells.Item($currentrow,1) = "PoweredOff - $optionname"
		$sheet.Cells.Item($currentrow,2) = $countOptionOff
		$currentrow ++
	}
}
ExcelChart $report $chartcount $startrow $currentrow
}
 
Function ExcelAddRawData {
# Create new worksheet to add raw csv data
$worksheet = $workbook.Worksheets.Add()
$sheet = $workbook.Worksheets.Item(1)
$sheet.Name = "RawCSVData"
 
# Import CSV data
$allcomputers = Import-Csv $csvfilecombiuniq
# Set understandable header information
$sheet.cells.item(1,1) = "ServerName"
$sheet.cells.item(1,2) = "Application"
$sheet.cells.item(1,3) = "ServerType"
$sheet.cells.item(1,4) = "Domain"
$sheet.cells.item(1,5) = "Virtual NIC"
$sheet.cells.item(1,6) = "Virtual SCSI Adapter"
$sheet.cells.item(1,7) = "vCPU"
$sheet.cells.item(1,8) = "RAM in MB"
$sheet.cells.item(1,9) = "Virtual Datastore"
$sheet.cells.item(1,10) = "Provisioned Storage in GB"
$sheet.cells.item(1,11) = "Used Storage in GB"
$sheet.cells.item(1,12) = "Virtual HW Level"
$sheet.cells.item(1,13) = "OS"
$sheet.cells.item(1,14) = "Power State"
$sheet.cells.item(1,15) = "Virtual Tools Version"
$sheet.cells.item(1,16) = "Virtual Tools Status"
$sheet.cells.item(1,17) = "Server Last Logon Date"
$sheet.cells.item(1,18) = "Extra Information"
 
$row = 2
foreach($pc in $allcomputers){
    $sheet.cells.item($row,1) = $pc.VMName
	$sheet.cells.item($row,2) = $pc.Application
	$sheet.cells.item($row,3) = $pc.ServerType
	$sheet.cells.item($row,4) = $pc.DomainName
	$sheet.cells.item($row,5) = $pc.NICType
	$sheet.cells.item($row,6) = $pc.SCSIType
	$sheet.cells.item($row,7) = $pc.CPU
	$sheet.cells.item($row,8) = $pc.MBRAM
	$sheet.cells.item($row,9) = $pc.Datastore
	$sheet.cells.item($row,10) = $pc.GBProvisioned
	$sheet.cells.item($row,11) = $pc.GBUsage
	$sheet.cells.item($row,12) = $pc.HWLevel
	$sheet.cells.item($row,13) = $pc.GuestOS
	$sheet.cells.item($row,14) = $pc.PowerState
	$sheet.cells.item($row,15) = $pc.ToolsVersion
	$sheet.cells.item($row,16) = $pc.ToolsStatus
	$sheet.cells.item($row,17) = $pc.LastLogonDate
	$sheet.cells.item($row,18) = $pc.ExtraInformation
	$row++
	}
}
 
Function ExcelFile (){
# Set Excel properties
# Create Excel COM object
$Excel = New-Object -ComObject Excel.Application
# Set to true for monitoring progress, to false for scheduled creation
$Excel.visible = $false
# Set to False to save existing files without warning
$Excel.DisplayAlerts = $False
# Add a workbook
$workbook = $excel.Workbooks.Add()
 
# Create required Worksheets and Name and title them
# Reports
$report1 = "OS Information"
$name1 = "OS Name"
$report2 = "VM Hardware Information"
$name2 = "Hardware Version"
$report3 = "VM Tools State Information"
$name3 = "Tools State"
$report4 = "VM Tools Version Information"
$name4 = "Tools Version"
$report5 = "VM Domain Information"
$name5 = "Domain Name"
$report6 = "Application Information"
$name6 = "Applications"
$report7 = "Server Type Information"
$name7 = "Server Type"
$reportcount = 7
$currentreport = 1
$totalvms = (import-csv $csvfilecombiuniq).Count
 
# Create a seperate worksheet for each report and title it
$currentreport..$reportcount | ForEach{
	$worksheet = $workbook.Worksheets.Add()
    $sheet = $workbook.Worksheets.Item(1)
	If ($currentreport -eq "1"){$sheet.Name = $report1; $title = $report1; $name = $name1}
	ElseIf ($currentreport -eq "2"){$sheet.Name = $report2; $title = $report2; $name = $name2}
	ElseIf ($currentreport -eq "3"){$sheet.Name = $report3; $title = $report3; $name = $name3}
	ElseIf ($currentreport -eq "4"){$sheet.Name = $report4; $title = $report4; $name = $name4}
	ElseIf ($currentreport -eq "5"){$sheet.Name = $report5; $title = $report5; $name = $name5}
	ElseIf ($currentreport -eq "6"){$sheet.Name = $report6; $title = $report6; $name = $name6}
	ElseIf ($currentreport -eq "7"){$sheet.Name = $report7; $title = $report7; $name = $name7}
	Else {$Sheet.Name = "Report Name Unknown"; $title = "Report Name Unknown"}
 
	# Set title and common knowledge on each sheet
	$sheet.Cells.Item(1,1) = $title
	$range = $sheet.Range("a1","b2")
	$range.Style = 'Title'
	$range.Merge() | Out-Null
	$range.VerticalAlignment = -4108
	$range.HorizontalAlignment = -4108
 
	# Fill in number
	$sheet.Cells.Item(3,1) = "Total Servers"
	$sheet.Cells.Item(3,2) = $totalvms
	# Set up header with autofilter
	$sheet.Cells.Item(4,1) = "$name"
	$sheet.Cells.Item(4,2) = "Count"
	$headerRange = $sheet.Range("a4","b4")
	$headerRange.AutoFilter() | Out-Null
 
 
	$currentreport ++
	}
 
ExcelReport $report1 GuestOS
ExcelReport $report2 HWLevel
ExcelReport $report3 ToolsStatus
ExcelReport $report4 ToolsVersion
ExcelReport $report5 DomainName
ExcelReport $report6 Application
ExcelReport $report7 ServerType
 
ExcelAddRawData
 
# Save and close Excelfile
$sheet.SaveAs($ExcelFile)
#$worksheet.Close()
#Quit Excel
sleep 5
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
 
}
 
#Start Actual Script
VMStatus $vcenters
PSStatus $domaincontrollers
 
# Combine and make the entries of the two csv files unique
# First sort on both VMName and the LastLogonDate so the order is OK (VM First)
$import1 = Import-csv $csvfilevm
$import2 = import-csv $csvfileps
$combined = $import1 + $import2
$combined | sort VMname,LastLogonDate -Unique | Export-Csv -notypeinformation $csvfilecombi
# Now sort on just unique so that entries that are both in AD and in vSphere just appear as in vSphere (so all other servers are gone or physical servers)
$import3 = import-csv $csvfilecombi
$import3 | sort VMname -Unique | Export-Csv -notypeinformation $csvfilecombiuniq
 
ExcelFile
 
# Send Report
$attachment = $excelfile
$subject = "GetShifting VM Compliany Report for week $weekdate"
$info = "Find the report attached to this email. Kind regards, Sjoerd Hooft."
Send-Email $subject $info $attachment
 
# Stop logging
stop-transcript
 
# More Information
# http://learn-powershell.net/2012/12/24/powershell-and-excel-adding-a-chart-and-header-filter-to-a-report/
# http://theolddogscriptingblog.wordpress.com/2010/06/01/powershell-excel-cookbook-ver-2/
scriptpowershellserverstatus.txt · Last modified: by 127.0.0.1