Batch script

Active Discovery-Discovery Method Script
==============================================================================================================================================
import groovy.sql.Sql

def hostname = hostProps.get("system.hostname")
def sqlInstances = hostProps.get("mssql.sql_server_instances") ?: hostProps.get("auto.sql_server_instances")

if (sqlInstances) {
    def executor = java.util.concurrent.Executors.newFixedThreadPool(sqlInstances.size().toInteger())

    sqlInstances.toString().tokenize(",").each { instanceName ->
        executor.submit({
            instanceName = instanceName.replaceAll(/[:|\\|\s|=]+/, "_")
            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") ?: ""

            if (jdbcConnectionString) {
                def sql = Sql.newInstance(jdbcConnectionString, user, pass, "com.microsoft.sqlserver.jdbc.SQLServerDriver")

                try {
                    def query1 = """
                     
					 SQL QUERY
					 
                    """

                    def resultSet = sql.rows(query1)

                    if (resultSet) {
                        resultSet.each { row ->
                            def x = row.get("database_name")
                            def y = row.get("BackupType")
                            def z = row.get("bkSize")
                            def c = row.get("TimeTaken")
                            def a = row.get("physical_device_name")
                            def b = row.get("recovery_model")
                            def p = row.get("server_name")
                            def m = row.get("Is_Native_Backup")

                            println("${a}##${a}######instance=${instanceName}&DB=${x}&BackupType=${y}&bkSize=${z}&TimeTaken=${c}&physical_device_name=${a}&recovery_model=${b}&&server_name=${p}&BackupLocation=${m}")


                            
                        }
                    } else {
                        println("Result set is null or empty")
                    }
                } catch (Exception e) {
                    println("Exception during sql.rows execution: ${e.message}")
                    e.printStackTrace()
                } finally {
                    sql.close()
                    println("Finally block executed")
                }
            }
        } as java.util.concurrent.Callable)
    }

    executor.shutdown()
    executor.awaitTermination(20, java.util.concurrent.TimeUnit.SECONDS)
}

return 0

============================================================================================================================================
Collection script-Batch Method
============================================================================================================================================
import groovy.sql.Sql

def hostname = hostProps.get("system.hostname")
def sqlInstances = hostProps.get("mssql.sql_server_instances") ?: hostProps.get("auto.sql_server_instances")

if (sqlInstances) {
    sqlInstances.toString().tokenize(",").each { currentInstance ->
        currentInstance = currentInstance.replaceAll(/[:|\\|\s|=]+/, "_")

        def jdbcConnectionString = hostProps.get("mssql.${currentInstance.trim()}.mssql_url") ?: hostProps.get("auto.${currentInstance.trim()}.mssql_url")
        def user = hostProps.get("mssql.${currentInstance.trim()}.user") ?: hostProps.get("jdbc.mssql.user") ?: ""
        def pass = hostProps.get("mssql.${currentInstance.trim()}.pass") ?: hostProps.get("jdbc.mssql.pass") ?: ""

        if (jdbcConnectionString) {
            def sql = Sql.newInstance(jdbcConnectionString, user, pass, "com.microsoft.sqlserver.jdbc.SQLServerDriver")

            try {
                def initialQuery = """
                ( same SQL used in Discovery)
                """

                def resultSet = sql.rows(initialQuery)
                def deviceNamesList = resultSet.collect { it.get("physical_device_name") }
                //.replaceAll("\\\\", "") }

                if (deviceNamesList) {
                    deviceNamesList.each { deviceName ->
                        def secondaryQuery = """
                        ( same SQL used in Discovery)
                        """

                        def secondaryResultSet = sql.rows(secondaryQuery)

                        if (secondaryResultSet) {
                            secondaryResultSet.each { row ->
                                def databaseName = row.get("database_name")
                                def backupType = row.get("BackupType")
                                def backupSize = row.get("bkSize").replaceAll(/\D/, '')
                                def timeTaken = row.get("TimeTaken").replaceAll(/\D/, '')
                                def recoveryModel = row.get("recovery_model")
                                def isNativeBackup = row.get("Is_Native_Backup")
                                def M = row.get("physical_device_name")
                                //.replaceAll("\\\\", "")
                                
                                 recoveryModel = (recoveryModel == 'simple') ? '1' : (recoveryModel == 'full') ? '2' : (recoveryModel == 'bulk_logged') ? '3' : recoveryModel
                                 
                                if (deviceName == M) {
                                def wildvalue = deviceName  

                                    //println("${wildvalue}.Device Name: ${deviceName}")
                                    //println("${wildvalue}.Database Name: ${databaseName}")
                                    println("${wildvalue}.BackupType:${backupType}")
                                    println("${wildvalue}.BackupSize: ${backupSize}")
                                    println("${wildvalue}.TimeTaken: ${timeTaken}")
                                    println("${wildvalue}.RecoveryModel: ${recoveryModel}")
                                    println("${wildvalue}.Native_Backup: ${isNativeBackup}")
                                }
                            }
                        }
                    }
                }

            } finally {
                sql.close()
            }
        } else {
            println("JDBC Connection String not found for $currentInstance")
        }
    }
} else {
    println("No SQL instances found")
}

return 0