Storage Pool-LUN growth queries.docx

Version 1

    These reports are for SRM Orion, they are custom SWQL reports for the following:

     

    Storage Pool growth - last 30 days

    Storage LUN growth with negative values column - last 30 days

    Storage LUN growth without negative values column - last 30 days

     

    For these you will go to Home tab > Reports > Manage Reports> Create New Report

     

    Choose Custom Table

    custom table.png

    Choose Advanced Database Query and input query into box - name your datasource

    Add content.png

    Choose Add Column

    add column.png

    Click all columns that appear

    add column 2.png

    On this page you can reorganize the columns in to which ever order you wish by drag and drop as well as name your Custom Table

    template.png

    If you wish to see the sizes in Gb or Mb you can also do that here by simply clicking on the +advanced and choosing Data Unit under the Add display settings drop down.

    data units 1.png

    After you choose that you will get options choose whichever you you want to see - the example below uses 1024 Gb

    data units 2.png

    Run through the rest of the wizard to name your report and schedule if necessary.

     

    Below you will find the SQWL queries to use.

     

    Storage Pool growth - last 30 days:

    SELECT

         sa.Caption AS StorageArray,

         p.Caption AS Pool,

         p.RaidType AS Raid,

         p.CapacityUserTotal AS UsablePoolSize,

         p.CapacityUserFree AS Free,

         p.CapacityUserUsed AS Used,

         t.PastCapacityUserUsed AS PastUsed,

         p.CapacityUserUsed - IsNull(t.PastCapacityUserUsed, 0) AS MonthlyGrowth,

         CASE WHEN IsNull(t.PastCapacityUserUsed, 0) > 0 THEN (Round((p.CapacityUserUsed * 100.0) / t.PastCapacityUserUsed, 2) - 100) END AS MonthlyGrowthPercent,

         Round((p.CapacityAllocated * 100.0) / p.CapacityUserTotal, 2) AS AllocatedPercent,

         Round((p.CapacitySubscribed * 100.0) / p.CapacityUserTotal, 2) AS SubscriptionPercent,

         p.CapacitySubscribed AS Subscription,

         p.CapacityUserTotal - p.CapacitySubscribed AS FreeSpaceAfterSubscription

    FROM Orion.SRM.Pools p

    LEFT JOIN Orion.SRM.StorageArrays sa ON (p.StorageArrayID=sa.StorageArrayID)

    LEFT JOIN

    (

         -- get used capacity from 30 days ago

         SELECT PoolID, AVG(CapacityUserUsed) AS PastCapacityUserUsed FROM Orion.SRM.PoolCapacityStatistics

         WHERE ObservationTimestamp < AddDay(-30, GetDate()) AND ObservationTimestamp > AddDay(-31, GetDate())

         GROUP BY PoolID

    ) AS t ON (p.PoolID = t.PoolID)

     

    Storage LUN growth with negative values column - last 30 days:

    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,

    l.CapacityAllocated - IsNull(t.PastCapacityAllocated, 0) AS MonthlyGrowth,

         CASE WHEN IsNull(t.PastCapacityAllocated, 0) > 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)


    Storage LUN growth without negative values column - last 30 days:

    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)



    If you are interested in learning more about SWQL or our SDK program please see the below links.


    Thanks!!



    SWQL: SolarWinds Online Help

    SDK: Orion SDK Information