I apologize in advance for my not so friendly formatting. The following is a script my DBA and I wrote to assist with balancing nodes. This is not the script that balances them, however its the first part. This script updates a custom property whose values represent the polling engine desired, marks it as in the DMZ or not, and defines its logical location in the network (which can be used for maps and dynamic groups). It only runs on nodes with custom property value Update_Script is yes. My query balances off of subnet and logical location to designate which poller it goes to, among other things. You can add in whatever conditions you like, I have removed most of mine. At the very end, this script outputs a change log. This script runs nightly on the SQL agent. I am confident someone better than me can optimize this.
Note the following custom properties need to exist:
CP_Location
InDMZ
Polling_Engine
Update_Script
In order to run this query on a node, you can use the web interface to set custom property Update_Script to yes or null. I have it as the default when a new node is discovered. Script sets this value back to no so it will not run twice. I like Automation.
-------------------------------------------------------------------------------------
BEGIN TRANSACTION;
--Define conditions to update logical location, temporary value to use in second part of the query
WITH cte_locations
AS (SELECT d.NodeID,
d.IP_Address,
CASE
WHEN d.IP_Address LIKE '<<dmz subnet>>' THEN '<<your_location value here>>'
<<additional conditions here>>
END AS your_Location,
--set in dmz or not below
CASE
WHEN d.IP_Address LIKE '<<dmz subnet>>' THEN 1
ELSE 0 END AS In_DMZ
FROM dbo.NodesData d
INNER JOIN dbo.NodesCustomProperties c
ON c.NodeID = d.NodeID
),
--part DEUX below is where the magic happens, update polling engine property
cte_poll
AS (SELECT d.NodeID,
d.your_Location,
d.In_DMZ,
d.IP_Address,
CASE
WHEN d.In_DMZ = 1 THEN '<<poll engine CP value>>'
WHEN d.IP_Address LIKE '<<subnet>>' OR d.IP_Address LIKE '<<subnet>>' THEN '<<poll engine cp value>>'
WHEN d.your_Location IN ( '<<cp_location value>>' ) AND d.IP_Address LIKE '<<subnet>>' THEN '<<cp polling engine value>>'
<<additional conditions here>>
.
ELSE CASE
WHEN d.IP_Address LIKE '<<subnet>>' OR d.IP_Address LIKE '<<subnet>>' THEN '<<poll engine cp value>>'
<<additional conditions here>>
ELSE '<<cp poll engine value>>'
END
END AS Polling_Engine
FROM cte_locations d)
--ACTUAL changes below
UPDATE NodesCustomProperties
SET CP_Location = z.your_Location, --logical location of the node
InDMZ = z.In_DMZ, --is this node in the dmz
Polling_Engine = z.Polling_Engine, --what polling engine you want it on
Update_Script = 'No' --do not modify this node next time.
--output change log
OUTPUT Deleted.NodeID, Deleted.CP_Location, Inserted.CP_Location, Deleted.Polling_Engine, Inserted.Polling_Engine
FROM cte_poll z
WHERE z.NodeID = NodesCustomProperties.NodeID
ROLLBACK
--commit
