Need Help Modifying Report

Question

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

rmcder · Accepted Answer

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.