Collection the data metrics where there are unique keys

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

// 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 {
    // Iterate over query results and list the databases
    def wildvalue = instanceProps.get("wildvalue")
    results = sql.rows("select spid,  blocked from master..sysprocesses WHERE db_name(dbid)=${wildvalue}")
}

sql.close()

// Format the results as key-value pairs
def resultParsed = results.collect { row ->
    "SPID = ${row.spid}\nBLOCKED = ${row.blocked}\n"
}

// Join the results with newlines and print the output
println resultParsed.join("\n")

return 0

As per my i understanding i shall the discovery method as SPID instead of Database name.?

Piecing together what you’re trying to do here. You want to know how many blocked there are per SPID, right? Then yes, your discovery should be outputting something like this:

SPID1##SPID 1
SPID3##SPID 3
SPID4##SPID 4
SPID5##SPID 5
SPID6##SPID 6
SPID7##SPID 7
SPID8##SPID 8
SPID9##SPID 9
SPID10##SPID 10
SPID11##SPID 11
SPID12##SPID 12
SPID13##SPID 13
SPID14##SPID 14
etc. etc.

Your collection script is pretty close to what you eventually want. Instead of

"SPID = ${row.spid}\nBLOCKED = ${row.blocked}\n"

You would have this:

println("SPID${row.spid}.BLOCKED: ${row.blocked}")

Your datapoint would be defined as a multi-line key-value pair and the key would be:

##WILDVALUE##.BLOCKED

When your collection script outputs this:

SPID1.BLOCKED: 0
SPID3.BLOCKED: 0
SPID4.BLOCKED: 0
SPID5.BLOCKED: 0
SPID6.BLOCKED: 0
SPID7.BLOCKED: 0
SPID8.BLOCKED: 0
SPID9.BLOCKED: 0
SPID10.BLOCKED: 0
SPID11.BLOCKED: 0
SPID12.BLOCKED: 0
SPID13.BLOCKED: 0
SPID14.BLOCKED: 0
etc. etc.

LM will automatically take care of matching up the lines in your collection output with the lines in your discovery output based on your datapoint key. The key dictates that LM should look for lines in your collection output that have the WILDVALUE, then a dot, then the word “BLOCKED”, then a colon, then a number. If the WILDVALUE of your collection script output line matches the WILDVALUE of a line in the discovery script, then the data will be matched up and added to that instance.

ok, what if i am querrying select spid, cmd, blocked, time_blocked from master..sysprocesses

Then i will get my collection script would be 

SPID1.BLOCKED: 0.CMD:tunning.time_blocked:18:52 ? 

is it right

Are cmd and time_blocked additional numeric datapoints about SPID1 (and each other SPID)?

yes cmd is not numeric but time_blocked is time format

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).

results = sql.rows("select spid, time_blocked, blocked from master..sysprocesses WHERE db_name(dbid)=${dbname}") }. our instance here is spid , can  i change to spid   ie.. as mentioned below

results = sql.rows("select spid, time_blocked, blocked from master..sysprocesses WHERE spid =${wildvalue}")

do i need to create datasource for CMD & TIME_bloked? if what are the properties for them

You do not need to have the SPID in the WHERE statement of your sql query. You could, but you’d have to change from BATCHSCRIPT to SCRIPT, which would spawn an individual task on the collector for each SPID and you don’t want to do that.

BATCHSCRIPT lets you execute one query and return all the data at once, for all the instances. I assumed you needed the database name in your SQL query to return the right SPIDs. If you don’t need that, don’t have a where statement at all. Just query all the SPIDs and return all the data for all the SPIDs.

You don’t need a different datasource for cmd and time_blocked. If they were both numerical, you’d have separate datapoints. So you’d have a datapoint for blocked, a datapoint for cmd, and a datapoint for time_blocked.

• Blocked is already numerical, so you’re good there.
• cmd is not numerical, so you can’t put it in a datapoint. The best you could do would be to put it in an instance level property (which could be used for grouping if you wanted).
• time_blocked is almost numerical. In the script above, I coded how you could translate the time_blocked to a numerical value that could then be stored in the datapoint.

The script failed, elapsed time: 0 seconds - Cannot invoke method tokenize() on null object
java.lang.NullPointerException: Cannot invoke method tokenize() on null object
    at org.codehaus.groovy.runtime.NullObject.invokeMethod(NullObject.java:91)
    at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.call(PogoMetaClassSite.java:47)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
    at org.codehaus.groovy.runtime.callsite.NullCallSite.call(NullCallSite.java:34)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:128)
    at Script1309$_run_closure2.doCall(Script1309.groovy:36)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorIm