This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Volume Details - SQL

Inspired by the custom Node Details widget submitted by  , https://thwack.solarwinds.com/t5/NPM-Documents/Node-Details-SQL/ta-p/512112, I made a similar compact replacement for the Volume Details widget.

It consolidated 2 out of the box widgets plus a custom one I use on my volume details views into a much more efficient space.

2020-04-07 09_59_45-Volume Details - Summary - C__ Label_ 4C754212.png

2020-04-07 10_00_00-Volume Details - Summary - C__ Label_ 4C754212.png

Download the latest version of the widget from github :

https://github.com/Mesverrum/MyPublicWork/blob/master/ViewsToShare/SingleWidgets/Custom%20Volume%20Details.xml

 Download the powershell script for importing widgets here:

https://github.com/Mesverrum/MyPublicWork/blob/master/ResourceImporter.ps1

select 'Status' as [c]
, concat('<img src="/Orion/images/StatusIcons/Small-',StatusLED,'"></>'
,'<a href="/Orion/View.aspx?View=NodeDetails&NetObject=N:',cast(v.volumeid as varchar),'"> '
,si.StatusName,'</>') AS [v] 
from volumes v
join statusinfo si on si.StatusId=v.Status
where volumeid=${volumeid}
 
union all ( select 'Type' as [c] , concat('<img src="/NetPerfMon/images/Volumes/',volumetypeicon,'"></> ' , v.VolumeType) as [v] from volumes v where volumeid=${volumeid} ) 

union all ( 
select 'Space Usage' as [c] 
, concat( case when v.VolumePercentUsed > isnull(fc.criticalthreshold,gcrit.crit) then '<img src="/Orion/images/StatusIcons/Small-critical.gif"></> ' 
when v.VolumePercentUsed > isnull(fc.criticalthreshold,gwarn.warn) then '<img src="/Orion/images/StatusIcons/Small-warning.gif"></> ' 
else '<img src="/Orion/images/StatusIcons/Small-up.gif"></> ' end 
, round(case when v.VolumeSize > 1099511627776 then (v.volumespaceused/1099511627776) 
when v.VolumeSize > 1073741824 then (v.volumespaceused/1073741824) 
when v.VolumeSize > 1048576 then (v.volumespaceused/1048576) 
when v.VolumeSize > 1024 then (v.volumespaceused/1024) 
else v.volumespaceused end,2) 
, case when v.VolumeSize > 1099511627776 then ' TB ' 
when v.VolumeSize > 1073741824 then ' GB ' 
when v.VolumeSize > 1048576 then ' MB ' 
when v.VolumeSize > 1024 then ' KB ' 
else ' Bytes ' end 
, ' used (',round(v.VolumePercentUsed,0),'%) of ' 
, round(case when v.VolumeSize > 1099511627776 then (v.VolumeSize/1099511627776) 
when v.VolumeSize > 1073741824 then (v.VolumeSize/1073741824) 
when v.VolumeSize > 1048576 then (v.VolumeSize/1048576)
when v.VolumeSize > 1024 then (v.VolumeSize/1024)
else v.volumespaceused
end,2)
, case when v.VolumeSize > 1099511627776 then ' TB '
when v.VolumeSize > 1073741824 then ' GB '
when v.VolumeSize > 1048576 then ' MB '
when v.VolumeSize > 1024 then ' KB '
else ' Bytes '
end
,' total size')
as [v]

from volumes v
cross join (Select CurrentValue AS crit FROM Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') gcrit
cross join (Select CurrentValue AS warn FROM Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') gwarn
left join ForecastCapacitySettings fc on fc.InstanceId=v.VolumeID and fc.MetricId = 3
where volumeid=${volumeid}
) 

union all (
select 'Warning Threshold' as [c] , case when fc.warningthreshold is not null then (concat(fc.warningthreshold,'% (Custom)')) else (concat(gwarn.warn,'% (Default)')) end as [v]
from volumes v 
cross join (Select CurrentValue AS warn FROM Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') gwarn
left join ForecastCapacitySettings fc on fc.InstanceId=v.VolumeID and fc.MetricId = 3
where volumeid=${volumeid}
) 

union all (
select 'Critical Threshold' as [c] , case when fc.criticalthreshold is not null then (concat(fc.criticalthreshold,'% (Custom)')) else (concat(gcrit.crit,'% (Default)')) end as [v]
from volumes v
cross join (Select CurrentValue AS crit FROM Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') gcrit
left join ForecastCapacitySettings fc on fc.InstanceId=v.VolumeID and fc.MetricId = 3
where volumeid=${volumeid}
) 

union all ( select 'Last Polled' as [c] , cast(max(vp.DateTime) as nvarchar) as [v]  from volumes v 
join VolumePerformance vp on vp.VolumeID=v.VolumeID 
where v.volumeid=${volumeid} group by v.volumeid ) 

union all ( select 'Polling Interval' as [c] , concat(cast(v.PollInterval as nvarchar),' seconds') as [v] from volumes v where v.volumeid=${volumeid} ) 

union all ( select 'Statistics Interval' as [c] , concat(cast(v.StatCollection as nvarchar),' minutes') as [v] from volumes v where v.volumeid=${volumeid} )

union all ( select 'Rediscovery Interval' as [c] , concat(cast(v.RediscoveryInterval as nvarchar),' minutes') as [v] from volumes v where v.volumeid=${volumeid} )