Forum Discussion

manthena2020's avatar
2 years ago

Collection Scripts

hi, Please find my code below 

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
 

  • Anonymous's avatar
    Anonymous

    Ok, there must be some special character in it9 that is messing things up. Does it have = or & in the text?

  • println("${it1}##${it1}######text=${it9}&blocked_spid=${it1}&blocked_by_spid=${it2}&dbname=${it3}&loginame=${it5}&lastbatch=${it6}&cmd=${it7}&program=${it8}")

    The above  i have 

  • Anonymous's avatar
    Anonymous

    Yeah, does it9 have = or & in its value? When it9 is resolved and gets inserted into this string, if it has = or & it will mess up the output parsing.

  • if iam using this

     println("${it1}##Blockedby:${it2}.dbname:${it3}.Login:${it5}.Batch:${it6}.cmd:${it7}.Program:${it8}###text=${it9}")

    iam getting output under these coloumns

    wildvalue    wildvalue2      description

  • Anonymous's avatar
    Anonymous

    if iam using this

     println("${it1}##Blockedby:${it2}.dbname:${it3}.Login:${it5}.Batch:${it6}.cmd:${it7}.Program:${it8}###text=${it9}")

    iam getting output under these coloumns

    wildvalue    wildvalue2      description

    You don’t have the double hash tags in the right place. It’s wildvalue##wildalias##description##wildvalue2##properties.

    Your it9 has equals signs in it. You’ll either have to remove the equals signs using .replaceAll(“=”,” “) or not include it9 in the properties. It works fine as the description because equals signs are allowed in the description. But they are not allowed in the values or names of properties.  Same goes for &.

  • Anonymous's avatar
    Anonymous

    Not sure what you mean by “create a token for Description”. What do you mean? You can set the description by putting something between the 2nd and 3rd pairs of hashtags:

    wildvalue##wildalias##WHATEVER YOU PUT HERE WILL BE THE DESCRIPTION##wildvalue2##properties

    If you want to use that in your alert message, use ##DSIDESCRIPTION##. However, be careful adding a large blob of text as your description. That description will show up in your resource tree and might make it difficult to navigate.