here is a powershell script that will read from an excel file and inpit into the fslu database. It runs slow and needs to be re-engineered, but it works. Usage details in the code. Need to replace OCI server address, path to excel spreadsheet, and worksheet number for your environment. Also need some powershell mods.
#excel fsutil script - reads an excel spreadsheet and populates the host_data database in preperation for FSLU acquisition
# assumes an input file with the following format: hostname, ipaddress, file system, type, allocated, used, mount point
# requires mysql powershell driver - mysql-connector-net-6.8.3 - search, download, and install
# runs slow - need to convert to ADE Excel driver to speed up
# SG 1/10/14
# MySQL functions
function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database)
{
# Load MySQL .NET Connector Objects
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
# Open Connection
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
return $conn
}
function WriteMySQLQuery($conn, [string]$query)
{
$command = $conn.CreateCommand()
$command.CommandText = $query
$RowsInserted = $command.ExecuteNonQuery()
$command.Dispose()
if ($RowsInserted) {
return $RowInserted }
else { return $false
}
}
# clean up function
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# setup vars for OCI DB connection
$user = 'hosts'
$pass = 'netapp123'
$database = 'host_data'
############################################ change address of OCI host here ##################################################
$MySQLHost = '192.168.77.55'
###############################################################################################################################
# Connect to MySQL Database
$conn = ConnectMySQL $user $pass $MySQLHost $database
# create journal entry
$query = 'INSERT INTO journal (identifier, status) VALUES ("localscript","SUCCESS") ON DUPLICATE KEY UPDATE journal_update_timestamp = CURRENT_TIMESTAMP'
$Rows = WriteMySQLQuery $conn $query
# Creating excel object
$objExcel = new-object -comobject excel.application
$objExcel.Visible = $Tru
############################################ change excel file name / location here ###########################################
# Directory location where we have our excel files
$ExcelFilesLocation = "c:\powershell\"
# Open our excel file
$UserWorkBook = $objExcel.Workbooks.Open($ExcelFilesLocation + "wdc.xlsx")
$UserWorksheet = $UserWorkBook.Worksheets.Item(1)
###############################################################################################################################
# starting at row 2 to skip header data
$intRow = 2
# for each row in spreadsheet, read cells and write to OCI DB
Do {
# read current row
$HostName = $UserWorksheet.Cells.Item($intRow, 1).Value()
$IPaddress = $UserWorksheet.Cells.Item($intRow, 2).Value()
$Filesystem = $UserWorksheet.Cells.Item($intRow, 3).Value()
$type = $UserWorksheet.Cells.Item($intRow, 4).Value()
$allocated = $UserWorksheet.Cells.Item($intRow, 5).Value()
$used = $UserWorksheet.Cells.Item($intRow, 6).Value()
$mountpoint = $UserWorksheet.Cells.Item($intRow, 7).Value()
# print values
"Row # " + $intRow
"host name " + $HostName
"ip address " + $IPaddress
"file system " + $Filesystem
"type" + $type
"allocated " + $allocated
"used " + $used
"mountpoint " + $mountpoint
" "
# insert into OCI DB
$query = "INSERT INTO HOST(hostname, journal_identifier) VALUES ('$HostName', 'localscript') ON DUPLICATE KEY UPDATE host_update_timestamp = CURRENT_TIMESTAMP, journal_identifier = 'localscript'"
$Rows = WriteMySQLQuery $conn $query
$query = "INSERT IGNORE INTO ip(ip, host_identifier) VALUES ('$IPaddress', '$HostName') "
$Rows = WriteMySQLQuery $conn $query
$query = "INSERT INTO file_system(mount_name, host_identifier,
allocated_capacity_mb,used_capacity_mb, fs_type, location_type) VALUES ('$Filesystem', '$HostName', '$allocated', '$used', '$type', 'LOCAL')
ON DUPLICATE KEY UPDATE allocated_capacity_mb = '$allocated', used_capacity_mb = '$used', fs_type = '$type'"
$Rows = WriteMySQLQuery $conn $query
# Move to next row
$intRow++
} While ($UserWorksheet.Cells.Item($intRow,1).Value() -ne $null)
# Exiting the excel object
$objExcel.Quit()
#Release all the objects used above
$a = Release-Ref($UserWorksheet)
$a = Release-Ref($UserWorkBook)
$a = Release-Ref($objExcel)