Map volume to vserver with SWQL

Hi,

I'm trying to update volumes using SWQL and Orion SDK but I need to take which vserver those volumes belong to. In SQL I see there is a separate table just for VolumeToVserver mapping but I can't seem to find similar in SWQL.

Any suggestions?

  • Its there, but I don't know if you can set it, it might be read only
    SELECT TOP 1000 VirtualDiskID, VolumeID
    FROM Orion.VIM.VirtualDiskVolumeMapping
  • 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.


  •  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.

  • so you want a script ( powershell?) to iterate through the Nas volumes, check the SVM (which I think is SRM_V.VServers.Caption) and if SVM = x then set custom property "prop1" to value "1" If SMV = y then set prop1 to value "2"
    Does that sound right?
  • OK , 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
        }
    }
  •  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!

  • That is the magic of SWQL, they do a bunch of those Joins for you, I assume you ahve the SDK, so in SWQL studio you will see the fields for the view you are quering, and linked tables under that. SWQL studio also does some autocomplete, so if you think something is related, just type it and maybe it comes up. It probably is.