2 Replies Latest reply on Jul 5, 2017 9:24 AM by maruthappan

    I want to create report monthly view for volume capacity of past 6 months

    maruthappan

      I have created report for past 6 months of Volume capacity, getting the report result view as all the months in same row, but I want to project the months in every column.

       

      Getting Report as...

       

      Display Name

       

       

      Volume Name

       

       

      Volume Percent Available

       

       

      Used space

       

       

      Available space

       

       

      Timestamp

       

       

      xxxx1

       

      C:

       

      92.6 %

       

      18.83 GB

       

      236.34 GB

       

      Jun-17

       

      xxxx1

       

      D:

       

      27.3 %

       

      178.88 GB

       

      67.19 GB

       

      Jun-17

       

      xxxx2

       

      C:

       

      30.7 %

       

      170.59 GB

       

      75.49 GB

       

      Jun-17

       

      xxxx2

       

      D:

       

      30.3 %

       

      171.52 GB

       

      74.55 GB

       

      Jun-17

       

      xxxx1

       

      C:

       

      92.6 %

       

      18.83 GB

       

      236.34 GB

       

      May-17

       

      xxxx1

       

      D:

       

      27.3 %

       

      178.88 GB

       

      67.19 GB

       

      May-17

       

      xxxx2

       

      C:

       

      30.7 %

       

      170.59 GB

       

      75.49 GB

       

      May-17

       

      xxxx2

       

      D:

       

      30.3 %

       

      171.52 GB

       

      74.55 GB

       

      May-17

       

      xxxx1

       

      C:

       

      92.6 %

       

      18.83 GB

       

      236.34 GB

       

      Apr-17

       

      xxxx1

       

      D:

       

      27.3 %

       

      178.88 GB

       

      67.19 GB

       

      Apr-17

       

      xxxx2

       

      C:

       

      30.7 %

       

      170.59 GB

       

      75.49 GB

       

      Apr-17

       

      xxxx2

       

      D:

       

      30.3 %

       

      171.52 GB

       

      74.55 GB

       

      Apr-17

       

       

      Looking report as.. Can some one please help me.

       

      Server

       

      Drive

       

      April

       

      May

       

      June

       

      xxxx1

       

      C:

       

      89.57

       

      93.59

       

      93.70

       

      xxxx1

       

      D:

       

      99.74

       

      99.92

       

      99.91

       

      xxxx2

       

      C:

       

      21.03

       

      17.51

       

      16.31

       

      xxxx2

       

      D:

       

      9.79

       

      8.83

       

      8.07

       

        • Re: I want to create report monthly view for volume capacity of past 6 months
          mesverrum

          SQL is not very efficient at tweaking the presentations but this particular one is doable, depending on your tables might take a longer time to execute.

           

          For those monthly aggregate values, are you using averages or peak values?

           

          Try this query to get averages

           

          Select v.node.caption as [Node], v.Caption as [Volume], round(v.Size/1073741824,1) as [Size (GB)] , tostring(m0.[AVG])+ ' %' as [This Month Used], tostring(m1.[AVG])+ ' %' as [Previous Month Used], tostring(m2.[AVG])+ ' %' as [2m Ago Used]
          
          from orion.Volumes v
          left join (SELECT NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime))) as [yyyy-mm], round(avg(PercentDiskUsed),0) as [AVG], max(percentdiskused) as [Peak]
          FROM Orion.VolumeUsageHistory
          where monthdiff(datetime,getdate())=0
          group by NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime)))) m0 on m0.nodeid=v.NodeID and m0.volumeid=v.VolumeID
          
          left join (SELECT NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime))) as [yyyy-mm], round(avg(PercentDiskUsed),0) as [AVG], max(percentdiskused) as [Peak]
          FROM Orion.VolumeUsageHistory
          where monthdiff(datetime,getdate())=1
          group by NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime)))) m1 on m1.nodeid=v.NodeID and m1.volumeid=v.VolumeID
          
          left join (SELECT NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime))) as [yyyy-mm], round(avg(PercentDiskUsed),0) as [AVG], max(percentdiskused) as [Peak]
          FROM Orion.VolumeUsageHistory
          where monthdiff(datetime,getdate())=2
          group by NodeID, VolumeID, (tostring(year(datetime)) + ' - '+ tostring(month(DateTime)))) m2 on m2.nodeid=v.NodeID and m2.volumeid=v.VolumeID