Bulk changing Volume Threshold (sql)

Hi All,

It's unfortunate that there is no GUI way to mass updating volume thresholds.  The way the thresholds are created in the tables is a bit weird and there are no verbs for altering volume thresholds. 

The Orion.Volumes ID seems to change from different installations, so we need to calculate that before doing anything else.

This query is to see which volumes do not have an entry in the thresholds table.  If it does not, then "create an entry".   Once the volumes are validated, alter the query to perform the insert on just one node.  Validate the entry in the table and the GUI.  Once it looks good, then perform the INSERT operation on the interesting volumes.

The settings for consecutive polls is set for 2 for this example.  It can be changed to serve your needs.

We'll have to resort to SQL to do this.  Be aware that the schema can change at any time - use with caution.

DECLARE @volWarning int

DECLARE @volCritical int
DECLARE @ThresholdNameIdVolume int

set @ThresholdNameIdVolume=(select id from Thresholdsnames where entitytype='Orion.Volumes')
set @volWarning=90
set @volCritical=95

--INSERT INTO THRESHOLDS ([InstanceId] ,[ThresholdType],[ThresholdNameId],[ThresholdOperator],[Warning],[Critical],[WarningFormula],[CriticalFormula],[BaselineFrom],[BaselineTo],[BaselineApplied],[BaselineApplyError] ,[WarningPolls],[WarningPollsInterval],[CriticalPolls],[CriticalPollsInterval],[WarningEnabled],[CriticalEnabled])

select volumeid as [InstanceId] , 1 as [ThresholdType],@ThresholdNameIdVolume as [ThresholdNameId]
,0 as [ThresholdOperator],@volWarning as [Warning],@volCritical as [Critical]
,NULL as [WarningFormula],NULL as [CriticalFormula],NULL as [BaselineFrom]
,NULL as [BaselineTo],NULL as [BaselineApplied],NULL as [BaselineApplyError]
,2 as [WarningPolls],2 as [WarningPollsInterval],2 as [CriticalPolls],2 as [CriticalPollsInterval]
,1 as [WarningEnabled],1 as [CriticalEnabled]
from (
select
v.volumeid
from volumes v
join nodes n on n.nodeid=v.nodeid
where v.volumeid not in (select v.VolumeID
--, n.caption,v.caption,v.VolumeID as [volumeid],t.ThresholdNameId
from Thresholds t
left join volumes v on t.InstanceId=v.VolumeID and t.ThresholdNameId=@ThresholdNameIdVolume
left join nodes n on n.nodeid=v.nodeid
where v.VolumeTypeID=4
and n.objectsubtype not like 'icmp' 
)
and n.objectsubtype not like 'icmp' and v.volumetypeid=4
and n.vendor = 'net-snmp'
--and n.nodeid=1431
) tmp

Updating existing entries in the thresholds tables should be fairly simple.

Thank you,

Amit

Loop1 systems

  • Nice Amit, Thank you  =o) ♥

  • Hi Amit, 

    Nice work, we also do this regularly as part of our consultancies, and it has a great benefit to customers. I just wanted to pop by to say that there IS a way to do this via the GUI, it's just a little awkward and slow is all!

    First, go to 'Manage Nodes' then group by, for example, Windows Server 2019 - Once you've done that, you can expand each server and select the mass select the volumes:

    After this you can edit in bulk as normal:

    Just for anyone who isn't super confident with SQL Slight smile

    Kind regards, 

    Marlie Fancourt | SolarWinds Pre-Sales Manager

    Prosperon Networks | SolarWinds Partner since 2006

  • Thanks Marlie!!   That's perfect for doing a handful of changes.  However, doing 7000 volumes changes can be problematic.  

    With the SQL script, it scales really well whether you're doing 10 or 1000.  It would take less than a few seconds.  Slight smile

    The same goes for deleting volumes (I think I posted a SQL script for that as well).  Scheduled discovery pulles in all volumes by default (unless exclusions are set up which are tedious to do).  The volume delete script is run after a discovery to remove unneeded volumes.

    Automation is the way to go. 

  • Yep absolutely agree! Still not as awkward as trying to change from SNMP/Agent status polling to ICMP status polling in bulk though ;)

  • Hi Amit,

    What would I need to add and where, in order for this to only apply to P:\ volumes?