Zebi-Utilization-Tracker

4.21

Zebi-Utilization-Tracker.ps1

                                <#PSScriptInfo

.VERSION 4.21

.GUID 1611dfc8-091d-426e-8e52-82aff670ff62

.AUTHOR Kenneth C. Mazie (kcmjr AT kcmjr.com)

.COMPANYNAME 

.COPYRIGHT 

.TAGS 

.LICENSEURI 

.PROJECTURI 

.ICONURI 

.EXTERNALMODULEDEPENDENCIES 

.REQUIREDSCRIPTS 

.EXTERNALSCRIPTDEPENDENCIES 

.RELEASENOTES

.PRIVATEDATA 

#>

<# 

.DESCRIPTION 

This script will query multiple Tegile Zebi Controllers, (or any Linux host) and run the "df -h" command capturing the

results and parsing them into a spreadsheet.  Each tab is a different day.  Each tab is a list of all target controllers.

A new spreadsheet is created each month.  Multiple runs in the same day over-write the existing data.

#> 

Param (

      [switch]$Debug = $false,

      [switch]$Console = $false

)

<#==============================================================================

         File Name : Zebi-Utilization-Tracker.ps1

   Original Author : Kenneth C. Mazie (kcmjr AT kcmjr.com)

                   :

       Description : This script will query multiple Tegile Zebi Controllers,

                   : (or any Linux host) and run the "df -h" command capturing the

                   : results and parsing them into a spreadsheet.  Each tab is a 

                   : different day.  Each tab is a list of all target controllers.

                   : A new spreadsheet is created each month.  Multiple runs in

                   : the same day over-write the existing data.

                   :

         Arguments : Named commandline parameters:  (all are optional)

                   :   "-console" - Displays console output during run. 

                   :   "-debug" - Switches email recipient and some criteria.

                   :

             Notes : Settings are loaded from an XML file located in the script folder.

                   : See the end of the script for config file example. 

                   :

      Requirements : REQUIRES Posh-SSH v1.7.6

                    : Requires Powershell v5

                   :  

          Warnings : None.  All operations are read only.

                   :

             Legal : Public Domain. Modify and redistribute freely. No rights reserved.

                   : SCRIPT PROVIDED "AS IS" WITHOUT WARRANTIES OR GUARANTEES OF

                   : ANY KIND. USE AT YOUR OWN RISK. NO TECHNICAL SUPPORT PROVIDED.

                   :

           Credits : Code snippets and/or ideas came from many sources around the web.

                   :

    Last Update by : Kenneth C. Mazie (email kcmjr AT kcmjr.com for comments or to report bugs)

   Version History : v1.0 - 09-12-16 - Original

    Change History : v2.0 - 09-27-16 - Major rewrite.  Reordered tabs.  Added free and used tabs

                   :     and moved them to the left.  Converted data from whatever to GB.

                   :

#===============================================================================#>

#requires -version 5.0

clear-host

If (!(Get-Module Posh-SSH)){Import-Module "Posh-SSH" -ErrorAction SilentlyContinue}

If ($Debug){$Script:Debug = $true}

If ($Console){$Script:Console = $true}

$ErrorActionPreference = "stop" #silentlycontinue"

$Script:DateToday = Get-Date -Format "MM-dd-yyyy"

$Script:ThisMonth = ((Get-Date -Format y) -replace (', ', '-')) -replace (' ','-')

$Script:ThisDay = (Get-Date -Format f).Split(",")[0]

$Script:Targets = @()

$Script:PoolList = @()

#--[ Excel Non-Interactive Fix ]------------------------------------------------

If (!(Test-path -Path "C:\Windows\System32\config\systemprofile\Desktop")){New-Item -Type Directory -Name "C:\Windows\System32\config\systemprofile\Desktop" -force}

If (!(Test-path -Path "C:\Windows\SysWOW64\config\systemprofile\Desktop")){New-Item -Type Directory -Name "C:\Windows\SysWOW64\config\systemprofile\Desktop" -force}

#--[ Excel will crash when run non-interactively via a scheduled task if these folders don't exist ]--

