Groovy & SQL authentication

The "if(auto.integrated_security" statement looks for a property on the device to indicate if integrated security should be used. If it should be used, the difference is the connection string, the SQLUrl variable. It'll contain "integratedSecurity=true" if you're using Windows. It'll have user and pass if it's false (or if it doesn't exist).

However, the real problem is that there is no variable in your script called "auto.integrated_security". You intended for that to look in LM and find the property by that name, but you skipped it. You'd need to do something like this:

import org.xbill.DNS.*;
import groovy.sql.Sql;

def returnCode = 0;
def sql = null;
try {
    dbname = hostProps.get("databasexyz")    
    hostname = hostProps.get('system.hostname');
    integrated = hostProps.get('auto.integrated_security');

//Check for Integrated Security and then authenticate that way if it's available
    if(integrated){  //just checks if the variable is true. It'll be null (false) if the hostProps.get statement above failed to find the property in LM
        SQLUrl = "jdbc:sqlserver://${hostname};integratedSecurity=true;applicationIntent=ReadOnly;logintimeout=5";
    } else {
        user = hostProps.get("jdbc.mssql.user");
        pass = hostProps.get("jdbc.mssql.pass");
        SQLUrl = "jdbc:sqlserver://${hostname}:1433;user=${user};password=${pass}";
    }
sql = Sql.newInstance(SQLUrl)
// Loop through every row returned by the query
sql.eachRow( 'SELECT * from blah blah blah')

I'm assuming your script was intentionally incomplete as you didn't close the try block.

Also, I'd check the hostProps.get("databasexyz") statement. That one looks for a property on the device in LM called "databasexyz". That likely is the actual value, not the value of the property with that name. So you might change it to:

dbname = hostProps.get("database_name")

Then, in LM, you'd create a property on the device called "dbname" and give it the value of "databasexyz". You might consider making dbname part of the AppliesTo, since this whole script will fail if the device doesn't have that property.

Thanks Stewart for getting me headed in the right direction.  I should of known I had to define that variable.  So here is what's happening now - The script runs correctly when tested on a box that's setup for Integrated auth however when tested on a box using SQL authentication just throws the standard error code(1).  If I comment out the Integrated lines below then it works on the SQL auth instance.  I'm not sure what's going on but it doesn't like something...  fails the same way if I try moving the user and pass variables down under the "else" as well.

import org.xbill.DNS.*;
import groovy.sql.Sql;

def returnCode = 0;
def sql = null;
try {    
    hostname = hostProps.get('system.hostname');
    integrated = hostProps.get('auto.integrated_security');
    user = hostProps.get("jdbc.mssql.user");
    pass = hostProps.get("jdbc.mssql.pass");

//Check for Integrated Security and then authenticate that way if it's available
    if(integrated){
        SQLUrl = "jdbc:sqlserver://${hostname};integratedSecurity=true;applicationIntent=ReadOnly;logintimeout=5";

    } else {
        SQLUrl = "jdbc:sqlserver://${hostname}:1433;user=${user};password=${pass}";   
    }

    sql = Sql.newInstance(SQLUrl)

    
    // Loop through every row returned by the query
    sql.eachRow( 'SELECT dev_id,dev_displayname,dev_ipaddress,dev_serialnumber,dev_mac_address,dev_manufacturer,dev_model,PPM,total_memory,system_systemname FROM genericdatabase.dbo.view_device_properties' ) {
        // Print active discovery script output - WildValue, WildAlias and auto properties.
        println "$it.dev_id##$it.dev_displayname##$it.dev_mac_address####genericdatabase.id=$it.dev_id&genericdatabase.displayName=$it.dev_displayname&genericdatabase.ip=$it.dev_ipaddress&genericdatabase.serialNumber=$it.dev_serialnumber&genericdatabase.macAddress=$it.dev_mac_address&genericdatabase.manufacturer=$it.dev_manufacturer&genericdatabase.Model=$it.dev_model&genericdatabase.memory(mb)=$it.total_memory&genericdatabase.ppm=$it.ppm&genericdatabase.devicename=$it.system_systemname"
    }
}
catch (Exception e){
    returnCode = 1;
}
finally {
    if(sql != null) {
        sql.close()
    }
    return returnCode;    
}

I'd suggest adding checks within your main try block. You might check that you're actually fetching values for user/pass. If you run this in a Collector debug, you can get more output, like the println statements.

import org.xbill.DNS.*; //BTW, you don't need this unless you're interacting with a DNS server here in your script.
import groovy.sql.Sql

def returnCode = 0
def sql = null

hostname = hostProps.get('system.hostname')
integrated = hostProps.get('auto.integrated_security')
user = hostProps.get("jdbc.mssql.user")
pass = hostProps.get("jdbc.mssql.pass")

try {
    //Check for Integrated Security and then authenticate that way if it's available
    if(integrated){
        SQLUrl = "jdbc:sqlserver://${hostname};integratedSecurity=true;applicationIntent=ReadOnly;logintimeout=5"
    } else {
        if (!user || !pass) {
            println("Unable to fetch username/password.")
            return 2
        }
        SQLUrl = "jdbc:sqlserver://${hostname}:1433;user=${user};password=${pass}"
    }
    println(SQLUrl)
    sql = Sql.newInstance(SQLUrl)


    // Loop through every row returned by the query
    sql.eachRow( 'SELECT dev_id,dev_displayname,dev_ipaddress,dev_serialnumber,dev_mac_address,dev_manufacturer,dev_model,PPM,total_memory,system_systemname FROM genericdatabase.dbo.view_device_properties' ) {
        // Print active discovery script output - WildValue, WildAlias and auto properties.
        println "$it.dev_id##$it.dev_displayname##$it.dev_mac_address####genericdatabase.id=$it.dev_id&genericdatabase.displayName=$it.dev_displayname&genericdatabase.ip=$it.dev_ipaddress&genericdatabase.serialNumber=$it.dev_serialnumber&genericdatabase.macAddress=$it.dev_mac_address&genericdatabase.manufacturer=$it.dev_manufacturer&genericdatabase.Model=$it.dev_model&genericdatabase.memory(mb)=$it.total_memory&genericdatabase.ppm=$it.ppm&genericdatabase.devicename=$it.system_systemname"
    }
}
catch (Exception e){
    returnCode = 1
}
finally {
    if(sql != null) {
        sql.close()
    }
    return returnCode
}

The fix seems to be this - if(integrated == 'true'){ instead of just if(integrated){  

When I add:

println(SQLUrl)

and test it on an instance that I know should use SQL auth the SQL url being returned was the integrated URL so I'm not totally sure why it doesn't like if(integrated).  I appreciate the tips & pointers with the added return values and checks!

Has to do with the truthiness of the variable. The variable is a string. So if it's a non-zero length string, it evaluates to true. Even if the value of the variable is "false". Your test compares the string. My way would have required casting the variable to a boolean to work under all circumstances (or required the property to be created slightly differently in LM). 

Great job figuring it out!