Modifying the LUN Growth Report to specific LUNs


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
     -- get used capacity from 30 days ago
          , AVG(CapacityAllocated) AS PastCapacityAllocated
     FROM Orion.SRM.LUNCapacityStatistics
     WHERE ObservationTimestamp < AddDay(- 30, GetDate())
          AND ObservationTimestamp > AddDay(- 31, GetDate())
     ) AS t
     ON (l.LUNID = t.LUNID)

Any help is greatly appreciated!

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

  • Ah! I was close to that. That makes it much more clear. I think it works--at least it's pulling the entities I want. You rock!

  • You are most welcome.  Don't sleep on Navigation Properties.  As "Custom Properties" are the secret superpower of the Orion Platform, Navigation Properties are the secret superpower of the SolarWinds Query Language.