Collection the data metrics where there are unique keys

Do you want to add it as the display name of the instance, the description of the instance, or an instance level property of the instance?

Either way, you’ll need to add a section of code to your discovery script to make the query that returns the segment for each if the sysdatabases you’re discovering. Is there a single query you can run that will return the names of the databases along with the segment for each one (I’m assuming each database only has one segment)?

So, instead of running this query in your discovery:

select name from master..sysdatabases

Is there a query you can run that would return the sysdatabase name in the first column and the segment in the second?

each database has 3 segments

please check my dicovery script. it is running sucessfully but instances are not discovering.please make some changes

import groovy.sql.Sql
import java.sql.*
// Get basic info to connect
def hostname = hostProps.get("system.hostname")
def user = hostProps.get("sybase.user")
def pass = hostProps.get("sybase.pass")
def port = 21000

// Construct an SQL instance with a url and a driver
def url = "jdbc:sybase:Tds:$hostname:$port"
def driver = "com.sybase.jdbc4.jdbc.SybDriver"
def sql = Sql.newInstance(url, user, pass, driver)
def conn = sql.getConnection()

try {
    def stmt = conn.createStatement()

    // Retrieve database names and IDs
    def dbRs = stmt.executeQuery("select name, dbid from sysdatabases")

    while (dbRs.next()) {
        def dbName = dbRs.getString("name")
        def dbId = dbRs.getInt("dbid")

        // Retrieve segment information for each database
        def segmentRs = stmt.executeQuery("select name from '${dbName}'..syssegments where dbid = ${dbId}")

        while (segmentRs.next()) {
            def segmentName = segmentRs.getString("segment_name")
            def segment = segmentRs.getBytes("segment")

            // Process segment information and create instance
            def instanceName = "${dbName}_${segmentName}_segment"
            def instanceConfig = [segment: segment]
            createInstance(instanceName, instanceConfig)
        }

        segmentRs.close()
    }

    dbRs.close()
    stmt.close()
} catch (SQLException e) {
    // Handle SQL exception
    e.printStackTrace()
} finally {
    if (conn != null) {
        conn.close()
    }
}

// Function to create instance
def createInstance(name, config) {
    println("Creating instance ${name} with config ${config}")
    // Implementation of instance creation logic here
}

Ok, each db has three segments. 

You want your discovery output to have one line per instance you want discovered. You need to provide at least two terms for each instance: unique identifier and display name. You can include other terms as well.

Based on your script, it looks like you probably need the dbName and segmentName combined to form the unique identifier for the segment. Ok. Your function should at minimum look like this:

// Function to create instance
def createInstance(name, config) {
    println("${name}##${name}")
}

The first time `name` is in your println statement defines the unique id that will match up in the collection script (if BATCHSCRIPT) is used. It’s referred to as the WILDVALUE.

The second time `name` is in your println statement defines the display name of the instance.

You should be checking the Use Wildvalue as Unique Identifier checkbox (it should be the default, the reasons to have it unchecked are few). Having it checked means that you can make the display name anything you want as long as the wildvalue is unique. 

If you wanted to add a description or instance level properties (for grouping), your function would look something like this:

// Function to create instance
def createInstance(name, config) {
    description = config.something
    prop1 = config.something_else
    prop2 = config.something_else_entirely
    println("${name}##${name}##${description}####prop1name=${prop1value}&prop2name=${prop2value}")
}

I would also recommend making your display name something a little more readable. You don’t even have to have the db name in the display name. You could put the display name in one of the instance level properties and group by that property.

i have changed the code as mentioned below

import groovy.sql.Sql

// Get basic info to connect
def hostname = hostProps.get("system.hostname")
def user = hostProps.get("sybase.user")
def pass = hostProps.get("sybase.pass")
def port = 21000

// Construct an SQL instance with a URL and a driver
def url = "jdbc:sybase:Tds:$hostname:$port"
def driver = "com.sybase.jdbc4.jdbc.SybDriver"
def sql = Sql.newInstance(url, user, pass, driver)
def conn = sql.getConnection()

