Collection the data metrics where there are unique keys | Product Discussions

manthena2020 · 2023-05-03T10:00:12Z

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

LM User

·3 years ago

Please start a new thread with this question.

manthena2020

OP3 years ago

hi , i have the follwing path

To read log file using event source can you please tell me how to fill the logfielpath using above path

LM User

·3 years ago

Yeah, you should avoid the space. You can do a .replaceAll(“ “,””) on that portion of the string to make sure it doesn’t have any spaces in it.

manthena2020

OP3 years ago

i am have space in one of the output ( recv sleep). i have issue to capture that datapoint. when i give space its not pulling data

manthena2020

OP3 years ago

Thanks bro problem solved. I have used ‘-’ instead of : . problem solved

Thanks

Bye bro

LM User

·3 years ago

https://www.logicmonitor.com/support/logicmodules/datasources/active-discovery/script-active-discovery#:~:text=Wildvalue%20cannot%20contain%20the%20following%20characters%3A%20%3D%2C%20%3A%2C%20%5C%2C%20%23%2C%20space

manthena2020

OP3 years ago

Just for clarification can we use ‘-’ instead of _ As i have split issues with _.

LM User

·3 years ago

Your output must change since `:` is not allowed in the wildvalue. This is why I suggested the script updates.

If your wildvalue were “BTS_PROD_system” your output would be “BTS_PROD_system.Size: 21260” so your datapoint key for Size would be `##WILDVALUE##.Size`.

manthena2020

OP3 years ago

As my team already ok , with the out put we recived earlier

can you please tell me how to capture data in data poll

i have tried different option

please look into it

Thanks

LM User

·3 years ago

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

manthena2020

OP3 years ago

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

LM User

·3 years ago

I suggest the following simpler version of your discovery script:

import groovy.sql.Sql<br/>import java.sql.SQLException<br/><br/>def hostname = hostProps.get("system.hostname")<br/>def user = hostProps.get("sybase.user")<br/>def pass = hostProps.get("sybase.pass")<br/>def port = 21000<br/><br/>def url = "jdbc:sybase:Tds:$hostname:$port"<br/>def driver = "com.sybase.jdbc4.jdbc.SybDriver"<br/>def sql = Sql.newInstance(url, user, pass, driver)<br/>def conn = sql.getConnection()<br/><br/>try {<br/>    def stmt = conn.createStatement()<br/>    def dbRs = stmt.executeQuery("select name from master..sysdatabases") // get the databases on the host<br/>    while (dbRs.next()) { // loop through each database on the host<br/>        def dbName = dbRs.getString("name")<br/>        // connect to the service<br/>        def dbUrl = "jdbc:sybase:Tds:$hostname:$port?ServiceName=$dbName"<br/>        def dbSql = Sql.newInstance(dbUrl, user, pass, driver)<br/>        def dbConn = dbSql.getConnection()<br/>        try {<br/>            def dbStmt = dbConn.createStatement()<br/>            def segmentRs = dbStmt.executeQuery("select name from syssegments") // Retrieve segment names for the current database<br/>            while (segmentRs.next()) { // loop through each segment<br/>                def segmentName = segmentRs.getString("name")<br/>                println("${dbName}_${segmentName}") // output the instance<br/>            }<br/>        }<br/>        segmentRs.close()<br/>        dbStmt.close()<br/>    }<br/>    catch (SQLException e) {e.printStackTrace()}<br/>    finally {<br/>        if (dbConn) {dbConn.close()}}<br/>        if (dbRs) {dbRs.close()}<br/>        if (stmt) {stmt.close()}<br/>}<br/>catch (SQLException e) {e.printStackTrace()}<br/>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<br/>import java.sql.SQLException<br/><br/>def hostname = hostProps.get("system.hostname")<br/>def user = hostProps.get("sybase.user")<br/>def pass = hostProps.get("sybase.pass")<br/>def port = 21000<br/><br/>def url = "jdbc:sybase:Tds:$hostname:$port"<br/>def driver = "com.sybase.jdbc4.jdbc.SybDriver"<br/>def sql = Sql.newInstance(url, user, pass, driver)<br/>def conn = sql.getConnection()<br/><br/>try {<br/>    def stmt = conn.createStatement()<br/>    def dbRs = stmt.executeQuery("select name from master..sysdatabases") // get the databases on the host<br/>    while (dbRs.next()) { // loop through each database on the host<br/>        def dbName = dbRs.getString("name")<br/>        // connect to the service<br/>        def dbUrl = "jdbc:sybase:Tds:$hostname:$port?ServiceName=$dbName"<br/>        def dbSql = Sql.newInstance(dbUrl, user, pass, driver)<br/>        def dbConn = dbSql.getConnection()<br/>        try {<br/>            def dbStmt = dbConn.createStatement()<br/>            def segmentRs = dbStmt.executeQuery("select name from syssegments") // Retrieve segment names for the current database<br/>            while (segmentRs.next()) { // loop through each segment<br/>                def segmentName = segmentRs.getString("name")<br/>                // println("${dbName}_${segmentName}") // output the instance<br/>                dbSql.eachRow("sp_helpsegment '${segmentName}'") { row -> //not sure how this query needs to be contructed, this is a guess (did you create a function?)<br/>                    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"<br/>                    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"<br/>                    println("${dbName}_${segmentName}.${datapoint}: ${value}") // print out the data: `BTS_PROD_system_segment.Size: 50`<br/>                }<br/>            }<br/>        }<br/>        segmentRs.close()<br/>        dbStmt.close()<br/>    }<br/>    catch (SQLException e) {e.printStackTrace()}<br/>    finally {<br/>        if (dbConn) {dbConn.close()}}<br/>        if (dbRs) {dbRs.close()}<br/>        if (stmt) {stmt.close()}<br/>}<br/>catch (SQLException e) {e.printStackTrace()}<br/>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.

manthena2020

OP3 years ago

please check the above as data is not polling

manthena2020

OP3 years ago

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

manthena2020

OP3 years ago

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

LM User

·3 years ago

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<br/>def createInstance(name, config) {<br/>    println("${name}##${name}")<br/>}

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<br/>def createInstance(name, config) {<br/>    description = config.something<br/>    prop1 = config.something_else<br/>    prop2 = config.something_else_entirely<br/>    println("${name}##${name}##${description}####prop1name=${prop1value}&prop2name=${prop2value}")<br/>}

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.

manthena2020

OP3 years ago

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
}

