Map volume to vserver with SWQL

Thanks! Yes, I can see those. However, I might not have been clear in my original question, what I'm looking for is the mapping between volumes and SVMs in NetApp using SRM module. I can see table for volumes and I can see table for vservers but I can't seem to figure out how to bind two together.

---

rinat.kirimov wrote:

Thanks! Yes, I can see those. However, I might not have been clear in my original question, what I'm looking for is the mapping between volumes and SVMs in NetApp using SRM module. I can see table for volumes and I can see table for vservers but I can't seem to figure out how to bind two together.

---

OK Maybe this will help, Its something close to what I threw on a dashboard (uses the custom query widget). If its not enough to get you started, @ me again.  I have a dashboard I am working on that when I get it working right I will be posting it, but this might be enough to get you moving in the right direction.

    SELECT 
     SRM_V.StorageArray.Caption AS [StorageArray],
     SRM_V.StorageArray.DetailsUrl AS [_LinkFor_StorageArray],
     SRM_V.Pools.Caption AS Pool,
     SRM_V.Pools.DetailsUrl AS [_LinkFor_Pool],
     SRM_V.VServers.Caption as [VServer],
     SRM_V.VServers.DetailsUrl as [_LinkFor_VServer],
     SRM_V.Caption AS [StorageVolume],
     SRM_V.DetailsUrl AS [_LinkFor_StorageVolume],
     SRM_V.CapacityTotal/1073741824 AS [Volume Size (GB)],
     SRM_V.CapacityFree/1073741824 AS [Volume Space Free Now (GB)],
     SRM_V.CapacityAllocated/1073741824 AS [Volume Space Used Now (GB)],
     t3.PastCapacityAllocated_3_days_Ago/1073741824 AS [Used 3 Days Ago (GB)],
     t7.PastCapacityAllocated_7_days_Ago/1073741824 AS [Used 7 Days Ago (GB)],
     t30.PastCapacityAllocated_30_days_Ago/1073741824 AS [Used 30 Days Ago (GB)],
    -- 3 days ago
        (SRM_V.CapacityAllocated/1073741824 - IsNull(t3.PastCapacityAllocated_3_days_Ago/1073741824, 0)) AS [3 Day Growth (GB)],
        (Round((SRM_V.CapacityAllocated * 100.0) / t3.PastCapacityAllocated_3_days_Ago, 2) - 100) AS [3 Day Growth %],
    -- 7 days ago
        (SRM_V.CapacityAllocated/1073741824 - IsNull(t7.PastCapacityAllocated_7_days_Ago/1073741824, 0)) AS [7 Day Growth (GB)],
        (Round((SRM_V.CapacityAllocated * 100.0) / t7.PastCapacityAllocated_7_days_Ago, 2) - 100) AS [7 Day Growth %],
    -- 30 days ago
        (SRM_V.CapacityAllocated/1073741824 - IsNull(t30.PastCapacityAllocated_30_days_Ago/1073741824, 0))AS [30 Day Growth (GB)],
        (Round((SRM_V.CapacityAllocated * 100.0) / t30.PastCapacityAllocated_30_days_Ago, 2) - 100) AS [30 Day Growth %],

    -- other case
         CASE 
                WHEN IsNull(SRM_V.CapacityTotal, 0) > 0 
                THEN Round((SRM_V.CapacityAllocated * 100.0) / SRM_V.CapacityTotal, 2) 
         END AS AllocatedPercent,
    SRM_V.CapacityTotal/1073741824 - SRM_V.CapacityAllocated/1073741824 AS [Free Space After Allocation]

    FROM Orion.SRM.Volumes SRM_V
    LEFT JOIN 
    (
        -- get used capacity from 3 days ago 
        SELECT SRM_VCS.VolumeID, MAX(SRM_VCS.CapacityAllocated   ) AS PastCapacityAllocated_3_days_Ago
        FROM Orion.SRM.VolumeCapacityStatistics SRM_VCS
        WHERE SRM_VCS.ObservationTimestamp < AddDay(-3, GetDate()) AND SRM_VCS.ObservationTimestamp > AddDay(-4, GetDate())
     GROUP BY SRM_VCS.VolumeID       
    ) AS t3 ON (SRM_V.VolumeID = t3.VolumeID)
    
    LEFT JOIN 
    (
        -- get used capacity from 7 days ago 
        SELECT SRM_VCS.VolumeID, MAX(SRM_VCS.CapacityAllocated   ) AS PastCapacityAllocated_7_days_Ago
        FROM Orion.SRM.VolumeCapacityStatistics SRM_VCS
        WHERE SRM_VCS.ObservationTimestamp < AddDay(-7, GetDate()) AND SRM_VCS.ObservationTimestamp > AddDay(-8, GetDate())
     GROUP BY SRM_VCS.VolumeID       
    ) AS t7 ON (SRM_V.VolumeID = t7.VolumeID)

    LEFT JOIN 
    (
        -- get used capacity from 30 days ago 
        SELECT SRM_VCS.VolumeID, MAX(SRM_VCS.CapacityAllocated   ) AS PastCapacityAllocated_30_days_Ago
        FROM Orion.SRM.VolumeCapacityStatistics SRM_VCS
        WHERE SRM_VCS.ObservationTimestamp < AddDay(-30, GetDate()) AND SRM_VCS.ObservationTimestamp > AddDay(-31, GetDate())
     GROUP BY SRM_VCS.VolumeID       
    ) AS t30 ON (SRM_V.VolumeID = t30.VolumeID)


    Order by [3 Day Growth (GB)] Desc

