Collection the data metrics where there are unique keys


Userlevel 1
Badge +2

SPID = 1 BLOCKED = 0
SPID = 3 BLOCKED = 0
SPID = 4 BLOCKED = 0
SPID = 5 BLOCKED = 0
SPID = 6 BLOCKED = 0
SPID = 7 BLOCKED = 0
SPID = 8 BLOCKED = 0
SPID = 9 BLOCKED = 0
SPID = 10 BLOCKED = 0
SPID = 11 BLOCKED = 0
SPID = 12 BLOCKED = 0
SPID = 13 BLOCKED = 0
SPID = 14 BLOCKED = 0
SPID = 17 BLOCKED = 0
SPID = 37 BLOCKED = 0
SPID = 60 BLOCKED = 0
SPID = 107 BLOCKED = 0
SPID = 147 BLOCKED = 0
SPID = 165 BLOCKED = 0
SPID = 183 BLOCKED = 0
SPID = 251 BLOCKED = 0
SPID = 273 BLOCKED = 0
SPID = 319 BLOCKED = 0
SPID = 430 BLOCKED = 0
SPID = 435 BLOCKED = 0
SPID = 466 BLOCKED = 0
SPID = 470 BLOCKED = 0
SPID = 494 BLOCKED = 0
SPID = 522 BLOCKED = 0
SPID = 539 BLOCKED = 0
SPID = 555 BLOCKED = 0
SPID = 584 BLOCKED = 0
SPID = 596 BLOCKED = 0
SPID = 600 BLOCKED = 0
SPID = 651 BLOCKED = 0
SPID = 673 BLOCKED = 0

 

i want to capture the above data, the above data is my querry result. please tell me the data point settings 

 

58 replies

Userlevel 1
Badge +2

please help

Userlevel 7
Badge +17

You’re going to need to provide more information. Is this a batchscript datasource? What does your discovery look like? 

Your output will need to change if it’s batchscript

It’ll probably need to look something like:

SPID651.blocked: 0

SPID673.blocked: 0

This makes assumptions since you didn’t provide any of the required details. Assuming you want separate instances for each line in your output and assuming blocked is the single datapoint you want to store for each of these instances.

Userlevel 1
Badge +2

 

Userlevel 1
Badge +2

 

Userlevel 1
Badge +2

i just wanted to store all spid and blocked info

Userlevel 1
Badge +2

if i go like this 

SPID651.blocked: 0

SPID673.blocked: 0

i cant predifne SPID651 in datapoints , as there was no gaurantee that this is going to be in the table

Userlevel 7
Badge +17

You still didn’t answer any of the questions. Batchscript? What discovery are you doing?

At any rate, you’re thinking about it wrong. SPID651 is not data. It’s the identifier. So you should have a discovery script that creates the instances based on the identifiers, then your collection script outputs the data with the identifier and datapoint name before the colon and the data after the colon.

Userlevel 1
Badge +2

import groovy.sql.Sql
//@Grab('org.hsqldb:hsqldb:2.7.1:jdk8')
//@GrabConfig(systemClassLoader=true)
//@Grab("com.sybase.jdbc4.jdbc.SybDriver")
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##:21000", username=user, password=pass
//def url = "jdbc:sybase:Tds:hostname:port", "user", "pass"

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

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

// Iterate over query results and list the databases
sql.eachRow("select name from master..sysdatabases")
        { 
            name= it.toString().split(":")[1].replace("]","")
            // Create an instance for each database
            println name+"##"+name
        }
    
   }
sql.close()        
return 0
 

Userlevel 1
Badge +2

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

Userlevel 1
Badge +2

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

Userlevel 7
Badge +17

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.

Userlevel 1
Badge +2

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

 

Userlevel 7
Badge +17

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

Userlevel 1
Badge +2

yes cmd is not numeric but time_blocked is time format

Userlevel 7
Badge +17

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

Userlevel 1
Badge +2

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

 

 

Userlevel 7
Badge +17

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.
Userlevel 1
Badge +2

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

Userlevel 7
Badge +17

Is row.time_blocked in the output of your query?

Userlevel 1
Badge +2

yes, null value as it will give time when it gets blocked only

Userlevel 1
Badge +2

 

Userlevel 1
Badge +2

 

Userlevel 1
Badge +2

sure  i will try to workout and get back to you

 

Userlevel 1
Badge +2

 

Userlevel 1
Badge +2

 

Reply