Author Posts

May 19, 2014 at 6:57 am

Hi,

I would like to optimize this script as it seems that there is some room for improvement.
What would you do?

ipmo 'virtualmachinemanager\virtualmachinemanager.psd1'

$const_SCVMM_server_long = "xxxxxxxx-001.cds.xxxx.net"
$const_SCVMM_server_short = "xxxxxxxx-001"
$const_SCVMM_environment = "MS cloud v1.0"
$const_outputfilename = $const_SCVMM_server_short + ".xml"

[XML]$SCVMM_xml = New-Object system.Xml.XmlDocument
$SCVMM_xml.LoadXml( @"
< ?xml version="1.0" encoding="utf-8"?>


$env:computername
$const_SCVMM_server_long
$(get-date -Format "yyyyMMdd_HHmmss")
1.0


"@ )

##############################################################################
# read guest information
##############################################################################

$VMs = Get-VM -VMMServer $const_SCVMM_server_long

foreach ($VM in $VMs) {
"Processing $($vm.name)"
$hostgroup = $($vm.hostgrouppath).Replace("All Hosts\","")
$hostgroup = $hostgroup.Replace($("\" + $vm.name),"")
$hostname = $($vm.HostName).split('.',2)[0]
$hostdomain = $($vm.HostName).split('.',2)[1]

$SCVMM_guest_data = @"
$($vm.name)
$const_SCVMM_environment
$HostName
$HostDomain
$($VM.Generation)
$($VM.VMAddition)
$($vm.OperatingSystem)
$($vm.virtualmachinestate)
$($VM.location)

$hostgroup
$($vm.Cloud.Name)
$const_SCVMM_server_long
$($vm.creationTime)
"@

$element = $SCVMM_xml.CreateElement("SCVMM_guest")
$element.InnerXml = $SCVMM_guest_data
$SCVMM_xml.SCVMM.AppendChild($element) | Out-Null

}

$SCVMM_xml.save($const_outputfilename)

##############################################################################
# upload that data to the CMDB database
##############################################################################

# Database initialisation
$dbconn = New-Object System.Data.SqlClient.SqlConnection
$dbconn.connectionstring = "Data Source=xx.xxxx.xx.com; Initial Catalog=xxx;User ID=Xmlimort;Password=Mis`xxxxxx@;"
$dbconn.Open()

#delete all the previous records first
$query_delete = "DELETE FROM [xmlimport].[XML_IMPORT] where datasource ='SCVMM' and datasourcefilter = '$const_SCVMM_server_short'"
$dbwrite = $dbconn.CreateCommand()
$dbwrite.CommandText = $query_delete
$query_result = $dbwrite.ExecuteNonQuery()

#read basic information and store it in the XML_Import table
$query_insert = "INSERT INTO [xmlimport].[XML_IMPORT] ([ImportDate],[Filename],[XMLimport],[DataSource],[DataSourceFilter]) VALUES "
$query_insert += "(getdate(),'$const_outputfilename','$($SCVMM_xml.outerxml)','SCVMM','$const_SCVMM_server_short')"

$dbwrite = $dbconn.CreateCommand()
$dbwrite.CommandText = $query_insert
$query_result = $dbwrite.ExecuteNonQuery()

Any help is appreciated!

May 19, 2014 at 7:38 am

It looks pretty reasonable to me, at first glance. Which parts do you think are taking too long?

May 19, 2014 at 11:18 pm

You can use Measure-Command {} in different parts of your script and see which ones take the most time to finish. Then you'll know which parts need optimization.

May 19, 2014 at 11:53 pm

Hello Christophe,

A few ideas how you can improve the reusability of your script.

# Convert the constants into a param block to make it easier to reuse the script in different environments
Param
(
[ValidateNotNullOrEmpty()]
[System.String]$VMMServer = 'xxxxxxxx-001.cds.xxxx.net',

[ValidateNotNullOrEmpty()]
[System.String]$Environment = 'MS cloud v1.0',

[ValidateNotNullOrEmpty()]
[System.String]$DatabaseServer = 'xx.xxxx.xx.com',

[ValidateNotNullOrEmpty()]
[System.String]$DatabaseName = 'xxx'
)

# Expand ipmo alias and use the fully qualified path name to the module manifest
Import-Module 'C:\...\virtualmachinemanager\virtualmachinemanager.psd1'

# Use the parameters to create the other variables. Let PowerShell do the work and get the short name of the VMM server.
$VMMServerShort = $VMMServer.Split('.')[0]
$OutputFilename = "$VMMServerShort.xml"

# Wrap your data gathering and database code into a try/catch block
try
{
$VMs = Get-VM -VMMServer $VMMServer
...
# Try not to hard code user name and password into your scripts. Use Windows authentication with a service account instead.
$dbconn.connectionstring = “Data Source=$DatabaseServer; Initial Catalog=$DatabaseName; Integrated Security = SSPI;”
...
}
catch
{
# Log the exception into a file or event log
}

Best,
Daniel