manthena2020

OP3 years ago

each database has 3 segments

LM User

·3 years ago

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?

manthena2020

OP3 years ago

actually, Iam think of to add segment as discovery. any idea of that

LM User

·3 years ago

You should make this a “script”, since your collection script is designed to only poll one instance’s worth of data.

In your collection script, your datapoint keys should not have spaces in them, so I’d update line 28 in your collection script to this:

def result = sql.rows("select s.name Segment, cast(sum((size*$alloc)/1048576) as decimal(38,10)) 'size_in_MB', cast(sum((size*$alloc)/1048576)-sum((curunreservedpgs(db_id('${wildvalue}'),lstart, unreservedpgs)* $alloc)/1048576) as decimal(38,10)) 'Used_in_MB', cast(sum((curunreservedpgs(db_id('${wildvalue}'),lstart, unreservedpgs)* $alloc)/1048576) as decimal(38,10)) 'Free_in_MB' from master..sysusages u, syssegments s where u.dbid=db_id('${wildvalue}') and (segmap & power(2, segment))=power(2,segment) group by s.name")

You’ll also need to figure out a way to transform the “Segment” into a number since the collection script can only provide numbers to the DataSource. You could modify your discovery script to add the segment as part of the instance definition, either the description or an instance level property. I’d work on getting your collection working before attempting that.

manthena2020

OP3 years ago

hi please find the above codes and output. here i want to make segments as my instances. please tell me how to do.

manthena2020

OP3 years ago

manthena2020

OP3 years ago

LM User

·3 years ago

This is the important part of the error:

No signature of method: java.lang.Integer.getAt() is applicable for argument types: (java.lang.Integer) values: [0]Possible solutions: getAt(java.lang.String)

This means that the .toInteger() method (of which java.lang.Integer.getAt() is a sub-method) requires input of an object of a certain type. You passed in an object that could not be converted to an integer. This means that either h or m doesn’t contain a string that can be converted to an integer. .toInteger() needs a string to convert to an integer. It looks like you’re passing in either a “0” (integer) or [0] (list). I can’t tell which one because java debugging requires a higher state of consciousness to decipher.

If you’re still using the script from here, the issue is in the “toInteger()” method, which is happening at this point in your script. Here are some suggestions on how to troubleshoot it.

