SQL Replication Monitoring | Product Discussions

StephenP · 2021-03-31T11:10:56Z

Hi All, first post on the forums. I'm trying to setup some monitoring of our MSSQL replication to try and keep a track and see when it fails or issues crop up. I've tried to create a data source but am not 100% sure what i'm doing in the groovy space. I've grabbed a script that does a SQL query as a base, but i need to run a procedure (sp_replmonitorhelppublication) instead. For active discovery i'm not getting any instances. What i have is below and would be great if anyone could poing me into the right direction as to where i've gone wrong? In this case, mssl.MSSQLSERVER.replication_url looks like jdbc:sqlserver://server_name:1433;databasename=distribution;integratedSecurity=false;applicationIntent=ReadOnly (mainly because i didn't know how to specify the database in groovy) import groovy.sql.Sql // Get basic info def hostname = hostProps.get("system.hostname") def sqlInstances = hostProps.get("mssl.MSSQLSERVER.replication_url") ?: hostProps.get("auto.sql_server_instances") // override sql instances with manual instances Boolean debug = false LMDebugPrint("***** Running System Jobs AD", debug) // This statement will return nothing if qroups aren't defined. Hard to return an error message since some named instances can be members of a group and others may not be. def sqlQuery = """ sp_replmonitorhelppublication """ LMDebugPrint("\tSQL Instances ${sqlInstances}", debug) if (sqlInstances) { sqlInstances.toString().tokenize(",").each { instanceName -> LMDebugPrint("\tInstance Name ${instanceName}", debug) def jdbcConnectionString = hostProps.get("mssql.${instanceName.trim()}.mssql_url") ?: hostProps.get("auto.${instanceName.trim()}.mssql_url") def user = hostProps.get("mssql.${instanceName.trim()}.user") ?: hostProps.get("jdbc.mssql.user") ?: "" def pass = hostProps.get("mssql.${instanceName.trim()}.pass") ?: hostProps.get("jdbc.mssql.pass") ?: "" LMDebugPrint("\tJDBC Connection String ${jdbcConnectionString}", debug) // ensure we dont have any null's if (jdbcConnectionString) { def conn = attemptConnection(user, pass, jdbcConnectionString, debug) if (conn.status == "success") { def outputRecords = runQuery(sqlQuery, conn.connection, debug) if (outputRecords.status == 'success') { outputRecords.data.each { oneRecord -> name = oneRecord.publication.toString().replaceAll(/[:|\\|\s|=]+/,"_") publication_id = oneRecord.publication_id publication_type = oneRecord.publication_type retention = oneRecord.retention instanceName = instanceName.replaceAll(/[:|\\|\s|=]+/,"_") wildvalue = instanceName + "||" + name // Print it out with instance level properties. println "${wildvalue}##${name} (${instanceName})######" + "auto.sql_instance=${instanceName}&" + "auto.publication_name=${name}&" + "auto.publication_id=${publication_id}&" + "auto.publication_type=${publication_type}&" + "auto.retention=${retention}" } } } } } return 0 } else { return 1 } /** * Helper method to print out debug messages for troubleshooting purposes. * @param message * @param debug * @return */ def LMDebugPrint(message, Boolean debug = false) { if (debug) { println(message.toString()) } } /** * Helper method which handles creating a connection to the jdbc database * @returnArray is an array with a connection, status and any error messages an array. * *connection = jdbc connection * *status, success or fail * *errors, if both connection types fail there will be 2 error messages. * */ def attemptConnection(String instanceUser, String instancePass, String instanceURL, Boolean debug = false) { LMDebugPrint("**** Props to connect:", debug) LMDebugPrint("\tuser:$instanceUser", debug) LMDebugPrint("\tinstanceURL:$instanceURL", debug) def returnArray = [:] def errors = [] def connComplete def db_connection try { // Connection creation thrown into Try/Catch block as to quickly capture any issues with initial connection. // Create a connection to the database. String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver" db_connection = Sql.newInstance(instanceURL, instanceUser, instancePass, driver) connComplete = true } catch (Exception e) { // Print out the exception and exit with 1. errors[0] = e.message LMDebugPrint("***** Attempt Connection error: ${e.message}", debug) connComplete = false } // populate the connection and any messages for the return array if (connComplete == true) { returnArray['connection'] = db_connection returnArray['status'] = 'success' } else { returnArray['status'] = 'failed' returnArray['errors'] = errors returnArray['connection'] = null } return returnArray } /* Attempt to execute SQL command. Returns an array. the first element is the query data second element is success or fail third element is the error message */ def runQuery(String sqlQuery, conn, debug = false) { def returnArray = [:] LMDebugPrint("****Running Query****", debug) LMDebugPrint("\tQuery to run: $sqlQuery", debug) // query the Oracle DB. try { returnArray['data'] = conn.rows(sqlQuery) returnArray['status'] = 'success' returnArray['error'] = '' } catch (Exception e) { returnArray['error'] = e.message returnArray['status'] = 'failed' LMDebugPrint("\tSQL Query Error message: ${e.message}", debug) } LMDebugPrint("Data Returned: ${returnArray.data}", debug) return returnArray }

MoreDiscussions

SQL Replication Monitoring | Product Discussions | LogicMonitor Community