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?
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?
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!
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.