#--[ Functions ]----------------------------------------------------------------

Function SendEmail {

Write-Host $Script:EnableEmail -ForegroundColor Cyan 

    If ($Script:Debug){$ErrorActionPreference = "stop"}

    If ($Script:EnableEmail){

    Write-Host "sending email" -ForegroundColor Green 

            $email = New-Object System.Net.Mail.MailMessage

            $email.From = $Script:EmailFrom

            $email.IsBodyHtml = $Script:EmailHTML

            $email.To.Add($Script:EmailTo)

            $email.Subject = $Script:EmailSubject

            $email.Body = "Attached is the weekly Zebi utilization report."

        If ($Script:EmailAttach){

            $email.Attachments.Add("$PSScriptRoot\$Script:ThisMonth.xlsx")

        }    

        $smtp = new-object Net.Mail.SmtpClient($Script:SmtpServer)

        $smtp.Send($email)

        If ($Script:Console){Write-Host "`nEmail sent...`n"}

    }    

}

Function Convert ($Incomming){    #--[ Convert TG and MB to GB ]--

    $Multiplier = $Incomming.substring($Incomming.length - 1, 1)

    $Number = $Incomming -replace ".$" 

    If($Multiplier -eq "T"){$Script:Converted = [int]$Number*1024}

    If($Multiplier -eq "G"){$Script:Converted = [int]$Number*1024}

    If($Multiplier -eq "M"){$Script:Converted = [int]$Number/1024}

    Return $Outgoing

}

#--[ Read and load configuration file ]-----------------------------------------

If (!(Test-Path "$PSScriptRoot\Configuration.xml")){                            #--[ Error out if configuration file doesn't exist ]--

    Write-host "MISSING CONFIG FILE.  Script aborted." -ForegroundColor red

    break

}Else{

    [xml]$Script:Configuration = Get-Content "$PSScriptRoot\Configuration.xml"  #--[ Load configuration ]--

    $Script:EnableEmail = $Script:Configuration.Settings.Email.Enable

    $Script:DebugEmail = $Script:Configuration.Settings.Email.Debug 

    $Script:EmailTo = $Script:Configuration.Settings.Email.To

    $Script:EmailHTML = $Script:Configuration.Settings.Email.HTML

    $Script:EmailSubject = $Script:Configuration.Settings.Email.Subject

    $Script:EmailFrom = $Script:Configuration.Settings.Email.From

    $Script:EmailAttach = $Script:Configuration.Settings.Email.Attach 

    $Script:SmtpServer = $Script:Configuration.Settings.Email.SmtpServer

    $Script:UserName = $Script:Configuration.Settings.Credentials.Username

    $Script:Password = $Script:Configuration.Settings.Credentials.Password

    [array]$Script:Targets = $Script:Configuration.Settings.General.Targets

    $Script:WeeksBack = $Script:Configuration.Settings.General.Weeks

    [array]$Script:PoolList = $Script:Configuration.Settings.General.Pools

}    

#-------------------------------------------------------------------------------

$MissingType = [System.Type]::Missing

$Excel = New-Object -ComObject Excel.Application 

If ($Script:Console -or $Script:Debug){

    $Excel.visible = $True

    $Excel.DisplayAlerts = $true

    $Excel.ScreenUpdating = $true

    $Excel.UserControl = $true

    $Excel.Interactive = $true

}Else{

    $Excel.visible = $False

    $Excel.DisplayAlerts = $false 

    $Excel.ScreenUpdating = $false 

    $Excel.UserControl = $false

    $Excel.Interactive = $false

}

If (Test-Path "$PSScriptRoot\$Script:ThisMonth.xlsx"){ 

    Rename-Item -Path "$PSScriptRoot\$Script:ThisMonth.xlsx" -NewName "$PSScriptRoot\$Script:ThisMonth-old.xlsx"

    $Workbook = $Excel.Workbooks.Open("$PSScriptRoot\$Script:ThisMonth-old.xlsx")

}Else{

    $Workbook = $Excel.Workbooks.Add()

    $Worksheet = $Workbook.Worksheets.Add()

    foreach ($Worksheet in $Workbook.Worksheets){

        If ($Worksheet.Name -eq "Sheet1"){

            $Workbook.Worksheets.item("Sheet1").Activate() 

            $Worksheet.name = "Used"

        }

        If ($Worksheet.Name -eq "Sheet2"){

            $Workbook.Worksheets.item("Sheet2").Activate() 

            $Worksheet.name = "Free"

        }

    }

}

