This is a beanshell code , so a few things might look odd for a java developer. The emailFileAttachment function is a sailpoint API, a tool I am using. My problem is the data I am putting in my map is being put in a single line in excel file. And the header ("Application, Num_entitlement") I am putting in the map is not being printed at the first line in the CSV file. Could anyone please help me. This is my code below:
import sailpoint.object.Application;
import sailpoint.object.Identity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.sql.DataSource;
import java.sql.SQLException;
import sailpoint.server.Environment;
import javax.sql.DataSource;
import java.sql.ResultSet;
import sailpoint.api.SailPointContext;
import java.io.BufferedWriter;
import java.io.IOExceptoin;
import java.io.FileWriter;
import sailpoint.object.EmailTemplate;
import sailpoint.object.EmailOptions;
import java.io.File;
import java.io.FileInputStream;
import sailpoint.object.EmailFileAttachment;
import java.util.HashMap;
import sailpoint.tools.Util;
String query = "SELECT app.name as application, count(*) as num_entitlements FROM spt_application as app, spt_identity_entitlement as ent WHERE app.id = ent.application GROUP BY app.name";
HashMap info = new HashMap();
info.put("Application ", "Num_Entitlement");
PreparedStatement getEntitlement_Num = null;
Connection conn = null;
/*
public static byte[] readFiletoByteArray(File file)
{
FileInputStream fileInputStream = null;
byte[] byteFile = new byte[(int) file.length()];
try
{
fileInputStream = new FileInputStream(file);
fileInputStream.read(byteFile);
fileInputStream.close();
}
catch (Exception e)
{
e.printStackTrace();
}
return byteFile;
}
*/
try{
// Prepared Statements
Environment e = Environment.getEnvironment();
DataSource src = e.getSpringDataSource();
//System.out.println("DataSource: " + src.toString());
conn = src.getConnection();
//System.out.println("Connection: " + conn);
getEntitlement_Num = conn.prepareStatement(query);
ResultSet rs = getEntitlement_Num.executeQuery();
//System.out.println("starting RS");
while(rs.next()) {
String appName = rs.getString("application");
int no_ent = rs.getInt("num_entitlements");
info.put(appName , no_ent);
}
System.out.println("finished RS");
}catch(SQLException e){
log.error( e.toString());
} finally {
if (getEntitlement_Num!= null) {
getEntitlement_Num.close();
}
if(conn != null) {
conn.close();
}
}
//I am using sailpoint APIs for the code below.
String emailDest = "//email address here";
EmailTemplate et = new EmailTemplate();
et.setFrom("//email address here");
et.setBody("Please find an attached CSV file that has the list of all applications in IIQ and their number of Entitlements");
et.setTo(emailDest);
et.setSubject("Entitlement count for each application in IIQ");
EmailOptions ops = new EmailOptions(emailDest,null);
String strInfo = Util.mapToString(info);
byte[] fileData = strInfo.getBytes();
EmailFileAttachment attachment = new EmailFileAttachment( "EntitlementCount.csv", EmailFileAttachment.MimeType.MIME_CSV, fileData );
ops.addAttachment(attachment);
context.sendEmailNotification(et, ops);
//System.out.println("email sent");
return "Success";
info
is a HashMap
which means there's no guarantee that you can extract data in the same order as you put it in. Therefore your header "Application" might not come first in the CSV file. Instead, use something that maintains the order, eg an ArrayList
of Tuple
objects (a class you write yourself that contain two String variables).
How does Util.mapToString(info) work? We need so see it so we can investigate the newline problem.