This is my fist attempt at automating the naming of HASHes within DPA. High level, I use DPA to support over 11,000 databases across 6 servers currently. There are too many hashes to manage manually so I sat down and put together a first version of an automatic process to handle this. It runs once a day and creates a unique name for any given HASH so long as that HASH occurs only once across all monitored instances. So far, it has worked everyday for two weeks for two separate DPA backend databases.
Be advised: that this is NOT supported by Solarwinds and this is not provided by Solarwinds or a representative thereof. I am happy to answer questions, but I cannot realistically provide support for issues you may encounter. Proceed at you own risk!
There are known limitations:
- THIS WAS DEVELOPED AND USED ON AND ONLY ON DPA 2019.4.1050 RC2 I CANNOT GUARANTEE THAT IT WILL WORK ON OTHER VERSIONS
- the CON_SQL_NAME table requires a unique HASH and a unique NAME
- the CON_SQL_NAME table does NOT currently handle duplicate HASH numbers generated in different monitored instances
- the code only creates HASH/NAME pairs for HASHes that are unique across all monitored instances
- the code is TSQL so it will likely have to be updated to work in other flavors of SQL.
Basically the process does the following:
- Create a cursor from COND table to loop through tables associated with a monitored instance
- For each monitored instance, collect all HASHes from CON_STATS_..._X
- For each monitored instance, collect all HASHes and NAMEs from CONST_X where P = 0 and PNAME is not null
- Identify the HASHes that have a corresponding PNAME and limit the result only to HASHes that occur once in the entire list.
- Create a unique NAME up to 100 characters from up to the first 91 characters of the PNAME and appended with the HASH in hex with a leading underscore
- Delete anything in CON_SQL_NAME that does not exist in the list excluding default values provided by Solarwinds
- Update anything in CON_SQL_NAME that exists in both
- Insert anything that only exists in the list