$ReRun = $false

ForEach ($Worksheet in $Workbook.Worksheets){If ($Worksheet.Name -eq $DateToday){$ReRun = $true}}

If ($ReRun){   #--[ Already run today ]--

    if ($Console){Write-host "-- Re-Running Todays Data -- " -ForegroundColor Yellow } 

    $Worksheet = $workbook.Sheets.Item($DateToday)

    $Worksheet.Activate()        

}Else{ 

    $Workbook.Worksheets.add([System.Reflection.Missing]::Value,$Workbook.worksheets.Item($Workbook.worksheets.count))

    ForEach ($Worksheet in $Workbook.Worksheets){

        If ($Worksheet.Name -like "*Sheet*"){$Worksheet.name = $DateToday}

    }    

}

$TargetCount = $Script:Targets.Target.count

$Row = 1

$Col = 1

$WorkSheet.Cells.Item($Row,$Col) = "Controller:"

$WorkSheet.Cells.Item($Row,$Col).font.bold = $true

$WorkSheet.Cells.Item($Row,$Col).HorizontalAlignment = 1

$Col++

$WorkSheet.Cells.Item($Row,$Col) = "Filesystem:"

$WorkSheet.Cells.Item($Row,$Col).font.bold = $true

$WorkSheet.Cells.Item($Row,$Col).HorizontalAlignment = 1

$Col++

$WorkSheet.Cells.Item($Row,$Col) = "Size GB:"

$WorkSheet.Cells.Item($Row,$Col).font.bold = $true

$WorkSheet.Cells.Item($Row,$Col).HorizontalAlignment = 1

$Col++    

$WorkSheet.Cells.Item($Row,$Col) = "Used GB:"

$WorkSheet.Cells.Item($Row,$Col).font.bold = $true

$WorkSheet.Cells.Item($Row,$Col).HorizontalAlignment = 1

$Col++

$WorkSheet.Cells.Item($Row,$Col) = "Available GB:"

$WorkSheet.Cells.Item($Row,$Col).font.bold = $true

$WorkSheet.Cells.Item($Row,$Col).HorizontalAlignment = 1

$Col++

$WorkSheet.Cells.Item($Row,$Col) = "Capacity:"

$WorkSheet.Cells.Item($Row,$Col).font.bold = $true

$WorkSheet.Cells.Item($Row,$Col).HorizontalAlignment = 1

$Col++

$WorkSheet.Cells.Item($Row,$Col) = "Mount Point:"

$WorkSheet.Cells.Item($Row,$Col).font.bold = $true

$WorkSheet.Cells.Item($Row,$Col).HorizontalAlignment = 1

$Col++    

$WorkSheet.application.activewindow.splitcolumn = 0

$WorkSheet.application.activewindow.splitrow = 1

$WorkSheet.application.activewindow.freezepanes = $true

$Resize = $WorkSheet.UsedRange

[void]$Resize.EntireColumn.AutoFit()

$Target = @()

$Row = 2