// get the values of hours and minutes from time_blocked. time_blocked will be something like 18:53<br/>// so h should end up being 18 (in this case)<br/>// and m should end up being 53 (in this case)<br/>(h,m) = row.time_blocked.split(":")<br/><br/><br/>// put a println statement here to see what h and m are to make sure they are convertable to integers<br/>println("h=${h}\nm=${m}")<br/><br/><br/>// if h and m look like integers here, then you shouldn't have any problems converting them to integers.<br/>// if this line still doesn't work, it may be that there are extra spaces. In that case, use .trim() to remove any extra spaces<br/>time_blocked = h.trim().toInteger()*60 + m.trim().toInteger()<br/><br/>// You can also split these lines up into their individual steps to see which one is failing<br/>intHours = h.toInteger()<br/>intMinutes = m.toInteger()<br/>time_blocked = intHours * 60 + intMinutes<br/><br/>// if h and m look like this:<br/>// h=[18]<br/>// m=[53]<br/>// then you don't have strings, you have a list of strings. You'd need to extract the elements from the list:<br/>time_blocked = h[0].toInteger()*60 + m[0].toInteger()

Give some of these suggestions a try and see what you get. Check the output of the println statement to see if you’re getting valid values from the .split() method.

There are other ways to parse a string into an integer, but your first order of business is to find out what h and m contain after the .split() happens.

manthena2020

OP3 years ago

hi, iam getting error related to time blocked, can you please check

manthena2020

OP3 years ago

The script failed, elapsed time: 0 seconds - No signature of method: java.lang.Integer.getAt() is applicable for argument types: (java.lang.Integer) values: [0]Possible solutions: getAt(java.lang.String), next(), putAt(java.lang.String, java.lang.Object), wait(), grep(), getClass()groovy.lang.MissingMethodException: No signature of method: java.lang.Integer.getAt() is applicable for argument types: (java.lang.Integer) values: [0]Possible solutions: getAt(java.lang.String), next(), putAt(java.lang.String, java.lang.Object), wait(), grep(), getClass()	at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:71)	at org.codehaus.groovy.runtime.callsite.PojoMetaClassSite.call(PojoMetaClassSite.java:48)	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:128)	at Script4977$_run_closure1$_closure2.doCall(Script4977.groovy:37)	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(DelegatingMethodAccessorImpl.java:43)	at java.base/java.lang.reflect.Method.invoke(Method.java:566)	at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:98)	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325)	at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:264)	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1034)	at groovy.lang.Closure.call(Closure.java:418)	at groovy.lang.Closure.call(Closure.java:434)	at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2125)	at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2110)	at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2151)	at org.codehaus.groovy.runtime.dgm$163.invoke(Unknown Source)	at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:274)	at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:56)	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:128)	at Script4977$_run_closure1.doCall(Script4977.groovy:33)	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(DelegatingMethodAccessorImpl.java:43)	at java.base/java.lang.reflect.Method.invoke(Method.java:566)	at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:98)	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325)	at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:264)	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1034)	at groovy.lang.Closure.call(Closure.java:418)	at groovy.lang.Closure.call(Closure.java:434)	at groovy.sql.Sql.callClosurePossiblyWithConnection(Sql.java:4396)	at groovy.sql.Sql.withTransaction(Sql.java:3586)	at groovy.sql.Sql$withTransaction.call(Unknown Source)	at Script4977.run(Script4977.groovy:24)

manthena2020

OP3 years ago

As of now , its ok . I need to go for TEST and see. Thanks soo much

LM User

·3 years ago

So, you’re good? All working as expected?

manthena2020

OP3 years ago

Thanks , bro

manthena2020

OP3 years ago

yes, time_blocked is comming as null

LM User

·3 years ago

What does your raw collection script output look like? If blocked is 0, time_blocked should be null, right?

manthena2020

OP3 years ago

HI, now the block value is comming and Date is comming as null. i will try to create a block in DB and see weather iam getting date also. Thanks Bro

manthena2020

OP3 years ago

manthena2020

OP3 years ago

sure i will try to workout and get back to you

manthena2020

OP3 years ago

manthena2020

OP3 years ago

manthena2020

OP3 years ago

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

LM User

·3 years ago

Is row.time_blocked in the output of your query?

manthena2020

OP3 years ago

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

LM User

·3 years ago

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.

manthena2020

OP3 years ago

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

LM User

