Groovy & SQL authentication
I have a couple of Groovy Datasources running SQL queries and I would like to write in the lines for the script to use Windows auth or SQL auth but I'm not sure how. I have it working using SQL auth but in some instances Windows Auth is going to be used. I'm looking at some of the other core SQL datasources and how they work but I'm having a hard time picking out the lines in the scripts that they use for this. It's my understanding that they use the outputs from some of the core SQL property sources. I'd like to leverage that as well but don't know what I need.
Here's the initial script I have but it's not working this way. Do any of you have an example I could look at or see an issue in what I've shown below?
import org.xbill.DNS.*;
import groovy.sql.Sql;
def returnCode = 0;
def sql = null;
try {
dbname = hostProps.get("databasexyz")
//user = hostProps.get("jdbc.mssql.user");
//pass = hostProps.get("jdbc.mssql.pass");
hostname = hostProps.get('system.hostname');
//Check for Integrated Security and then authenticate that way if it's available
if(auto.integrated_security == "true"){
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 * from blah blah blah
LM User
·4 years agoHas 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!
Shack
OP4 years agoThe fix seems to be this - if(integrated == 'true'){ instead of just if(integrated){
When I add:
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!
LM User
·4 years agoI'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.
Shack
OP4 years agoThanks 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;
}
LM User
·4 years agoI'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.
LM User
·4 years agoThe "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: