cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter Custom SQL Reporting

DaveM16
Level 4
Partner Employee Accredited Certified

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
 

1 ACCEPTED SOLUTION

Accepted Solutions

DaveM16
Level 4
Partner Employee Accredited Certified

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

 

 

View solution in original post

1 REPLY 1

DaveM16
Level 4
Partner Employee Accredited Certified

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