try {
    def stmt = conn.createStatement()

    // Retrieve database names and IDs
    def dbRs = stmt.executeQuery("select name, dbid from sysdatabases")

    while (dbRs.next()) {
        def dbName = dbRs.getString("name")
        def dbId = dbRs.getInt("dbid")

        // Retrieve segment information for each database
        def segmentRs = stmt.executeQuery("select name from '${dbName}'..syssegments where dbid = ${dbId}")

        while (segmentRs.next()) {
            def segmentName = segmentRs.getString("name")

            // Process segment information and create instance
            def instanceName = "${dbName}_${segmentName}_segment"
            def instanceConfig = [segment: segmentName]
            createInstance(instanceName, instanceConfig)
        }

        segmentRs.close()
    }

    dbRs.close()
    stmt.close()
} catch (e) {
    // Handle SQL exception
    e.printStackTrace()
} finally {
    if (conn != null) {
        conn.close()
    }
}

def createInstance(name, config) {
    def description = config.description
    def prop1 = config.prop1
    def prop2 = config.prop2

    println("${name}##${name}##${description}####prop1name=${prop1}&prop2name=${prop2}")
}

i am getting the following output

import groovy.sql.Sql
import java.sql.SQLException

// Get basic info to connect
def hostname = hostProps.get("system.hostname")
def user = hostProps.get("sybase.user")
def pass = hostProps.get("sybase.pass")
def port = 21000

// Construct an SQL instance with a URL and a driver
def url = "jdbc:sybase:Tds:$hostname:$port"
def driver = "com.sybase.jdbc4.jdbc.SybDriver"
def sql = Sql.newInstance(url, user, pass, driver)
def conn = sql.getConnection()

try {
    def stmt = conn.createStatement()

    // Retrieve database names
    def dbRs = stmt.executeQuery("select name from master..sysdatabases")

    while (dbRs.next()) {
        def dbName = dbRs.getString("name")

        // Construct a new SQL instance for each database
        def dbUrl = "jdbc:sybase:Tds:$hostname:$port?ServiceName=$dbName"
        def dbSql = Sql.newInstance(dbUrl, user, pass, driver)
        def dbConn = dbSql.getConnection()

        try {
            def dbStmt = dbConn.createStatement()

            // Retrieve segment names for the current database
            def segmentRs = dbStmt.executeQuery("select name from syssegments")

            while (segmentRs.next()) {
                def segmentName = segmentRs.getString("name")

                // Process segment information and create instance
                def instanceName = "${dbName}_${segmentName}_segment"
                def instanceConfig = [
                    "segment": segmentName,
                    "description": "Instance for segment: ${segmentName}",
                    "prop1name": "prop1value",
                    "prop2name": "prop2value"
                ]

                createInstance(instanceName, instanceConfig)
            }

            segmentRs.close()
            dbStmt.close()
        } catch (SQLException e) {
            // Handle SQL exception
            e.printStackTrace()
        } finally {
            if (dbConn != null) {
                dbConn.close()
            }
        }
    }

    dbRs.close()
    stmt.close()
} catch (SQLException e) {
    // Handle SQL exception
    e.printStackTrace()
} finally {
    if (conn != null) {
        conn.close()
    }

}

def createInstance(name, config) {
    def description = config.description
    def prop1 = config.prop1name
    def prop2 = config.prop2name

    println("${name}##${name}##${description}####prop1name=${prop1}&prop2name=${prop2}")
}

i got the out put as desired 

the problem here is how to pass this instance as wild value in the second script. say if my second  script is below  

import groovy.sql.Sql
//@Grab('org.hsqldb:hsqldb:2.7.1:jdk8')
//@GrabConfig(systemClassLoader=true)
Class.forName("com.sybase.jdbc4.jdbc.SybDriver")

// Get basic info to connect
def hostname = hostProps.get("system.hostname")
def user = hostProps.get("sybase.user")
def pass = hostProps.get("sybase.pass")
def port = 21000
def wildvalue = instanceProps.get("wildvalue")
def segmentWildcard = "${wildvalue}"
// Construct an SQL instance with a url and a driver
def url = "jdbc:sybase:Tds:${hostname}:${port}"
def sql = Sql.newInstance(url, user, pass, "com.sybase.jdbc4.jdbc.SybDriver")

sql.eachRow("sp_helpsegment '${segmentWildcard}'") { row ->
    println row
}

sql.close()

please check the above as data is not polling

I suggest the following simpler version of your discovery script:

import groovy.sql.Sql
import java.sql.SQLException

def hostname = hostProps.get("system.hostname")
def user = hostProps.get("sybase.user")
def pass = hostProps.get("sybase.pass")
def port = 21000

