Get all export policies which are not assigned to volume / qtree

[LUKASPRELOVSKY]

Hello community,

i would like to create a powershell script, which shows me all export policies without volume / qtree assignment.

Anyone able to help me with some usefull code?

Thank you,

Lukas

[mbeattie]

Hi Lukas,

I think an efficent method would be to query the OCUM database based on a the cluster and vserver. Something like...

SELECT
   cluster.NAME AS 'cluster_name',
   vserver.NAME AS 'vserver_name',
   export_policy.NAME AS 'policy_name',
   volume.NAME AS 'volume_name',
   qtree.NAME AS 'qtree_name'
FROM
   netapp_model_view.export_policy,
   netapp_model_view.volume,
   netapp_model_view.qtree,
   netapp_model_view.vserver,
   netapp_model_view.cluster
WHERE
   vserver.clusterId = cluster.objid
AND
   volume.vserverId = vserver.objid
AND
   qtree.volumeId = volume.objid
AND
   qtree.vserverId = vserver.objid
AND
   volume.exportPolicyId = export_policy.objid
AND
   qtree.exportPolicyId = export_policy.objid
AND
   vserver.NAME = 'vserver2'
AND
   cluster.NAME = 'cluster1'

To ensure that gives you the correct results in your environment cross check the results with the CLI output of:

volume show -vserver <%vserver_name%> -fields policy
qtree show -vserver <%vserver_name%> -fields export-policy

Once you've determined the query provide the correct results your looking for it's just a matter of comparing the export policies that exist on the vserver with the ones that are assigned to volumes or qtrees

/Matt

[LUKASPRELOVSKY]

Hello Matt,

thank you for your quick reply. Means it's not easy to realize via Powershell Toolkit CMDlets?

Thank you,

Lukas

[mbeattie · Accepted solution]

Hi Lukas,

It's more efficent to query the OCUM database given the objects are already discovered and the database contains the information you are looking for. You can query the cluster\vserver using powershell if you required. Here is an example:

Param(
   [Parameter(Mandatory=$True, HelpMessage="The Cluster name or IP Address")]
   [String]$Cluster,
   [Parameter(Mandatory=$True, HelpMessage="The vserver name")]
   [String]$VserverName,
   [Parameter(Mandatory=$True, HelpMessage="The credentials to authenticate to the cluster")]
   [System.Management.Automation.PSCredential]$Credentials
)
Try{
   Import-Module DataONTAP -ErrorAction Stop
   Connect-NcController -Name $Cluster -HTTPS -Credential $Credentials -ErrorAction Stop | Out-Null
}Catch{
   Throw $("Failed connecting to cluster ""$Cluster"". Error " + $_.Exception.Message)
}
Try{
   $ep = Get-NcExportPolicy -Vserver $VserverName -ErrorAction Stop
}Catch{
   Throw $("Failed enumerating export policies. Error " + $_.Exception.Message)
}
Try{
   $vols = Get-NcVol -Vserver $VserverName -ErrorAction Stop
}Catch{
   Throw $("Failed enumerating volumes. Error " + $_.Exception.Message)
}
Try{
   $qt = Get-NcQtree -VserverContext $VserverName -ErrorAction Stop | Where-Object {$_.Id -ne 0}
}Catch{
   Throw $("Failed enumerating Qtrees. Error " + $_.Exception.Message)
}
[HashTable]$policies = @{};
[HashTable]$volumes  = @{};
[HashTable]$qtrees   = @{};
[HashTable]$used     = @{};
[HashTable]$unused   = @{};
ForEach($vol In $vols){
   If(-Not($volumes.ContainsKey($vol.name))){
      [HashTable]$volumes.Add($vol.name, $vol.VolumeExportAttributes.Policy)
   }
}
ForEach($q In $qt){
   If(-Not($qtrees.ContainsKey($("/" + $q.Volume + "/" + $q.name)))){
      [HashTable]$qtrees.Add($("/" + $q.Volume + "/" + $q.Qtree), $q.ExportPolicy)
   }
}
ForEach($p In $ep){
   If(-Not($policies.ContainsKey($p.PolicyName))){
      [HashTable]$policies.Add($p.PolicyName, $p.Vserver)
   }
}
ForEach($key In $volumes.Keys){
   If(-Not($used.ContainsKey($volumes[$key]))){
      [HashTable]$used.Add($volumes[$key], "")
   }
}
ForEach($key In $qtrees.Keys){
   If(-Not($used.ContainsKey($qtrees[$key]))){
      [HashTable]$used.Add($qtrees[$key], "")
   }
}
ForEach($key In $policies.Keys){
   If(-Not($used.ContainsKey($key))){
      [HashTable]$unused.Add($key, "")
   }
}
Write-Host "Volumes`:"
$volumes
Write-Host ""
Write-Host "Qtree`:"
Write-Host ""
$qtrees
Write-Host ""
Write-Host "Export Policies`:"
Write-Host ""
$policies
Write-Host ""
Write-Host "Used Export Policies"
Write-Host ""
$used.Keys
Write-Host ""
Write-Host "Unused Export Policies"
Write-Host ""
$unused.Keys

