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

Parents Reply Children
No Data