That looks interesting but not exactly what I'm looking for. I am just trying to assign custom properties to volumes based on SVM they live on using Orion SDK. So, that's basically what I'm trying to achieve.

OK rinat.kirimov, I adapted another script I had to be close to what you want (maybe?), but I wasn't able to run it, so you will need to tweak it. Specifically the custom properties, but I might have typos or some small logic mistakes. 

param ($SwisServer, $credentials)
try { Add-PSSnapin SwisSnapin }
catch {
	Write-Host "Message: $($Error[0])";
	exit 1;
}
try {
    $swis = Connect-Swis -Certificate #Credentials not needed when run on Orion server. Good for scheduled tasks
    if ($SwisServer -eq $null) { $SwisServer = 'localhost' }
}
catch {
    $credentials = Get-Credential
    if ($SwisServer -eq $null) { $SwisServer = read-host -Prompt "Please enter a servername" }
    if ($credentials -eq $null) { $credentials = Get-Credential }   
	$Swis = Connect-Swis $SwisServer -Credential $credentials
}

$NAS_Volumes_Query = 'SELECT TOP 1000 SRM_V.Caption, SRM_V.Uri, SRM_V.VServers.Caption as [VServer] FROM Orion.SRM.Volumes SRM_V'
$SRM_NAS_Volumes = Get-SwisData -SwisConnection $swis -Query $NAS_Volumes_Query 

$PropertyValue1 = 'Kermit the Frog'
$PropertyValue2 = 'Gonzo'
$PropertyValue3 = 'Beaker'

For Each ($Volume in $SRM_NAS_Volumes) {

    If ( $Volume.VServer -like "%whatever%" ) {
        $text = "Updating "+ $volume.Caption +" Property VolProp to "+ $PropertyValue1
        Write-host $text
        $customProps = @{
            VolProp  = $PropertyValue1
            # Do more than 1 property if you want
            # otherVolProp  = 'data'
        }
        $NAS_VOlume_CustomPropURI = $Volume.URI + '/CustomProperties'
        Set-SwisObject $swis -Uri $NAS_VOlume_CustomPropURI -Properties $customProps
    }
    
    If ( $Volume.VServer -like "%something%" ) {
        $text = "Updating "+ $volume.Caption +" Property VolProp to "+ $PropertyValue2
        Write-host $text
        $customProps = @{
            VolProp  = $PropertyValue2
            # Do more than 1 property if you want
            # otherVolProp  = 'data'
        }
        $NAS_VOlume_CustomPropURI = $Volume.URI + '/CustomProperties'
        Set-SwisObject $swis -Uri $NAS_VOlume_CustomPropURI -Properties $customProps
    }
}

jm_sysadmin That totally worked! I don't know much about SQL statements, hence, the original problem of getting it to work. How did you map vserver to volumes without using any kind of join statement? Unless it's something simple that I'm totally overlooking.

Thanks a lot for the help!

jm_sysadmin That totally worked! I don't know much about SQL statements, hence, the original problem of getting it to work. How did you map vserver to volumes without using any kind of join statement? Unless it's something simple that I'm totally overlooking.

Thanks a lot for the help!