cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 10

Map volume to vserver with SWQL

Jump to solution

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?

0 Kudos
1 Solution

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
    }
}

 

 

 

View solution in original post

0 Kudos
8 Replies
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
0 Kudos

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.

0 Kudos

@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
0 Kudos

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.

0 Kudos
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?
0 Kudos

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
    }
}

 

 

 

View solution in original post

0 Kudos

@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!

0 Kudos
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.
0 Kudos