As the title says, please can someone advise on the script below? It effectively performs the same function as the Volume Utilisation report in SAM. However, it shows 'Not calculated' for a lot of entries (for no apparent reason) - I raised a ticket with tech support who refuse to help because this is a 'custom' script. So far, nobody seems to be able to answer this question (and I see in the forum that another user has a very similar query).
The script i'm using is this one:
SELECT
N.Caption as [Node],
N.IP as [IP Address],
F.InstanceCaption as [Volume],
CASE
WHEN V.size >= 1099511627776 THEN CONCAT(ROUND(V.size/1099511627776,2), ' TB')
WHEN V.size >= 1073741824 THEN CONCAT(ROUND(V.size/1073741824,2), ' GB')
WHEN V.size >= 1048576 THEN CONCAT(ROUND(V.size/1048576,2),' MB')
WHEN V.size >= 1024 THEN CONCAT(ROUND(V.size/1024,2),' KB')
ELSE '0'
END as [VolumeSize],
CASE
WHEN v.VolumeSpaceUsed >= 1099511627776 THEN CONCAT(ROUND(v.VolumeSpaceUsed/1099511627776,2), ' TB')
WHEN v.VolumeSpaceUsed >= 1073741824 THEN CONCAT(ROUND(v.VolumeSpaceUsed/1073741824,2), ' GB')
WHEN v.VolumeSpaceUsed >= 1048576 THEN CONCAT(ROUND(v.VolumeSpaceUsed/1048576,2),' MB')
WHEN v.VolumeSpaceUsed >= 1024 THEN CONCAT(ROUND(v.VolumeSpaceUsed/1024,2),' KB')
ELSE '0'
END as [Space Used],
CASE
WHEN v.VolumeSpaceAvailable >= 1099511627776 THEN CONCAT(ROUND(v.VolumeSpaceAvailable/1099511627776,2), ' TB')
WHEN v.VolumeSpaceAvailable >= 1073741824 THEN CONCAT(ROUND(v.VolumeSpaceAvailable/1073741824,2), ' GB')
WHEN v.VolumeSpaceAvailable >= 1048576 THEN CONCAT(ROUND(v.VolumeSpaceAvailable/1048576,2),' MB')
WHEN v.VolumeSpaceAvailable >= 1024 THEN CONCAT(ROUND(v.VolumeSpaceAvailable/1024,2),' KB')
ELSE '0'
END as [Space Available],
CONCAT(round(V.VolumePercentAvailable,1),'%') AS [Percent Available],
CONCAT(round(V.VolumePercentUsed,1),'%') as [Percent Used],
CASE
WHEN F.DaysToWarningAvg is NULL THEN tostring('Not Calculated')
WHEN F.DaysToWarningAvg <= 0 THEN tostring('Now')
WHEN F.DaysToWarningAvg >= 1825 THEN tostring('> 5 Years')
WHEN F.DaysToWarningAvg >= 365 THEN tostring('> 1 Year')
WHEN F.DaysToWarningAvg >= 90 THEN tostring('> 90 Days')
WHEN F.DaysToWarningAvg >= 60 THEN tostring('> 60 Days')
WHEN F.DaysToWarningAvg >= 30 THEN tostring('> 30 Days')
ELSE tostring(tostring(F.DaysToWarningAvg) + ' Days')
END AS [Warning],
CASE
WHEN F.DaysToCriticalAvg is NULL THEN tostring('Not Calculated')
WHEN F.DaysToCriticalAvg <= 0 THEN tostring('Now')
WHEN F.DaysToCriticalAvg >= 1825 THEN tostring('> 5 Years')
WHEN F.DaysToCriticalAvg >= 365 THEN tostring('> 1 Year')
WHEN F.DaysToCriticalAvg >= 90 THEN tostring('> 90 Days')
WHEN F.DaysToCriticalAvg >= 60 THEN tostring('> 60 Days')
WHEN F.DaysToCriticalAvg >= 30 THEN tostring('> 30 Days')
ELSE tostring(tostring(F.DaysToCriticalAvg) + ' Days')
END AS [CRITICAL],
CASE
WHEN F.DaysToCapacityAvg is NULL THEN tostring('Not Calculated')
WHEN F.DaysToCapacityAvg <= 0 THEN tostring('Now')
WHEN F.DaysToCapacityAvg >= 1825 THEN tostring('> 5 Years')
WHEN F.DaysToCapacityAvg >= 365 THEN tostring('> 1 Year')
WHEN F.DaysToCapacityAvg >= 90 THEN tostring('> 90 Days')
WHEN F.DaysToCapacityAvg >= 60 THEN tostring('> 60 Days')
WHEN F.DaysToCapacityAvg >= 30 THEN tostring('> 30 Days')
ELSE tostring(tostring(F.DaysToCapacityAvg) + ' Days')
END AS [AT CAPACITY]
FROM
orion.forecastcapacity F
LEFT JOIN
Orion.Nodes N ON F.NodeID = N.NodeID
LEFT JOIN
Orion.Volumes V ON F.InstanceId = V.VolumeID
LEFT JOIN
Orion.NodesCustomProperties ONCP ON ONCP.NodeID = N.NodeID
WHERE
F.MetricName = 'Forecast.Metric.PercentDiskUsed'
AND ONCP.CapacityPlan = '1' -- Filtering based on CapacityPlan field from NodesCustomProperties table
AND N.status <> 2
AND N.status <> 9
AND V.status <> 2
AND V.StatusDescription <> 'Unknown'
AND V.VolumeType LIKE '%Fixed%'
--UnComment below to use in a search box and make it searchable by Volume/Node Caption
--AND (V.Caption LIKE '%${SEARCH_STRING}%' OR N.Caption LIKE '%${SEARCH_STRING}%')
ORDER BY
F.DaysToCapacityAvg ASC,
F.DaysToCriticalAvg ASC,
F.DaysToWarningAvg ASC
A sample of the output it produces is below:
Any help as gratefully received.
Simon
