Load AD users to SQL Server table
//sqljdbc42.jar in classpath
//Read AD pattern from properties file
String sPropFileLine = null;
BufferedReader oBufReader = null;
String[] GroupArr = null;
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String connectionURL = "jdbc:sqlserver://Server1\\Staging;databaseName=DB1";
String login = "user1";
String password = "password";
String tableName = "AD_USER";
try {
//load driver here
Class.forName(driverName);
//use jdbc url, database login, and database password to create connection
java.sql.Connection conn = java.sql.DriverManager.getConnection(connectionURL, login, password);
java.sql.Statement stmt = conn.createStatement();
String dbQuery = null;
dbQuery = "TRUNCATE TABLE " + tableName; //delete all data from table
stmt.executeUpdate (dbQuery);
oBufReader = new BufferedReader(new FileReader("C:/CMfiles/AdTest.properties"));
while ((sPropFileLine = oBufReader.readLine()) != null) {
GroupArr = sPropFileLine.split(";");
//Read AD users by group name
StringBuffer sADAllUsers = new StringBuffer();
sADAllUsers.append("cmd /c dsget group \"CN="+GroupArr[0]+",OU=Resource Access,OU=Groups,OU=Enterprise,DC=google,DC=co,DC=uk\" -members | dsget user -dn");
Process procReadADUsers = Runtime.getRuntime().exec(sADAllUsers.toString());
BufferedReader brInputReadADUsers = new BufferedReader(new InputStreamReader(procReadADUsers.getInputStream()));
BufferedReader brErrorReadADUsers = new BufferedReader(new InputStreamReader(procReadADUsers.getErrorStream()));
String sReadADUserErrors = null;
String sReadADUser = brInputReadADUsers.readLine();
String unprocessedUserDN = brInputReadADUsers.readLine();
while (unprocessedUserDN!= null) {
if((! unprocessedUserDN.equals("dsget succeeded"))) {
sReadADUser =(unprocessedUserDN.substring((int)unprocessedUserDN.indexOf('=')+1,(int)unprocessedUserDN.indexOf(','))).trim().toLowerCase();
//execute sql query
dbQuery = "INSERT INTO " + tableName + " VALUES ('"+ sReadADUser + "');";
printOut(dbQuery);
stmt.executeUpdate (dbQuery);
}
unprocessedUserDN = brInputReadADUsers.readLine();
}
while ((sReadADUserErrors = brErrorReadADUsers.readLine()) != null) {
printOut("Error reading members of AD group "+GroupArr[0]+": "+sReadADUserErrors);
}
int exitValue = procReadADUsers.waitFor();
printOut("Exit Status: "+exitValue);
sADAllUsers.delete(0,sADAllUsers.length());
}
} catch (Exception e) {
printErr(e.getLocalizedMessage());
}