Modifying the LUN Growth Report to specific LUNs

Kwalls88

Hello,

I'm new here, and I tried searching around and found nothing, so I thought this was the best place to start (apologies if this question belongs somewhere else). 

I am trying to use the existing "Lun Growth Report - 30/60/90/180 Days" to filter for specific LUNs as opposed to all of them. It's written in a SWQL query, and while I am trying to learn more about SWQL/SQL, this bit is above my experience. I've posted the 30 Day query below -- where would I modify this to look for specific nodes, and what would the syntax looks like? I thought Node.Name = ' X' might work, but it did not. I'm trying to modify this one as opposed to building node specific because I didn't see any Monthly Growth options in the colums for specific nodes. 

SELECT l.StorageArray.Caption AS StorageArray
     , l.Pools.Caption AS Pool
     , l.Caption AS LUN
     , l.CapacityTotal AS LunSize
     , l.CapacityFree AS Free
     , l.CapacityAllocated AS Used
     , t.PastCapacityAllocated AS PastUsed
     , CASE 
          WHEN l.CapacityAllocated >= IsNull(t.PastCapacityAllocated, 0)
               THEN (l.CapacityAllocated - IsNull(t.PastCapacityAllocated, 0))
          ELSE 0
          END AS MonthlyGrowth
     , CASE 
          WHEN IsNull(t.PastCapacityAllocated, 0) > 0
               AND l.CapacityAllocated >= IsNull(t.PastCapacityAllocated, 0)
               THEN (Round((l.CapacityAllocated * 100.0) / t.PastCapacityAllocated, 2) - 100)
          END AS MonthlyGrowthPercent
     , CASE 
          WHEN IsNull(l.CapacityTotal, 0) > 0
               THEN Round((l.CapacityAllocated * 100.0) / l.CapacityTotal, 2)
          END AS AllocatedPercent
     , l.CapacityTotal - l.CapacityAllocated AS FreeSpaceAfterAllocation
FROM Orion.SRM.LUNs l
LEFT JOIN (
     -- get used capacity from 30 days ago
     SELECT LUNID
          , AVG(CapacityAllocated) AS PastCapacityAllocated
     FROM Orion.SRM.LUNCapacityStatistics
     WHERE ObservationTimestamp < AddDay(- 30, GetDate())
          AND ObservationTimestamp > AddDay(- 31, GetDate())
     GROUP BY LUNID
     ) AS t
     ON (l.LUNID = t.LUNID)

Any help is greatly appreciated!

KMSigma.SWI

You'd need to find a way to connect the Storage Array entities to the the respective nodes entities.  This can be done through navigation properties.

• Orion SRM.LUNs (which is aliased above to l) is connected to Orion.Volumes via the navigation property ServerVolumes or RelyServerVolumes.
• Orion.Volumes is connected to Orion.Nodes via the navigation property Node.

So you should be able to do this:

WHERE ( l.ServerVolumes.Node.Caption = 'NodeNameHere' OR l.RelyServerVolumes.Node.Caption = 'NodeNameHere' )

Please note: I cannot test this in my current lab because I do not have storage arrays.