Get all volumes used space, and increment it in an Excel file automatically with PowerShell

by on ‎2013-01-29 01:19 AM

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

#   by Xavier Bourdeau : cthulhu14@hotmail.com     #

#   Feel free to share, not for any commercial use    #

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

# At least this is not a perfect script, but it shows how to play with Excel, Sheets and DataOnTAP

# It takes about 2 minutes for 20 volumes. Might ran on weekly or daily basis.

# You can see the excel file being modified by uncomenting below Application.Visible=$true

Import-Module DataONTAP # For sure you need to install the NetAPP cmdlet on your computer before importing this

$allNetAPP = @("controller_1","controller_2") #        <--- Put there your NetApp controllers name or IP

$volUnused = @("vol_boot","vol_test") #                <--- Put there the volume you don't want to check

$narootpasswd = "this_is_the_root_password" #          <--- This is you root password

$currentPath = (Get-Variable -Name PWD).Value #        <--- This is your current directory, that should work fine

$excelFileName = "$currentPath\NetAppStatsUsed.xlsx" # <--- You can change the filename

$now = Get-Date -UFormat "%d/%m/%Y %H:%M" #            <--- Depending on your regional settings, you must adapt this one.

$xlDateFormat = "jj/mm/aaa h:mm;@" #                   <--- Same here, you must adapt to your Excel langage

#Storing login & password

$password = ConvertTo-SecureString $narootpasswd -AsPlainText -Force

$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "root",$password

$xlApplication = New-Object -ComObject "Excel.Application"

# For debugging, so you can see the Excel files changin

#$xlApplication.Application.Visible=$true

$xlApplication.Application.Visible=$false

# If the workbook doesn't exist, I create it

if (-not(Test-Path -Path $excelFileName))

    {

    $xlWorkBook=$xlApplication.Workbooks.Add()

    $xlWorkBook.SaveAs($excelFileName)

    # I delete the default sheets...

    while ($xlWorkBook.Sheets.Count -ne 1)

        {

        $xlWorkSheet = $xlWorkBook.Sheets.Item(1)

        $xlWorkSheet.Delete()

        }

    #... and change the last one with name "Stats"

    $xlWorkSheet = $xlWorkBook.Sheets.Item(1)

    $xlWorkSheet.Name = "Stats"

    $xlCells = $xlWorkSheet.Cells

    # i change the number format

    $xlCells.NumberFormat = "# ##0"

    # and change the first row that will containe date with the date format

    $xlRow = $xlWorkSheet.Rows.Item(1)

    $xlRow.EntireRow.NumberFormat = $xlDateFormat

    }

# If the file exit, I open it.

else

    {

    $FindGoodSheet = $false

    $xlWorkBook = $xlApplication.Workbooks.Open($excelFileName)

    # There I read all the sheets names until I find one called "Stats".

    for ($i=1; $i -le $xlWorkBook.Sheets.Count; $i++)

        {

        if ($xlWorkBook.Sheets.Item($i).Name -eq "Stats")

            {

            $FindGoodSheet=$i

            }

        }

    # If FindGoodSheet is still $false, it means that we are not sure to use the right file.

    if ($FindGoodSheet -eq $false)

        {

        Write-Host "I'm not sure to use the right file, I prefere to quit instead of corrupting the wrong file"

        return 0

        }

    else

        {

        $xlWorkSheet = $xlWorkBook.Sheets.Item($FindGoodSheet)

        $xlCells = $xlWorkSheet.Cells

        }

    }

# There I'm looking for the next free Columns

$i=2

while ($xlCells.Item(1,$i).Text -ne "")

    {

    $i++

    }

$FindFreeCol = $i

$xlCells.Item(1,$FindFreeCol) = $now

foreach ($thisNetAPP in $allNetAPP)

    {

    Connect-NaController $thisNetAPP -Credential $cred |Out-null

    $AllVolumes = Get-NaVol

    foreach ($thisVolume in $AllVolumes)

        {

        # If the volume in not blacklisted...

        if ($thisVolume -notin $volUnused)

            {

            # I'm looking at the first colum of everyrow in case the volume is already referenced.

            $FindGoodRow=$false

            $i=2

            while ($xlCells.Item($i,1).Text -ne "")

                {

                if ($xlCells.Item($i,1).Text -eq $thisVolume.Name)

                   {

                   $FindGoodRow=$i

                   }

                $i++

                }

            # If I can't find it, it means I can create a new line

            if ($FindGoodRow -eq $false)

                {

                $xlCells.Item($i,1)=$thisVolume.Name

                $FindGoodRow=$i

                }

            $xlCells.Item($FindGoodRow,$FindFreeCol) = $thisVolume.SizeUsed

            }

        }

    }

