Need help with a SWQL query - updated

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!

Find more posts tagged with

Accepted answers

swellerrific

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!

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

All comments

adam.beedell

I can't check right now, but I guess there's no event currently?

(Could join to it with vol id around min(vssize) possibly)

swellerrific

Thanks Adam. We do currently have events. I'm not sure how to do the join you are suggesting.

bobmarley

If the data is there it may be in the Audit Event table. Try looking here and if it could then join the tables together.

Select TOP 100 
      VAR2.NodeID
    , VAR2.IP_Address
    , VAR2.Caption
    , VAR2.customproperties._group
    , VAR1.AuditEventMessage
    , VAR2.Status
    , VAR1.TimeLoggedUtc

FROM Orion.AuditingEvents VAR1
INNER JOIN Orion.nodes VAR2 on VAR1.NetObjectID = VAR2.NodeID

adam.beedell

I'm on holiday at the moment, if you havnt got it working by next week ping me

You can join on anything that matches, Bob's suggestion should work, but you could have a value on one part of the join pulled out of the event text or whatever using a subquery

swellerrific

Thanks bobmarley. It appears that these don't appear in the audit events and thats ok. I'll edit my question to remove that part. I would still like the date of change though. I can see the dates in the VolumeUsageHistory table, but haven't been able to get a query to pull it. I see Adam suggested a subquery but I'm not sure how to do that within this query.

bobmarley

The column DiskSize is where the change is made. As soon as SolarWinds discovers the change it will update this row. The row will show the new value and what time the value changed. The discoveries are not 'live' but run on a schedule so may not occur at the same time the admin made the change.

SELECT TOP 100 NodeID, VolumeID, DateTime, DiskSize, AvgDiskUsed, MinDiskUsed, MaxDiskUsed, PercentDiskUsed, AllocationFailures, ObservationTimestamp, ObservationFrequency, Weight, DisplayName, Description, InstanceType, Uri, InstanceSiteId
FROM Orion.VolumeUsageHistory

--Where VolumeID Like '728383' 
--Disk size is what shows when the change happened

swellerrific

Thanks Bob. I am aware of that table and it is the primary table in my query. The problem is that I can't figure out how to get that DateTime added to my current query. Everything I try fails, not that I am an expert by any means.

swellerrific

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!

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