Querying OCUM Database using Powershell

Oncommand Unified Manager (OCUM) is the software to monitor and troubleshoot cluster or SVM issues relating to data storage capacity, availability, performance and protection. OCUM polls the clustered Data ONTAP stoage systmes and stores all inventory information in MySQL database. Using powershell we can query MySQL database and retrieve information to create reports.

All we need is MySQL .NET connector to query OCUM database and retrieve information from various tables. Another tool that is helpful is “HeidiSQL” client for MySQL. You can connect to OCUM Database using Heidi SQL and view all the tables and columns within the database.

Download and use version 2.0 MySQL Connector with OCUM 6.2

Donwload link to HeidiSQL

NetApp Communities Post

First of all you’ll need to create a “Database User” with Role “Report Schema” (OCUM GUI -> Administration -> ManagerUsers -> Add)

Use HeidiSQL to connect to OCUM database

Connect-OCUM

OCUM

Ocum_report

Sample Powershell Code to connect to OCUM Database and retrieve information

# Get-cDOTAggrVolReport.ps1
# Date : 2016_03_10 12:12 PM
# This script uses MySQL .net connector at location E:\ssh\MySql.Data.dll to query OCCUM 6.2 database

# Function MySQL queries OCUM database
# usage: MySQL -Query <sql-query>
function MySQL {
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$Query
)

$MySQLAdminUserName = 'reportuser'
$MySQLAdminPassword = 'Netapp123'
$MySQLDatabase = 'ocum_report'
$MySQLHost = '192.168.0.71'
$ConnectionString = "server=" + $MySQLHost + ";port=3306;Integrated Security=False;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase

Try {
[void][System.Reflection.Assembly]::LoadFrom("E:\ssh\MySql.Data.dll")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()

$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]
}

Catch {
Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
}

Finally {
$Connection.Close()
}
}

# Define disk location to store aggregate and volume size reports retrieved from OCUM
$rptdir = "E:\ssh\aggr-vol-space"
$rpt = "E:\ssh\aggr-vol-space"
$filedate = (Get-Date).ToString('yyyyMMdd')
$aggrrptFilename = "aggrSize`_$filedate.csv"
$aggrrptFile = Join-Path $rpt $aggrrptFilename
$volrptFilename = "volSize`_$filedate.csv"
$volrptFile = Join-Path $rpt $volrptFilename

# verify Report directory exists
if ( -not (Test-Path $rptDir) ) {
write-host "Error: Report directory $rptDir does not exist."
exit
}

# Produce aggregate report from OCUM
#$aggrs = MySQL -Query "select aggregate.name as 'Aggregate', aggregate.sizeTotal as 'TotalSize KB', aggregate.sizeUsed as 'UsedSize KB', aggregate.sizeUsedPercent as 'Used %', aggregate.sizeAvail as 'Available KB', aggregate.hasLocalRoot as 'HasRootVolume' from aggregate"
$aggrs = MySQL -Query "select aggregate.name as 'Aggregate', round(aggregate.sizeTotal/Power(1024,3),1) as 'TotalSize GB', round(aggregate.sizeUsed/Power(1024,3),1) as 'UsedSize GB', aggregate.sizeUsedPercent as 'Used %', round(aggregate.sizeAvail/Power(1024,3),1) as 'Available GB', aggregate.hasLocalRoot as 'HasRootVolume' from aggregate"
$aggrs | where {$_.HasRootVolume -eq $False} | export-csv -NoTypeInformation $aggrrptFile

# Produce volume report from OCUM
$vols = MySQL -Query "select volume.name as 'Volume', clusternode.name as 'Nodename', aggregate.name as 'Aggregate', round(volume.size/Power(1024,3),1) as 'TotalSize GB', round(volume.sizeUsed/Power(1024,3),1) as 'UsedSize GB', volume.sizeUsedPercent as 'Used %', round(volume.sizeAvail/Power(1024,3),1) as 'AvaliableSize GB', volume.isSvmRoot as 'isSvmRoot', volume.isLoadSharingMirror as 'isLSMirror' from volume,clusternode,aggregate where clusternode.id = volume.nodeId AND volume.aggregateId = aggregate.id"
$vols | where {$_.isSvmRoot -eq $False -and $_.isLSMirror -eq $False -and $_.Volume -notmatch "vol0$"} | export-csv -NoTypeInformation $volrptFile

Leave a Reply

Your email address will not be published. Required fields are marked *