#I ajust the columns, Save, Close and Quit nicely

$xlWorkSheet.Columns.AutoFit() | Out-Null 

$xlWorkBook.Save()

$xlWorkBook.Close()

$xlApplication.Quit()

Comments
vinith Former NetApp Employee

good Job.

I get the following error when i try and run this script

       

199 if ($thisVolume -Notin $volUnused)

"you must provide a value expression on the right-hand side of the operator"

I got the script to run by editing the following line     199. However now i get a different error "Exception setting "numberformat" unable to set the numberformat property of the range class"

if ("$thisVolume -notin $volUnused")

As I said in the variable declaration, you MUST adapt those two line to your own regional settings

$now = Get-Date -UFormat "%d/%m/%Y %H:%M" #            <--- Depending on your regional settings, you must adapt this one.

$xlDateFormat = "jj/mm/aaa h:mm;@" #                   <--- Same here, you must adapt to your Excel language

That's the french version. I guess in English version it should be like

$now = Get-Date -UFormat "%m/%d/%Y %H:%M"

$xlDateFormat = "mm/dd/yyyy h:mm;@"

If you're not sure, go to Excel, change a cell format, and check the cell format in "custom" categories

That because you are using PowerShell 1.0 or 2.0.

You may upgrade to PowerShell 3.0 which is in Windows Management Framework 3.0 : http://www.microsoft.com/en-us/download/details.aspx?id=34595

If you upgrade, you'll notice that the ISE is much more user friendly, and you'll have more conditional commands etc....

The bad news is that you must have a Windows 6.0 or 6.1 (use winver.exe to check that)

If I remember well, in case you cannot upgrade, you can replace :

if ($thisVolume -Notin $volUnused)

by

if (-not($volUnused -in $thisVolume)

I am running powershell 3.0. I am running this script on a windows 7 sp1 box version 6.1

PS H:\> Get-PSSnapin


Name        : Microsoft.PowerShell.Core
PSVersion   : 3.0
Description : This Windows PowerShell snap-in contains cmdlets used to manage components of Windows PowerShell.

Name        : Quest.ActiveRoles.ADManagement
PSVersion   : 1.0
Description : This Windows PowerShell snap-in contains cmdlets to manage Active Directory and Quest ActiveRoles Server.

So upgrade your Windows Management Framework to version 3.0 : http://www.microsoft.com/en-us/download/details.aspx?id=34595

The " -notin " condition is a quite new functionality.

By the way, $volUnused must be an array

i am running WMF 3.0

PS H:\> get-host


Name             : ConsoleHost
Version          : 3.0
InstanceId       : 01e073b4-24f0-4d2f-854c-d7f0bdc46e0f
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-US
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

Send me you code by mail. I'll check what's wrong : cthulhu14@hotmail.com

email is on its way, thank you!

if ("$thisVolume -Notin $volUnused") must not have comma, replace it by if ($thisVolume -Notin $volUnused)

I tried this. When i remove the quote, i get error

"you must provide a value expression on the right-hand side of the operator"

if ("$thisVolume -Notin $volUnused") must not have comma, replace it by if ($thisVolume -Notin $volUnused)

The script does work, even though i was getting the below error in my script editor, when i run the script it runs without error. Now to make this script very usefule it would be great to run it as a scheduled task.

"you must provide a value expression on the right-hand side of the operator"

Warning!

This NetApp Community is public and open website that is indexed by search engines such as Google. Participation in the NetApp Community is voluntary. All content posted on the NetApp Community is publicly viewable and available. This includes the rich text editor which is not encrypted for https.

In accordance to our Code of Conduct and Community Terms of Use DO NOT post or attach the following:

  • Software files (compressed or uncompressed)
  • Files that require an End User License Agreement (EULA)
  • Confidential information
  • Personal data you do not want publicly available
  • Another’s personally identifiable information
  • Copyrighted materials without the permission of the copyright owner

Files and content that do not abide by the Community Terms of Use or Code of Conduct will be removed. Continued non-compliance may result in NetApp Community account restrictions or termination.