Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

OpsCenter Custom SQL Reporting

Created: 08 Dec 2013 • Updated: 08 Dec 2013 | 1 comment
drm163's picture
This issue has been solved. See solution.

Hi

I'm trying to generate a report that displays the percentage usage for disk pools.

I can display this either as an integer or as a decimal to 6 places - neither is what I am after.

I am trying to limit the percentage display to 2 decimal places.

From investigation on SQL, this should be doable using DECIMAL(3,2) for instance - but I have not yet found the right was to add this to a custom SQL script (or even if this is the right way for OpsCenter). I've also looked at the FORMAT directive without success.

Here is the SQL I am using (this shows the percentage as an Interger - changing 100 to 100.0 shows as percentage to 6 places):

SELECT
  dms.friendlyName as "Master Server",
  dp.name AS "Disk Pool",
  dp.serverType AS "Pool Type",
  100*dp.usedCapacity/dp.usableSize AS "Percentage Full"
FROM domain_DiskPool dp
INNER JOIN domain_MasterServer dms ON dms.id = dp.MasterserverID

GROUP BY dms.friendlyName, dp.name, dp.serverType, dp.usedCapacity, dp.usableSize
ORDER BY dms.friendlyName, dp.name, dp.serverType, dp.usedCapacity, dp.usableSize
 

Operating Systems:

Comments 1 CommentJump to latest comment

drm163's picture

After a little more investigation I found the correct usage of the CAST operative.

This now displays the percentage as I wanted and ordered by percentage full.

---------------

SELECT
  dms.friendlyName as "Master Server",
  dp.name AS "Disk Pool",
  dp.serverType AS "Pool Type",
  CAST (100*dp.usedCapacity/dp.usableSize AS DECIMAL(4,1) ) "Percentage Full"
FROM domain_DiskPool dp
INNER JOIN domain_MasterServer dms ON dms.id = dp.MasterserverID

GROUP BY dms.friendlyName, dp.name, dp.serverType, dp.usedCapacity, dp.usableSize
ORDER BY "Percentage Full", dms.friendlyName, dp.name, dp.serverType, dp.usedCapacity, dp.usableSize

SOLUTION