Trying to list nodes with volume changes with date changed.

swellerrific

Hi All. I'm trying to list nodes with volume changes and the date it changed. I have this so far, but I can't seem to figure out how to get the associated datetime the change was made. Any ideas?

SELECT
N.Caption AS [Node]
,N.NodeID
,VS.VolumeID
,V.Caption AS [Volume]
,Round(MIN(DiskSize)/1024/1024/1024,0) AS [MinDisk_GB]
,Round(MAX(DiskSize)/1024/1024/1024,0) AS [MaxDisk_GB]

FROM Orion.VolumeUsageHistory AS VS
INNER JOIN Orion.Volumes AS V on VS.VolumeID=V.VolumeID
INNER JOIN Orion.Nodes AS N ON V.NodeID=N.NodeID
WHERE
VS.DateTime>GETUTCDATE()-2 --Check last x days
AND V.VolumeTypeID IN (2,4,100) -- Physical RAM, Fixed disk, mount point
GROUP BY N.NodeID, VS.VolumeID, N.Caption, V.Caption
HAVING MAX(VS.DiskSize)<>MIN(VS.DiskSize)

swellerrific

Resolved this by switching from SWQL to SQL:

SELECT 
	N.Caption AS Node
	,D.[Timestamp]
	,D.Caption AS Volume
	,D.Prev_DiskSize_GB AS Prev_GB
	,D.New_DiskSize_GB AS New_GB
	,D.[VolumeType]
	,D.[VolumeID]
    ,D.[NodeID]
	,D.[VolumeTypeID]
FROM 
	(SELECT 
		VS.[VolumeID]
		,VS.[NodeID]
		,VS.[Timestamp]
		,ROUND(LAG(DiskSize,1,0) OVER (PARTITION BY VS.VolumeID ORDER BY Timestamp)/1024/1024/1024,0) AS Prev_DiskSize_GB
		,ROUND(DiskSize /1024/1024/1024,0) AS New_DiskSize_GB
		,V.[VolumeType]
		,V.[VolumeTypeId]
		,V.[Caption]
	  FROM [DB].[dbo].[VolumeUsage_CS_Detail_hist] VS
		LEFT JOIN [DB].[dbo].[Volumes] V ON VS.VolumeID=V.VolumeID
	  WHERE Timestamp>GETUTCDATE()-14
		AND V.VolumeTypeID IN (2,4,100) -- Physical RAM, Fixed disk, mount point)
		) AS D
	  LEFT JOIN [DB].[dbo].[Nodes] AS N ON D.NodeID=N.NodeID
WHERE 
	New_DiskSize_GB <> Prev_DiskSize_GB
	AND New_DiskSize_GB <> '0'
	AND Prev_DiskSize_GB <> '0'

ORDER BY Timestamp DESC, N.Caption