That worked fine in my lab, i tested modifying export policies on volumes and qtrees and the results were what i expected showing the used and unused export policies per vserver.

/Matt

[netappmagic]

@mbeattie ,

Can you please elaborate on how can I query OCUM database?  I have no basic ideas on how I can use OCUM database. Could you please help me where can I start with to learn, or any documents?

This is something I have aleays wanted to know.  Thanks!

[mbeattie]

Hi,

For information on connecting to the OCUM database please see this TR: 

https://www.netapp.com/us/media/tr-4709.pdf

See Section 2.2 on page 4

• Create an OCUM database user
• Download and install an application that enables you to connect to MySQL 
• Connect to MySQL on the OCUM server using the database user credentials

I'd recommend using HeidiSQL for writing queries, it's free, lightweight and easily portable by copying a single .exe file.

You can download it here: https://www.heidisql.com/download.php

Assuming you have WFA in your environment running on Windows then download the x64 nightly build executable and copy it to the "lib" directory (default is 'C:\Program Files\MySQL\MySQL Server 5.7\lib' on your WFA server), send a shortcut of the heidisql executable to the desktop and open the shortcut.  When creating a connection to OCUM use the read-only database user credentials. This should enable you to connect to the OCUM database, browse the database views and write queries

/Matt

[LUKASPRELOVSKY]

Hello @mbeattie 

getting entries from OCUM is one option, but it's not like "realtime".

My solutions is:

# Creating HashTable and filling with values
$Policies = @{}
foreach ($QtreeItem in Get-NcQtree | Where-Object {$_.Vserver -notlike '*-mc' }) {
    $ExportPolicy = $QtreeItem.ExportPolicy
    if ($Policies.$ExportPolicy) {
        $Policies.$ExportPolicy += $QtreeItem
    }
    else {
        $Policies.$ExportPolicy = @($QtreeItem)
    }
}

# Comparison with HashTable: Export-Policies without "Qtree" assignment
foreach ($ExportPolicyItem in Get-NcExportPolicy | Where-Object {$_.Vserver -notlike '*-mc'}) {
    $PolicyName = $ExportPolicyItem.PolicyName
    if ($null -eq $Policies.$PolicyName) {
        Write-Host "Vserver: $($ExportPolicyItem.Vserver)"
        Write-Host "Export: $PolicyName"
        Write-Host ""
    }

    # Comparison with HashTable: If Export-Policy has no "Host" entries
    if ((Get-NcExportRule -Policy $PolicyName).Count -eq 0) {
        $PolicyItems = $Policies.$PolicyName
        if ($PolicyItems.QTree.Count) {
            foreach ($PolicyItem in $PolicyItems) {
                Write-Host "Vserver: $($PolicyItem.Vserver)"
                Write-Host "Export: $PolicyName"
                Write-Host ""
            }
        }
    }   
}

Thank you for your help and I hope my code is interesting for other ppl here :-)

Best regards,

Lukas