SQL Replication Monitoring

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
}