optimize script

PowerShell Help
1

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”?>
<SCVMM>
<globalinformation>
<devicename>$env:computername</devicename>
<SCVMMserver>$const_SCVMM_server_long</SCVMMserver>
<inventorydate>$(get-date -Format “yyyyMMdd_HHmmss”)</inventorydate>
<scriptversion>1.0</scriptversion>
</globalinformation>
</SCVMM>
"@ )

##############################################################################

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 = @"
&lt;guestname&gt;$($vm.name)&lt;/guestname&gt;
&lt;SCVMMName&gt;$const_SCVMM_environment&lt;/SCVMMName&gt;
&lt;hostname&gt;$HostName&lt;/hostname&gt;
&lt;hostdomain&gt;$HostDomain&lt;/hostdomain&gt;
&lt;Generation&gt;$($VM.Generation)&lt;/Generation&gt;
&lt;VMAddition&gt;$($VM.VMAddition)&lt;/VMAddition&gt;
&lt;operatingsystem&gt;$($vm.OperatingSystem)&lt;/operatingsystem&gt;
&lt;state&gt;$($vm.virtualmachinestate)&lt;/state&gt;
&lt;location&gt;$($VM.location)&lt;/location&gt;
&lt;Clustername&gt;&lt;/Clustername&gt;
&lt;Hostgroup&gt;$hostgroup&lt;/Hostgroup&gt;
&lt;cloud&gt;$($vm.Cloud.Name)&lt;/cloud&gt;
&lt;scvmmserver&gt;$const_SCVMM_server_long&lt;/scvmmserver&gt;
&lt;creationdate&gt;$($vm.creationTime)&lt;/creationdate&gt;

"@

$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!

2

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

3

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.

4

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