8 Replies Latest reply on Jun 29, 2020 10:54 PM by LucD

    Delete VM's and output results - PART 2

    piercj2 Enthusiast

      I'm still having issues passing values/variables between functions in a script.

      I've added a new function to the original script

       

      function Get-CIStatus {

          $cmdbServer = "AUSPWIDCLPAGL01.aus.amer.dell.com"

          $cmdbDatabase = "IDCLP"

          $cmdbUsername = "svc_VMwareEng"

          $cmdbPassword = "ymA9BeCs79m5cMYbDFfZ8_"

       

          $cmdbQuery = "USE $cmdbDatabase

              SELECT * FROM [IDCLP].[dbo].CMDB_CI

              WHERE Name='durvcprod01'"

       

          $cmdbQueryResult = Invoke-Sqlcmd -Query $cmdbQuery -ServerInstance $cmdbServer -Username $cmdbUsername -Password $cmdbPassword

          [string]$Script:cmdbQueryResultStatus = $cmdbQueryResult | select -ExpandProperty Status | join "|"

          $Script:cmdbQueryResultStatus

      }

       

      The above returns what can be multiple values for each VM, "Build", "Installed", "Decommissioned"

       

      I then try to call this function from within another any output the results to an array

       

      function Get-Details {

          Start-Sleep -Seconds 5

          Read-Host "When you have saved the text file, press ENTER to continue..."

          $Script:decomReport = @()

          $dir = $Script:directory

          $VMList = Get-VM (Get-Content $Script:file) | Sort-Object

          $count = $VMList.count

          $i = 1

          foreach($vm in $VMList){

              Write-Progress -Activity "Collecting details on provided VMs" -Status "Working on $vm" -PercentComplete (($i*100)/$count)

              $ip = $vm.guest.IPAddress[0]

              $powerState = $vm.PowerState

              $memory = $vm.memoryGB

              $hddSize = [math]::Round(((Get-HardDisk -VM $vm).CapacityGB | Measure-Object -Sum).Sum)

              $vmProperty = [ordered] @{

                  'vCenter' = $global:DefaultVIServer.Name

                  'VM Name' = $vm.Name

                  'IP Address' = $ip

                  'PowerState' = $powerState

                  'Memory (GB)' = $memory

                  'Disk Capacity (GB)' = $hddSize

              }

       

              # Download the vmware.log file to a local PSDrive

              $logPath = $vm.Extensiondata.Config.Files.LogDirectory

              $dsName = $logPath.Split(']')[0].Trim('[')

              $vmPath = $logPath.Split(']')[1].Trim(' ')

              $ds = Get-Datastore -Name $dsName

              $drvName = "LogCollect-" + (Get-Random)

              New-PSDrive -Location $ds -Name $drvName -PSProvider VimDatastore -Root '\' | Out-Null

              Copy-DatastoreItem -Item ($drvName + ":" + $vmPath + "vmware.log") -Destination ($dir + "\" + $vm.Name + "\") -Force:$true

       

              # Get the date of the last log entry

              $lastLine = Get-Content ($dir + "\" + $vm.Name + "\" + "vmware.log") -Tail 1

              [datetime]$LastLogDate = $lastLine.Split("|")[0]

              $vmProperty.Add("Date of last Log entry",$LastLogDate)

              #$vmProperty.Add("Can be deleted","If all criteria met")

              $vmProperty.Add("Removed from Inventory",(Get-Date -Format yyyy-MMM-dd-HHmm))

       

              # determine number of days VM has been powered off

              $today = Get-Date

              $daysOff = New-TimeSpan -Start $today -End $LastLogDate

              $absoluteDaysOff = [math]::Abs($daysOff.Days)

              $vmProperty.Add("Powered Off for (Days)",$absoluteDaysOff)

       

              # add the CI Status to the results

              $vmProperty.Add("CI Status", (Get-CIStatus))

       

              Remove-PSDrive -Name $drvName -Confirm:$false | Out-Null

              $Script:decomReport += New-Object -TypeName psobject -Property $vmProperty

              $i++

          }

       

          $Script:decomReport |

              Sort-Object -Property 'VM Name' |

               Export-Excel @Script:excelHash

              

      }

       

      The Excel output for "CI Status" displays System.Object[]

      not "Decommissioned" or "Installed" or whatever the actual CI Status is from our Asset Management Database

       

      The end goal will be to read from the $Script:decomReport array (or better still form a dedicated SQL Database) and have a function that deletes VM's from disk

      e.g.

      function Remove-Decom {

          # Placeholder to delete Decommissioned VM's once pre-req's are met

          <#

          Read from array or SQL DB

          if ($ciStatus -eq "Decommissioned") AND PoweredDown for more than 30 days

         

          Delete VM From Inventory

          #>

      }

       

       

      Thanks

        • 1. Re: Delete VM's and output results - PART 2
          LucD Guru
          vExpertCommunity WarriorsUser Moderators

          That means that the value in that column is an array.

          An easy way to convert an array to a single string is with the -join operator.

           

          $vmProperty.Add("CI Status", (Get-CIStatus) -join '|')

          Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
          • 2. Re: Delete VM's and output results - PART 2
            piercj2 Enthusiast

            Thanks Luc, that worked.

             

            only one piece to the puzzle left

             

            function Remove-Decom {

                foreach ($item in $Script:decomReport){

                    if (($item.'powered off for (Days)' -ge 30) -and ($item.'CI Status' -notlike 'Installed' -or 'Build' -or 'In Maintenance')) {

                        $timeStamp = Get-Date -Format yyyy-MMM-dd-HHmm

                        $viServerUser = # username of person logged into vCenter

                        Write-Host -ForegroundColor DarkMagenta "$item.'VM Name' can be deleted"

                        Write-Host -ForegroundColor DarkMagenta "$item.'VM Name' deleted on $timeStamp by $viServerUser"

                    } else {

                        Write-Host -ForegroundColor DarkRed "$item.'VM Name' does NOT meet deletion criteria. $item.'VM Name' will NOT be deleted from disk"

                    }

                }

            }

             

            The output is returning all properties the $item, not just the $item.'VM Name'

             

            Question:

            - is there a way to identify the username of the person running the script or, the person logged into the vCenter

            - is my if statement correct, are all the "-or" statements good/bad ?

             

            thanks !

            • 3. Re: Delete VM's and output results - PART 2
              LucD Guru
              vExpertCommunity WarriorsUser Moderators

              The user that established the vSphere Server connection can be found with

               

              ($global:defaultVIServer).User

               

              The user in whose session the script is running can be found with

               

              $env:USERNAME

               

              Your condition should probably look like this (if I understood the intention correctly)

               

              if (($item.'powered off for (Days)' -ge 30) -and ('Installed','Build','In Maintenance' -notcontains $item.'CI Status')) {
              Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
              • 4. Re: Delete VM's and output results - PART 2
                piercj2 Enthusiast

                That's helped but my output still looks like

                 

                '@{vCenter=myVC01.us.corp.com; VM Name=VCPROD01; IP Address=10.10.10.33; PowerState=PoweredOn; Memory (GB)=32; Disk Capacity (GB)=599;  Powered Off for (Days)=100; CI Status=Decommissioned|Installed}.'VM Name'' does NOT meet deletion criteria. '@{vCenter=myVC01.us.corp.com; VM Name=VCPROD01; IP Address=10.10.10.33; PowerState=PoweredOn; Memory (GB)=32; Disk Capacity (GB)=599; Powered Off for (Days)=100; CI Status=Decommissioned|Installed}.'VM Name'' will NOT be deleted from disk

                • 5. Re: Delete VM's and output results - PART 2
                  LucD Guru
                  vExpertUser ModeratorsCommunity Warriors

                  Not exactly sure what exactly you mean, but if it is the output coming from Write-Output in that function, you should enclose the variables in $()

                  Whenever you want to substitute a property of an object in a string, you have to enclose it.

                  Like this

                   

                  Write-Host -ForegroundColor DarkMagenta "$($item.'VM Name') can be deleted"
                  Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                  1 person found this helpful
                  • 6. Re: Delete VM's and output results - PART 2
                    piercj2 Enthusiast

                    That was exactly it, thank you !

                     

                    function Remove-Decom {

                        foreach ($item in $Script:decomReport){

                            if (($item.'powered off for (Days)' -ge 30) -and ('Installed','Build','In Maintenance' -notcontains $item.'CI Status' )) {

                                $timeStamp = Get-Date -Format yyyy-MMM-dd-HHmm

                                $viServerUser = ($global:DefaultVIServer).User

                                Write-Host -ForegroundColor DarkMagenta "$($item.'VM Name') can be deleted"

                                Write-Host -ForegroundColor DarkMagenta "$($item.'VM Name') deleted on $timeStamp by $viServerUser"

                            } else {

                                Write-Host -ForegroundColor DarkRed "$($item.'VM Name') does NOT meet deletion criteria. $($item.'VM Name') will NOT be deleted from disk"

                            }

                        }

                    }

                    • 7. Re: Delete VM's and output results - PART 2
                      piercj2 Enthusiast

                      I thought that I had a working script, all the individual functions work as expected but, when i put them all together, the $Script:decomReport array isn't getting updated by the Get-ciStatus or Remove-Decom functions.

                       

                      Full script below

                       

                      <#

                      .SYNOPSIS

                      Retrieve the virtual machine logs to identify how long they are Powered down

                      Query external DB to verify safe to delete VM

                      .DESCRIPTION

                      The function retrieves the logs from one or more virtual machines and stores them in a local folder

                      Date of last Log entry is captured

                      .NOTES

                      Credits:  Luc Dekens

                      #>

                       

                      Set-PowerCLIConfiguration -DefaultVIServerMode Single -InvalidCertificateAction Ignore -Scope Session -Confirm:$False | Out-Null

                       

                      function Install-SqlServer{

                          If (!(Get-Module -Name SqlServer)) {

                              find-Module -Name SqlServer | Install-Module -Scope CurrentUser -Confirm:$false

                          }

                      }

                       

                      function Install-PowerCLI {

                          If (!(Get-Module -Name VMware.VimAutomation.Cis.Core)) {

                              find-Module -Name VMware.VimAutomation.Cis.Core | Install-Module -Scope CurrentUser -Confirm:$false

                          }

                      }

                       

                       

                      function Login-VCSA {

                          Disconnect-VIServer * -Confirm:$False -WarningAction SilentlyContinue

                          $targetVC = Read-Host "Enter the FQDN of the vCenter you want to connect to"

                          Connect-VIServer $targetVC | Out-Null -ErrorAction SilentlyContinue

                          if($DefaultVIServer -eq $null) {

                              Write-Host -ForegroundColor Red "Error: You need to connect to your vSphere environment before running this. Please do so now and try again"

                              exit

                          }

                      }

                       

                       

                      # Create folder to store results

                      function Create-Directory {

                          $Script:directory = $null

                          $Script:directory = Read-Host "Enter local directory to store results"

                          if ( !(Test-Path -LiteralPath $Script:directory)) {

                              try {

                                  New-Item -Path $Script:directory -ItemType Directory -ErrorAction Stop | Out-Null

                              }

                              catch {

                                  Write-Error -Message "Unable to create directory '$Script:directory'. Error was: $_" -ErrorAction Stop

                              }

                              Write-Host -ForegroundColor Green "Successfully created directory '$Script:directory'."

                              } else {

                                  Write-Host -ForegroundColor Yellow "Directory '$Script:directory' already exists - not creating it again"

                              }

                      }

                       

                       

                      # Create text file that contains VM Names

                      function Create-File {

                          $Script:file  = $null

                          $Script:file = $Script:directory + "\" + "vmList.txt"

                          if ( !(Test-Path -LiteralPath $Script:file -PathType Leaf)) {

                              Set-Content -Encoding UTF8 -LiteralPath $Script:file -Value ""

                              Write-Host -ForegroundColor Green "Successfully created '$Script:file'."

                          } else {

                              Write-Host -ForegroundColor Yellow "File '$Script:file' already exists - not creating it again"

                          }

                          Write-Host "Enter VM Names into the text file and save it. Opening File for you now."

                          start-sleep -Seconds 3

                          Invoke-Item $Script:file

                      }

                       

                       

                      # install ImportExcel module if not already installed and prepare layout

                      #function global:Prepare-Excel {

                      function script:Prepare-Excel {

                          If (!(Get-module -ListAvailable "ImportExcel")) {

                              Find-Module -Name ImportExcel | Install-Module -Scope CurrentUser

                          }

                       

                          $cannotBeDeleted = New-ConditionalText -Text PoweredOn -BackgroundColor Yellow -ConditionalTextColor Red

                          $ciStatus = New-ConditionalText -Text Installed -BackgroundColor Yellow -ConditionalTextColor Red

                       

                          $Script:excelHash = @{

                              Path = $Script:directory + "\DecomList-" + (Get-Date -Format yyyy-MMM-dd-HHmm) + ".xlsx"

                              Show = $true;

                              AutoSize = $true;

                              AutoFilter = $true;

                              ConditionalText = $cannotBeDeleted, $ciStatus

                              ShowPercent = $true;

                              HideSheet = "Sheet1";

                          }

                      }

                       

                       

                      function Get-CIStatus {

                          $cmdbServer = "myDatabaseServer.corp.com"

                          $cmdbDatabase = "myDatabase"

                          $cmdbUsername = "dbUser"

                          $cmdbPassword = "dbPassword"

                          $vmName = $Script:vm.name

                       

                          $cmdbQuery = "USE $cmdbDatabase

                              SELECT * FROM [myDatabase].[dbo].CMDB_CI

                              WHERE Name=$vmName"

                       

                          $cmdbQueryResult = Invoke-Sqlcmd -Query $cmdbQuery -ServerInstance $cmdbServer -Username $cmdbUsername -Password $cmdbPassword

                          [string]$Script:cmdbQueryResultStatus = ($cmdbQueryResult | select -ExpandProperty Status) -join '|'

                          $Script:cmdbQueryResultStatus

                          $vmProperty.Add("CI Status",$Script:cmdbQueryResultStatus)

                      }

                       

                       

                      function Remove-Decom {

                          foreach ($item in $Script:decomReport){

                              if (($item.'powered off for (Days)' -ge 30) -and ('Design','Build','Installed','In Maintenance' -notcontains $item.'CI Status' )) {

                                  $timeStamp = Get-Date -Format yyyy-MMM-dd-HHmm

                                  $viServerUser = ($global:DefaultVIServer).User

                                  $vmProperty.Add("Removed from Inventory On",$timeStamp)

                                  $vmProperty.Add("Removed from Inventory By",$viServerUser)

                                  Write-Host -ForegroundColor DarkMagenta "$($item.'VM Name') can be deleted"                                    

                                  Write-Host -ForegroundColor DarkMagenta "$($item.'VM Name') deleted on $timeStamp by $viServerUser"

                                  #Remove-VM -vm $($item.'VM Name') -DeletePermanently -RunAsync -WhatIf

                                  #$Script:decomReport += New-Object -TypeName psobject -Property $vmProperty

                              } else {

                                  Write-Host -ForegroundColor DarkRed "$($item.'VM Name') does NOT meet deletion criteria. $($item.'VM Name') will NOT be deleted from disk"

                              }

                          }

                      }

                       

                       

                      function Get-Details {

                          Start-Sleep -Seconds 5

                          Read-Host "When you have saved the text file, press ENTER to continue..."

                          $dir = $Script:directory

                          $VMList = Get-VM (Get-Content $Script:file) | Sort-Object

                          $count = $VMList.count

                          $i = 1

                          $Script:decomReport = @()

                          foreach($Script:vm in $VMList){

                              Write-Progress -Activity "Collecting details on provided VMs" -Status "Working on $Script:vm" -PercentComplete (($i*100)/$count)

                              $ip = $Script:vm.guest.IPAddress[0]

                              $powerState = $Script:vm.PowerState

                              $memory = $Script:vm.memoryGB

                              $hddSize = [math]::Round(((Get-HardDisk -VM $Script:vm).CapacityGB | Measure-Object -Sum).Sum)

                              $vmProperty = [ordered] @{

                                  'vCenter' = $global:DefaultVIServer.Name

                                  'VM Name' = $Script:vm.Name

                                  'IP Address' = $ip

                                  'PowerState' = $powerState

                                  'Memory (GB)' = $memory

                                  'Disk Capacity (GB)' = $hddSize

                              }

                       

                              # Download the vmware.log file to a local PSDrive

                              $logPath = $Script:vm.Extensiondata.Config.Files.LogDirectory

                              $dsName = $logPath.Split(']')[0].Trim('[')

                              $vmPath = $logPath.Split(']')[1].Trim(' ')

                              $ds = Get-Datastore -Name $dsName

                              $drvName = "LogCollect-" + (Get-Random)

                              New-PSDrive -Location $ds -Name $drvName -PSProvider VimDatastore -Root '\' | Out-Null

                              Copy-DatastoreItem -Item ($drvName + ":" + $vmPath + "vmware.log") -Destination ($dir + "\" + $Script:vm.Name + "\") -Force:$true

                       

                              # Get the date of the last log entry

                              $lastLine = Get-Content ($dir + "\" + $Script:vm.Name + "\" + "vmware.log") -Tail 1

                              [datetime]$LastLogDate = $lastLine.Split("|")[0]

                              $vmProperty.Add("Date of last Log entry",$LastLogDate)

                          

                              # determine number of days VM has been powered off

                              $today = Get-Date

                              $daysOff = New-TimeSpan -Start $today -End $LastLogDate

                              $absoluteDaysOff = [math]::Abs($daysOff.Days)

                              $vmProperty.Add("Powered Off for (Days)",$absoluteDaysOff)

                       

                              # add the CI Status to the results

                              Get-CIStatus

                       

                              # Delete the Decommissioned VCs from VCSA

                              Remove-Decom

                       

                              Remove-PSDrive -Name $drvName -Confirm:$false | Out-Null

                              $Script:decomReport += New-Object -TypeName psobject -Property $vmProperty

                              $i++

                          }

                       

                          $Script:decomReport |

                              Sort-Object -Property 'VM Name' |

                               Export-Excel @Script:excelHash

                           

                      }

                       

                      Install-PowerCLI

                      Login-VCSA

                      Create-Directory

                      Create-File

                      Script:Prepare-Excel

                      Get-Details

                       

                      The $Script:decomReport array never gets updated with the following

                      1) $Script:cmdbQueryResultStatus

                      2) $timestamp

                      3) $viServerUser

                       

                      Is there a way to do this ?

                       

                      my output when the above is run against a powered on VM is

                       

                      Invoke-Sqlcmd : Invalid column name 'TestVM1'.

                      Msg 207, Level 16, State 1, Procedure , Line 3.

                      At D:\Documents\Powershell SCRIPTS\Remove-Vm (29062020)-2.ps1:105 char:24

                      + ... eryResult = Invoke-Sqlcmd -Query $cmdbQuery -ServerInstance $cmdbServ ...

                      +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                          + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException

                          + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

                       

                       

                      Invoke-Sqlcmd : Invalid column name 'TestVM1'.

                      Msg 207, Level 16, State 1, Procedure , Line 3.

                      At D:\Documents\Powershell SCRIPTS\Remove-Vm (29062020)-2.ps1:105 char:24

                      + ... eryResult = Invoke-Sqlcmd -Query $cmdbQuery -ServerInstance $cmdbServ ...

                      +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                          + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException

                          + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

                       

                       

                      TestVM1 does NOT meet deletion criteria. TestVM1 will NOT be deleted from disk

                      • 8. Re: Delete VM's and output results - PART 2
                        LucD Guru
                        vExpertUser ModeratorsCommunity Warriors

                        I'm not that versed in SQL, but isn't that Invoke-SqlCmd error caused by missing quotes around the value?

                        $cmdbQuery = "USE $cmdbDatabase

                         

                                SELECT * FROM [myDatabase].[dbo].CMDB_CI

                                WHERE Name='$vmName'"


                        Since you are using the ordered hash table $vmProperty in multiple functions, you will have to make that a variable in the Script scope as well.

                        Replace all $vmProperty occurrences with $script:vmProperty, and give it another go.

                        Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz