Hi, I have written an SQL query which I was hoping to use for a report on Solarwinds, but it does not appear to work within the report writer. Is this because Solarwinds report writer does not support FULL SQL?
My SQL query which works on my actual SQL server is:
WITH Capacity AS(SELECT n.NodeID, n.Caption AS NodeName, v.volumeid, MAX(v.Caption) AS volume_name,
CAST
(MAX(volumesize/1073741822.30351) AS DECIMAL(12, 2))AS disksize_gb,
CAST(MAX(volumespaceavailable/1073741822.30351) AS DECIMAL(12, 2)) AS disk_remains,
CAST((MAX(avgdiskused/1073741822.30351) - MIN(avgdiskused/1073741822.30351)) / COUNT(DISTINCT DATENAME(mm,vd.datetime))AS DECIMAL(12, 2)) AS avg_usage, CAST(MAX(volumespaceavailable/1073741822.30351) / ((MAX(avgdiskused/1073741822.30351) - MIN(avgdiskused/1073741822.30351)+0.001) / COUNT(DISTINCT DATENAME(mm,vd.datetime)+ DATENAME(yy,vd.datetime))) AS DECIMAL(12, 0)) AS months_remain FROM
Nodes n JOIN VolumeUsage_Daily vd ON n.NodeId = vd.nodeid JOIN
volumes v ON vd.volumeid = v.volumeid AND n.NodeId = v.NodeId WHERE
(N.Caption LIKE '%SV') OR (N.Caption LIKE '%DC') GROUP BY n.Caption, n.NodeID, v.volumeid)SELECT
NodeId, NodeName, volumeid, volume_name, disksize_gb, disk_remains, avg_usage, months_remain,
CASE WHEN months_remain > 95000 THEN CAST('99991231' AS datetime) ELSE DATEADD(m, months_remain, GETDATE()) END AS full_dateFROM
Capacity
ORDER BY NodeName, volumeid;
