Need help with a SWQL query - updated

Question

Howdy! 
 I have this query to tell me when the sys admins change volume resources. What I can't figure out is how to get the date of change, if its even possible. 
 
 SELECT
N.Caption AS [Node]
,V.Caption AS [Volume]
,Round(MIN(DiskSize)/1024/1024/1024,0) AS [Before_GB]
,Round(MAX(DiskSize)/1024/1024/1024,0) AS [After_GB]
,N.DetailsUrl AS [_LinkFor_Node]
,V.DetailsUrl AS [_LinkFor_Volume]
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()-7 --Check last x days
AND V.VolumeTypeID IN (2,4,100) -- Physical RAM, Fixed disk, mount point
GROUP BY N.Caption,V.Caption, N.DetailsUrl, V.DetailsUrl
HAVING MAX(VS.DiskSize)<>MIN(VS.DiskSize)
ORDER BY N.Caption

This is an example of what I am trying to get. The code above pulls the difference in DiskSize (shown in yellow in the pic), but I also want to pull the date (in blue) when the DiskSize changed. This is pic is only showing/using the VolumeUsageHistory table. I hope that helps clarify. 
 
 Any ideas? 
 Cheers!

swellerrific · Accepted Answer

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

swellerrific · Answer

Hi All - I was able to resolve this by switching from SWQL to SQL and using the (LAG) function. 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 Thanks to those who offered suggestions. I do appreciate it!

Need help with a SWQL query - updated

Top Replies