Create SQL job on SolarWindsOrion SQL dB to update Thresholds based on Custom Properties

SW_United

I have a number of automated tasks on nodes/interfaces/volumes that updates the Custom Property fields.

Off the back of this Custom Property information, such as Environment and DeviceRole I'd like to specify a set range of thresholds

Example

	LIVE			DEV
SQL	Warning	Critical	CRITERIA	Warning	Critical	CRITERIA
CPU	90	95	> 2min	90	95	> 15min
Memory	98	99	> 5min	98	99	> 30min

The above DeviceRole is Database and Environment Live (Prod) and Dev, so I'd like to set these Warning and Critical figures. the criteria is the length of time before the alert triggers.

I have this SQL code so far (extracted from previous post on here so thanks for that .... link) 

update dbo.Thresholds
set ThresholdType = 1, Warning = 90, Critical = 95
where ThresholdNameId =1 AND N.CustomProperties.Environment = Production AND N.CustomProperties.DeviceRole = Database

I've managed to pull this off AI but not had a chance to access my lab to test it yet;

UPDATE dbo.Threshold
SET 
    WarningThreshold = 92,
    CriticalThreshold = 95
WHERE 
    NodeID IN (
        SELECT NodeID 
        FROM Orion.NodesCustomProperties 
        WHERE Environment = 'Live' 
        AND DeviceRole = 'Database'
    );

Anyone already doing something similar to this, really appreciate any input.

Thanks

SW_United

Another update; I've found Threshold table;

Id	InstanceId	ThresholdType	ThresholdNameId	ThresholdOperator	Warning	Critical	WarningFormula	CriticalFormula	BaselineFrom	BaselineTo	BaselineApplied	BaselineApplyError	WarningPolls	WarningPollsInterval	CriticalPolls	CriticalPollsInterval	WarningEnabled	CriticalEnabled
100	36	1	1	0	92	95							1	1	1	1	TRUE	TRUE
101	36	1	2	0	92	95							1	1	1	1	TRUE	TRUE

Id = Unique identifier within this table

ThresholdType = Using custom thresholds (not using default)

InstanceId = NodeID from Nodes table

ThresholdNameId

1 = CPU
2 = Memory
46 = Volume

Hopefully someone with more DB knowledge can help with this query