############################################
# 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()