·3 years ago

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<br/>SPID3##SPID 3######cmd=cmdofSPID3<br/>SPID4##SPID 4######cmd=cmdofSPID4<br/>SPID5##SPID 5######cmd=cmdofSPID5<br/>SPID6##SPID 6######cmd=cmdofSPID6<br/>SPID7##SPID 7######cmd=cmdofSPID7<br/>SPID8##SPID 8######cmd=cmdofSPID8<br/>SPID9##SPID 9######cmd=cmdofSPID9<br/>SPID10##SPID 10######cmd=cmdofSPID10<br/>SPID11##SPID 11######cmd=cmdofSPID11<br/>SPID12##SPID 12######cmd=cmdofSPID12<br/>SPID13##SPID 13######cmd=cmdofSPID13<br/>SPID14##SPID 14######cmd=cmdofSPID14<br/>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<br/>//@Grab('org.hsqldb:hsqldb:2.7.1:jdk8')<br/>//@GrabConfig(systemClassLoader=true)<br/>Class.forName("com.sybase.jdbc4.jdbc.SybDriver")<br/><br/>// Get basic info to connect<br/>def hostname = hostProps.get("system.hostname")<br/>def user = hostProps.get("sybase.user")<br/>def pass = hostProps.get("sybase.pass")<br/>def port = 21000<br/>def dbname = hostProps.get("sybase.dbname")<br/><br/>// Construct an SQL instance with a url and a driver<br/>def url = "jdbc:sybase:Tds:${hostname}:${port}", username=user, password=pass<br/><br/>// Sybase SQL Driver<br/>def driver = "com.sybase.jdbc4.jdbc.SybDriver"<br/><br/>// Create a connection to the SQL server<br/>def sql = Sql.newInstance(url, user, pass, driver)<br/><br/>def results = []<br/><br/>sql.withTransaction {<br/>    // ADDED time_blocked to the sql query here so that it's in your results.<br/>    results = sql.rows("select spid, time_blocked, blocked from master..sysprocesses WHERE db_name(dbid)=${dbname}")<br/>}<br/><br/>sql.close()<br/><br/>// Format the results as key-value pairs<br/>results.each { row -><br/>    println("${row.spid}.BLOCKED: ${row.blocked}") // use the println statement. <br/>    // add a second println statement for the second datapoint on this SPID. This datapoint is called TIME_BLOCKED<br/>    (h,m) = row.time_blocked.tokenize(":")<br/>    time_blocked = h.toInteger()*100 + m.toInteger()<br/>    println("${row.spid}.TIME_BLOCKED: ${time_blocked}")<br/>    // The datapoint definition would be a multi-line key-value pair just like the blocked datapoint<br/>    // The key would be ##WILDVALUE##.TIME_BLOCKED<br/>}<br/><br/>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).

manthena2020

OP3 years ago

yes cmd is not numeric but time_blocked is time format

LM User

·3 years ago

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

manthena2020

OP3 years ago

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

LM User

·3 years ago

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<br/>SPID3##SPID 3<br/>SPID4##SPID 4<br/>SPID5##SPID 5<br/>SPID6##SPID 6<br/>SPID7##SPID 7<br/>SPID8##SPID 8<br/>SPID9##SPID 9<br/>SPID10##SPID 10<br/>SPID11##SPID 11<br/>SPID12##SPID 12<br/>SPID13##SPID 13<br/>SPID14##SPID 14<br/>etc. etc.

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

"SPID = ${row.spid}\nBLOCKED = ${row.blocked}\n"<br/>

You would have this:

println("SPID${row.spid}.BLOCKED: ${row.blocked}")<br/>

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<br/>SPID3.BLOCKED: 0<br/>SPID4.BLOCKED: 0<br/>SPID5.BLOCKED: 0<br/>SPID6.BLOCKED: 0<br/>SPID7.BLOCKED: 0<br/>SPID8.BLOCKED: 0<br/>SPID9.BLOCKED: 0<br/>SPID10.BLOCKED: 0<br/>SPID11.BLOCKED: 0<br/>SPID12.BLOCKED: 0<br/>SPID13.BLOCKED: 0<br/>SPID14.BLOCKED: 0<br/>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.

manthena2020

OP3 years ago

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

manthena2020

OP3 years ago

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

manthena2020

OP3 years ago

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

LM User

·3 years ago

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.

manthena2020

OP3 years ago

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

manthena2020

OP3 years ago

i just wanted to store all spid and blocked info

manthena2020

OP3 years ago

manthena2020

OP3 years ago

LM User

·3 years ago

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.

manthena2020

OP3 years ago

please help

1	SUYASH GAIKWAD
2	KV karthikeyan VG
3	ME Mir Ehtesham Ali
4	SN Sangeetha Nataraj
5	FG Feng Guo
6	SR Shankar Raman M