def url = "jdbc:sybase:Tds:$hostname:$port"
def driver = "com.sybase.jdbc4.jdbc.SybDriver"
def sql = Sql.newInstance(url, user, pass, driver)
def conn = sql.getConnection()

try {
    def stmt = conn.createStatement()
    def dbRs = stmt.executeQuery("select name from master..sysdatabases") // get the databases on the host
    while (dbRs.next()) { // loop through each database on the host
        def dbName = dbRs.getString("name")
        // connect to the service
        def dbUrl = "jdbc:sybase:Tds:$hostname:$port?ServiceName=$dbName"
        def dbSql = Sql.newInstance(dbUrl, user, pass, driver)
        def dbConn = dbSql.getConnection()
        try {
            def dbStmt = dbConn.createStatement()
            def segmentRs = dbStmt.executeQuery("select name from syssegments") // Retrieve segment names for the current database
            while (segmentRs.next()) { // loop through each segment
                def segmentName = segmentRs.getString("name")
                println("${dbName}_${segmentName}") // output the instance
            }
        }
        segmentRs.close()
        dbStmt.close()
    }
    catch (SQLException e) {e.printStackTrace()}
    finally {
        if (dbConn) {dbConn.close()}}
        if (dbRs) {dbRs.close()}
        if (stmt) {stmt.close()}
}
catch (SQLException e) {e.printStackTrace()}
finally {if (conn != null) {conn.close()}}

To get the same names into your collection script, you’d just repeat what you’ve already done in your discovery script, just output the data instead of just the names. This requires changing back to BATCHSCRIPT, since you’d be outputting all the data with one run of your collection script. I’m making a lot of guesses here since I don’t really understand your data. You may need to adjust this so that things match up.

import groovy.sql.Sql
import java.sql.SQLException

def hostname = hostProps.get("system.hostname")
def user = hostProps.get("sybase.user")
def pass = hostProps.get("sybase.pass")
def port = 21000

def url = "jdbc:sybase:Tds:$hostname:$port"
def driver = "com.sybase.jdbc4.jdbc.SybDriver"
def sql = Sql.newInstance(url, user, pass, driver)
def conn = sql.getConnection()

try {
    def stmt = conn.createStatement()
    def dbRs = stmt.executeQuery("select name from master..sysdatabases") // get the databases on the host
    while (dbRs.next()) { // loop through each database on the host
        def dbName = dbRs.getString("name")
        // connect to the service
        def dbUrl = "jdbc:sybase:Tds:$hostname:$port?ServiceName=$dbName"
        def dbSql = Sql.newInstance(dbUrl, user, pass, driver)
        def dbConn = dbSql.getConnection()
        try {
            def dbStmt = dbConn.createStatement()
            def segmentRs = dbStmt.executeQuery("select name from syssegments") // Retrieve segment names for the current database
            while (segmentRs.next()) { // loop through each segment
                def segmentName = segmentRs.getString("name")
                // println("${dbName}_${segmentName}") // output the instance
                dbSql.eachRow("sp_helpsegment '${segmentName}'") { row -> //not sure how this query needs to be contructed, this is a guess (did you create a function?)
                    datapoint = row.tokenize(":. ")[2] // take the row "master:system.Size: 50.0", split it wherever there is a `:`, a `.`, or a ` ` (space) and grab the third item from that set "Size"
                    value = row.tokenize(":. ")[3]     // take the row "master:system.Size: 50.0", split it wherever there is a `:`, a `.`, or a ` ` (space) and grab the fourth item from that set "50"
                    println("${dbName}_${segmentName}.${datapoint}: ${value}") // print out the data: `BTS_PROD_system_segment.Size: 50`
                }
            }
        }
        segmentRs.close()
        dbStmt.close()
    }
    catch (SQLException e) {e.printStackTrace()}
    finally {
        if (dbConn) {dbConn.close()}}
        if (dbRs) {dbRs.close()}
        if (stmt) {stmt.close()}
}
catch (SQLException e) {e.printStackTrace()}
finally {if (conn != null) {conn.close()}}

Notice how the only real difference between the two is what gets output for each segment in the `while (segmentRs.next()) loop. In discovery, you output the instance naming details. In collection you output the wildvalue, plus the datapoint name, plus the value.

As i got my out put as required, cant’ i poll data with the old one?

Oh, I misunderstood. I thought you said the data was not polling.