Foreach ($Target in $Script:Targets.Target){

    if ($Console){Write-Host "`n--[ Processing Target: $Target ]-----------------------------------" -ForegroundColor Yellow }

    $Count = 1     

    $Cmd = 'df -h'             #--[ The command to run over SSH ]--

    #--[ Some optional other commands that could be used ]--

    #   '/usr/sbin/zpool list'

    #    'cat /var/log/tegile/zebi/zebirep.log | egrep "has completed|has failed"'    #--[ to purge replication logs ]--

    #    'uname -a '

    #    '/usr/sbin/zfs list -o name'

    #    'pwd'

    #    'df -h'

    #    '/usr/sbin/dladm show-link –S'

    #    '/usr/sbin/fmadm faulty'

    #    '/usr/sbin/zpool list'

    #    '/usr/sbin/zfs list -o name'

    #    'zfs list -t snapshot'

    #    'zfs list -o space -r hostname$/Local'

    #     'zpool iostat 2'                                             #--[ Display ZFS I/O statistics every 2 seconds ]-- 

    #     'zpool iostat -v 2'                                              #--[ Display detailed ZFS I/O statistics every 2 seconds ]--

    Remove-SSHSession -SessionId 0 -ErrorAction SilentlyContinue | Out-Null     #--[ Clear out previous session if it still exists ]--

    Remove-SSHSession -SessionId 1 -ErrorAction SilentlyContinue | Out-Null     #--[ Clear out previous session if it still exists ]--

    $Script:Converted = ""

    $secpasswd = ConvertTo-SecureString $Script:Password -AsPlainText -Force

    $mycreds = New-Object System.Management.Automation.PSCredential ($Script:UserName, $secpasswd)

    $SSH = New-SshSession -ComputerName $Target -Credential $mycreds -AcceptKey:$true # | Out-Null  #--[ Open new SSH session ]--

    $Script:Converted = $(Invoke-SSHCommand -SSHSession $SSH -Command $Cmd ).Output      #--[ Invoke SSH command and capture the output as a string ]--

    $Col = 1

    $Counter = 1

    ForEach ($Line in $Script:Converted ){

    $Line = $Line -Replace ('\s+', ' ')

    $Line = $Line -Split " "

    if ($Console){Write-Host "   Current input line :"$Line -ForegroundColor cyan }

    if ($Console){write-host "   Input pool list    :"$Script:PoolList.Pool -ForegroundColor Red }

    if ($Console){write-host "   Match criteria     :"$Line.split("/")[0] -ForegroundColor Magenta} 

    if ($Script:PoolList.Pool -contains($Line.split("/")[0]) -and ($Line.split(" ")[5].split("/")[1] -Like "export" )){

        if ($Console){Write-Host "   -- Match found, using line. --" -ForegroundColor yellow}

        if ($Counter -gt 1){                     #--[ Dump collected data into spreadsheet ]--

            #--[ Controller ]-------------------------------------------------------            

            $WorkSheet.Cells.Item($Row,$Col) = "$Script:Target"

            $Col++

            #--[ FileSystem ]-------------------------------------------------------        

            $WorkSheet.Cells.Item($Row,$Col) = $Line[0]

            $Col++        

            #--[ Size ]-------------------------------------------------------------

            $Return = Convert $Line[1]

            $WorkSheet.Cells.Item($Row,$Col) = $Script:Converted

            #$WorkSheet.Cells.Item($Row,$Col) = $Line[1]

            $Col++

            #--[ Used ]-------------------------------------------------------------

            $Return = Convert $Line[2]

            $WorkSheet.Cells.Item($Row,$Col) = $Script:Converted

            #$WorkSheet.Cells.Item($Row,$Col) = $Line[2]

            $Col++

            #--[ Available ]--------------------------------------------------------

            $Return = Convert $Line[3]

            $WorkSheet.Cells.Item($Row,$Col) = $Script:Converted

            #$WorkSheet.Cells.Item($Row,$Col) = $Line[3]

            $Col++

            #--[ Capacity ]---------------------------------------------------------

            $WorkSheet.Cells.Item($Row,$Col) = $Line[4]

            $Col++

            #--[ MountPoint ]-------------------------------------------------------        

            $WorkSheet.Cells.Item($Row,$Col) = $Line[5]

            $Col++

            sleep -milliseconds 100

            $Line = ""

            $RepJob = ""

            $Status = ""

            $Col = 1

            $Resize = $WorkSheet.UsedRange

            [void]$Resize.EntireColumn.AutoFit()

        }

        $Row++

    }Else{

        if ($Console){Write-Host "   -- No Match found. --" -ForegroundColor DarkGray }

    }

    $Counter ++    

    }

Remove-SSHSession -SessionId 0 -ErrorAction SilentlyContinue | Out-Null 

Remove-SSHSession -SessionId 1 -ErrorAction SilentlyContinue | Out-Null 

}

