1 Reply Latest reply on Mar 8, 2013 6:25 PM by rmcder

    Need Help Modifying Report

    rmcder

      Hello all,

       

      I have a daily report that is mailed to our admins group that shows which disks are currently low on space.  The results are sorted ascending on (Percent Available).  The report was created in Report Writer and it looks like this:

       

      ************************************************************************

      Low Free Disk Space

      Less than 20 % Free Space Available

       

      (Node)    (Volume)    (Percent Available) (Space Available) (Volume Size)

       

      Node1        F:\                0 %                    9.9 MB            210.7 GB

      Node2        E:\                2 %                    1.1 GB            68.3 GB

      ...

      Node(n)

      ************************************************************************

       

      I've been asked to improve the report - and I'm not able to figure this out.  The improvement requested is to add an additional column between (Space Available) and (Volume Size) called (Yesterday's Space Available).  This new column would be the value of space available 'yesterday' on the listed volume.  The additional column will make it easier to identify a volume that is filling up fast.

       

      I've been experimenting with custom SQL script and can create a report with two lines per volume (one for today and one for yesterday) but that is not what the boss is looking for.  I suspect this is possible using a custom SQL script but I don't have the skills to get the job done.

       

      If any Report Gurus have ideas or suggestions how I can add the additional column to this report, I'd really appreciate it.  I'm currently using NPM 10.1.1 SP1.

       

      Thanks very much in advance...

        • Re: Need Help Modifying Report
          rmcder

          Well, for what it's worth here is the custom SQL report syntax that fits the bill:

           

          SELECT  Nodes.Caption AS Node,

          left(Volumes.Caption,3) AS Disk,

          volumes.VolumeSize as Capacity,

          (100-volumes.VolumePercentUsed) as '% Free',

          volumes.VolumeSpaceAvailable as 'Free',

          MAX(VolumeUsage.DiskSize-VolumeUsage.MinDiskUsed) AS 'Previous'

          FROM

          (Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID))  INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)

          WHERE

          ( volumeusage.DateTime < CAST(CONVERT(datetime,GETDATE()) as bigint) and DateTime >= CAST(CONVERT(datetime,GETDATE()) as bigint)-1 )

          AND

          volumes.VolumePercentUsed > 80

          AND

          Nodes.NetworkCategory='Servers'

          AND

          (

            NOT (Volumes.Caption LIKE 'Vir%%') AND

            NOT (Volumes.Caption LIKE '/vmf%%') AND

            NOT (Volumes.Caption LIKE 'Phy%%')

          )

          GROUP BY volumeusage.disksize,nodes.Caption,volumes.Caption,volumes.VolumeSpaceAvailable,volumes.VolumeSize,volumes.VolumePercentUsed

          order by VolumePercentUsed desc

           

          Don't know if others will find this useful or not.  Recommendations for improvement appreciated.