optimize script

This topic contains 3 replies, has 4 voices, and was last updated by Profile photo of Daniel Krebs Daniel Krebs 2 years, 6 months ago.

  • Author
    Posts
  • #15365
    Profile photo of Christophe Lams
    Christophe Lams
    Participant

    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!

  • #15366
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

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

  • #15405
    Profile photo of Istvan Szarka
    Istvan Szarka
    Participant

    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.

  • #15406
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    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

You must be logged in to reply to this topic.