#--[ Populate FREE and USED tabs ]-------------------------------------

$Col = ((Get-Date).Day)+1   

$Row = 1

#--[ Copy Filesystem Names  ]---

$Worksheet0 = $workbook.WorkSheets.Item($DateToday)

$Range0A = $WorkSheet0.Range("B1").EntireColumn()

#--[ Paste Filesystem Names to USED ]---

$Range0A.Copy()

$Worksheet1 = $Workbook.Worksheets.Item("Used")

$Range1A = $WorkSheet1.cells.Item(1,1)

$Worksheet1.Paste($Range1A)

#$WorkSheet1.cells.Item($Row,$Col) = $Col

#--[ Copy Todays Data, Paste to USED ]--

$Range1B = $WorkSheet0.Range("D2").EntireColumn()

$Range1B.Copy()

$Range1B = $WorkSheet1.cells.Item($Row,$Col)

$Worksheet1.Paste($Range1B)

$WorkSheet1.cells.Item($Row,$Col) = ($Col-1)

#--[ Paste Filesystem Names to FREE ]---

$Range0A.Copy()

$Worksheet2 = $Workbook.Worksheets.Item("Free")

$Range2A = $WorkSheet2.cells.Item(1,1)

$Worksheet2.Paste($Range2A)

#$WorkSheet2.cells.Item($Row,$Col) = $Col

#--[ Copy Todays Data, Paste to FREE ]--

$Range2B = $WorkSheet0.Range("E2").EntireColumn()

$Range2B.Copy()

$Range2B = $WorkSheet2.cells.Item($Row,$Col)

$Worksheet2.Paste($Range2B)

$WorkSheet2.cells.Item($Row,$Col) = ($Col-1)

#--[ Save and Quit ]--

$Workbook.SaveAs("$PSScriptRoot\$Script:ThisMonth.xlsx")

$Excel.Quit()

$Excel = $Null

If (Test-Path "$PSScriptRoot\$Script:ThisMonth-old.xlsx"){Remove-Item -Path "$PSScriptRoot\$Script:ThisMonth-old.xlsx" -Confirm:$false -Force:$true}

Sleep -Seconds 2

#If ($Script:ThisDay -eq "Monday"){

SendEmail #}    #--[ Email report on Sundays ]--

[gc]::Collect() | Out-Null 

[gc]::WaitForPendingFinalizers() | Out-Null 

if ($Console){Write-Host "--- Completed ---" -ForegroundColor Red }

<#-----------------------------[ Config File ]---------------------------------

The configuration file must be named "Configuration.xml" and must reside in

the same folder as the script.  Below is the format and element list:

<!-- Settings & Configuration File -->

<Settings>

    <General>

        <ScriptName>SAN-Utilization-Tracker</ScriptName>

        <DebugTarget>test-server</DebugTarget>

        <Targets>

            <Target>10.100.1.1</Target>

            <Target>10.100.1.2</Target>

            <Target>10.100.1.3</Target>

            <Target>10.100.1.4</Target>

            <Target>10.100.1.5</Target>

            <Target>10.100.1.6</Target>

            <Target>10.100.1.7</Target>

            <Target>10.100.1.8</Target>

        </Targets>

        <Pools>

            <Pool>Pool1</Pool>

            <Pool>Pool2</Pool>

        <Pools>

        <Weeks>2</Weeks>

    </General>

    <Email>

        <Enable>$true</Enable>

        <From>SANReports@mydomain.com</From>

        <To>me@mydomain.com</To>

        <Subject>Zebi Utilization Status Report</Subject>

        <HTML>$true</HTML>

        <Attach>$true</Attach>

        <SmtpServer>10.10.1.10</SmtpServer>

    </Email>

    <Credentials>

        <UserName>zebiadminuser</UserName>

        <Password>zebiadminpwd</Password>

    </Credentials>

</Settings>    

#>