seeking config example for FSLU datasource from Excel

[stephen2]

IHAC that wants to acquire file system data from a daily excel spreadsheet which is placed on the OCI server. The help files and FSLU.xml show examples from how to acquire from a database. Does anyone have an example of how to acquire from an Excel spreadsheet?

[ostiguy]

This datasource doesn't work this way.

The FSLU datasource is a bit different from traditional OCI data flow - OCI datasources pull data via a poll mechanism. This one does as well, but it is pulling data from a fixed set of database tables within the OCI database that customers have write ( !!!) access to - which is in itself unusual, because the OCI operational database is basically a blackbox for which there is no access to.

These specific tables + schema are fixed for customers to publish FSLU data into, from whatever source they want.

The FSLU datasource has very few options, because it is pulling data from database tables where the schema is fixed.

This datasource requires that customers own the responsibility for scriptomatically / programmatically pulling FSLU data out of another application, and inserting it into the FSLU tables - this responsibility may not be something storage teams will want to undertake.

[moechnig]

I've been asking around for example scripts to populate the FSLU database.  So far I haven't found anyone willing to share.  If I write some, I will post them internally.  Stephen2, if you come across some good examples please let me know. 

[stephen2 · Accepted solution]

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)