Collection the data metrics where there are unique keys

Ok, non-numeric data can’t be fetched as part of collection. However, you could add the cmd string to the instance level properties. so that you have that information available in LM. You’d do that by making your discovery output look like this:

SPID1##SPID 1######cmd=cmdofSPID1
SPID3##SPID 3######cmd=cmdofSPID3
SPID4##SPID 4######cmd=cmdofSPID4
SPID5##SPID 5######cmd=cmdofSPID5
SPID6##SPID 6######cmd=cmdofSPID6
SPID7##SPID 7######cmd=cmdofSPID7
SPID8##SPID 8######cmd=cmdofSPID8
SPID9##SPID 9######cmd=cmdofSPID9
SPID10##SPID 10######cmd=cmdofSPID10
SPID11##SPID 11######cmd=cmdofSPID11
SPID12##SPID 12######cmd=cmdofSPID12
SPID13##SPID 13######cmd=cmdofSPID13
SPID14##SPID 14######cmd=cmdofSPID14
etc. etc.

Where cmdofSPID1, cmdofSPID3, etc. is just the string from the cmd column.

As for the time_blocked, you’d need to convert that to an integer or floating point decimal (HH:MM is not a number, it’s a string). So, your collection script would look like this:

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 dbname = hostProps.get("sybase.dbname")

// Construct an SQL instance with a url and a driver
def url = "jdbc:sybase:Tds:${hostname}:${port}", username=user, password=pass

// Sybase SQL Driver
def driver = "com.sybase.jdbc4.jdbc.SybDriver"

// Create a connection to the SQL server
def sql = Sql.newInstance(url, user, pass, driver)

def results = []

sql.withTransaction {
    // ADDED time_blocked to the sql query here so that it's in your results.
    results = sql.rows("select spid, time_blocked, blocked from master..sysprocesses WHERE db_name(dbid)=${dbname}")
}

sql.close()

// Format the results as key-value pairs
results.each { row ->
    println("${row.spid}.BLOCKED: ${row.blocked}") // use the println statement. 
    // add a second println statement for the second datapoint on this SPID. This datapoint is called TIME_BLOCKED
    (h,m) = row.time_blocked.tokenize(":")
    time_blocked = h.toInteger()*100 + m.toInteger()
    println("${row.spid}.TIME_BLOCKED: ${time_blocked}")
    // The datapoint definition would be a multi-line key-value pair just like the blocked datapoint
    // The key would be ##WILDVALUE##.TIME_BLOCKED
}

return 0

I added time_blocked to your SQL statement as you probably have already done.

I changed your each loop to just a basic for..each loop and used simple print statements.

I added 3 lines to the for..each loop to grab the hours and minutes from the time_blocked string, convert to an integer and put it all together as an integer. Then I printed out the line that gives the data to LM to put in the time_blocked datapoint.

Also, since you’re discovering the SPIDs as instances, the wildvalue isn’t the name of the database anymore. I assume there’s just one db name. If there’s more, we can discuss it. Instead of pulling the db name from the wildvalue (which doesn’t contain it anymore), i added it as a host property. You’ll need to add the dbname as a host property to the host (and probably apply it to the appliesto since the DS won’t work without it).