<#Created by Gidon Marcus, May 2018. to consume the NetApp OCUM events and process them tested win OCUM 7.2,7.3 windows 2012R2,2016, ontap 9.1P6. My SQL server 5.7, mysql-connector-net-6.10.5.msi (last one only needed if you enable some of the script functionality) To implement it as-is you need to edit the variables $Emailto and $SMTPServer. upload it to OCUM in https://server_name/admin/scripts and assign it to alert in https://server_name/#/alerting Note that the very large region "Pulling Quota information from the OCUM DB" is not necessary for the rest of the functionality but will also not run if you don't configure this EMS. if you don't need it - feel free to delete it. #> #region consume all OCUM alert parameters into a hashtable #create hashtable, Get rid of chars that break the script, append any word that start with " -" to a hashtable index with the input following it until the next " -" as a value. $parms=@{};" "+($args -replace "\]|\[|@"," " -join " ") -split " -"|?{$_}|%{$t=$_ -split " ",2;$parms.($t[0])=$t[1]} #run on $parms.eventArgs and append the first word before a "=" as a eventArgs_* index, and as the value any words after it - until the next word that come before "=". ($parms.eventArgs -split " "|%{if($_ -match "="){".cut."}$_}) -join " " -split ".cut. "|?{$_}|%{$t=$_ -split "=";$parms.("eventArgs_"+$t[0])=$t[1]} #the next line output to a folder for debug, enable only while testing #$parms | Format-List > c:\temp\parms_$($parms.EventID)_$(Get-date -Format "ddMMyyyy_HHmmss").txt #endregion #region Pulling Quota information from the OCUM DB where i find the information in a specific alert (Quota Threshold breach generated by custom EMS) if ($parms."eventArgs_ems-trigger-condition" -match "quota.softlimit.exceeded: Threshold exceeded for" -and $parms.eventState -eq "NEW") { <# ActionNeeded: This is a custom EMS event that does not exists in OCUM by default. you can set it in OCUM using the "configuration" > "Manage events" > "Subscribe to EMS Events" and add "quota.softlimit.exceeded" ActionNeeded: requires http://dev.mysql.com/downloads/connector/net/ (tested with version 6.10.5) ActionNeeded: the user created from the with the commands: "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u root -p CREATE USER 'Read_OCUMdb_For_Event_Scripts'@'%' IDENTIFIED BY 'MyOtherSecretPassword!1'; GRANT SELECT ON *.* TO 'Read_OCUMdb_For_Event_Scripts'@'%'; #> Function Convert-BytesToSize {[CmdletBinding()] Param([parameter(Mandatory=$False,Position=0)][int64]$Size) Switch ($Size){ {$Size -gt 1TB}{ $NewSize = “$([math]::Round(($Size / 1TB),2))TB” Break} {$Size -gt 1GB}{ $NewSize = “$([math]::Round(($Size / 1GB),2))GB” Break} Default{ $NewSize = “$([math]::Round(($Size / 1MB),2))MB” Break} }Return $NewSize } Function Invoke-MySQLQuery{ [CmdletBinding()] Param( [Parameter(Position=0,Mandatory=$True,ValueFromPipeLine=$True,ValueFromPipeLineByPropertyName=$True)] [String]$Query,[Parameter(Position=1,Mandatory=$True,ValueFromPipeLine=$True,ValueFromPipeLineByPropertyName=$True)] [String]$ConnectionString ) $result = "" [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") $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") $result = $dataSet.Tables[0] $connection.Close() Return $result; } [String]$VolumeName = $parms.eventArgs_volname -replace " " [String]$SVMUUID = $parms.eventArgs_volident -replace " |vserver:" [int]$QtreeNumber = $Parms.eventArgs_whom -replace " |tree " [String]$FormatedVolAndQtreeName = "%.$QtreeNumber.TREE./vol/$VolumeName/%" [String]$userName = "Read_OCUMdb_For_Event_Scripts" [String]$password = "MyOtherSecretPassword!1" [String]$database = "ocum_report" [String]$hostName = "localhost" [String]$portNumber = 3306 [String]$connectionString = "server=$hostName;port=$portNumber;uid=$userName;pwd=$password;database=$database" [String]$query = "select quotaTarget,diskUsed,diskLimit,softDiskLimit,threshold,svm.name AS SVMName,cluster.name AS ClusterName from qtreequota inner join svm on svm.clusterid = qtreequota.clusterid AND svm.id = qtreequota.svmid inner join cluster on cluster.id = svm.clusterid where svm.uuid = '$SVMUUID' AND qtreequota.uuid LIKE '$FormatedVolAndQtreeName'" $results = Invoke-MySQLQuery -Query $query -ConnectionString $connectionString $EmailSubject = "Quota breached first space threshold on $($results.ClusterName):$($results.quotaTarget) (Reported by $ENV:COMPUTERNAME)" $EmailBody = @" Quota breached first space threshold , Quota Target name: $($results.quotaTarget), Volume name: $VolumeName, Cluster name: $($results.ClusterName), SVM name: $($results.SVMName) Current use (output can be in up to 30 minutes delay): $(Convert-BytesToSize ($results.diskUsed*1024)) Threshold (Genereates this Email): $(Convert-BytesToSize ($results.threshold*1024)) , Soft limit: $(Convert-BytesToSize ($results.softDiskLimit*1024)), Hard limit (blocks writes): $(Convert-BytesToSize ($results.diskLimit*1024)) You can increase it in: https://$($results.ClusterName)/sysmgr/SysMgr.html#quota&svm=$($results.SVMName) Search for target: $($results.quotaTarget) . and increase the quota per needed. Keep the standard of soft_limit equal to hard_limit * 0.9, and threshold is hard_limit * 0.8. Confirm the dialog asking if it is OK to resize the quota. Regards The storage admin "@ } #endregion #region if the alert is new and no email was not orchasrated yet - orchastreat a generic one. if (!$EmailSubject -and !$EmailBody -and $parms.eventState -eq "NEW") { $EmailSubject = $parms.eventState+" "+$parms.eventSeverity+" event:"+$parms.eventName+" reported on: "+$parms.eventSourceName $EmailBody = @" $EmailSubject The full event Details below $($parms | FL | out-string) Regards The storage admin "@ } #endregion #region send the email if subject is set if ($EmailSubject) { $Emailto = "g@g.local" $SMTPServer = "smtp.g.local" $FROMAddress = "External_Event_Handling_$($parms.eventSeverity)@$ENV:COMPUTERNAME.local" Send-MailMessage -SmtpServer $SMTPServer -FROM $FROMAddress -to $Emailto -Subject $([string]$EmailSubject) -Body $([string]$EmailBody) -ErrorVariable errorwithemail if ($errorwithemail) { $errorwithemail > c:\installs\Error_Sending_Email_$(Get-date -Format "ddMMyyyy_HHmmss").txt } } #endregion #region if no error occurred and the event in OCUM is new assign it to a dedicated user if ($parms.eventState -eq "NEW" -and !$errorwithemail) { <# #ActionNeeded: For this to work you have to create a dedicated operator user in OCUM, remove comment <> when done... um cli login -u External_Event_Handling -p VerySecretPassword1! um event ack $parms.eventID um event assign -u External_Event_Handling $parms.eventID #> } #endregion