001package com.identityworksllc.iiq.common.task.export; 002 003import com.identityworksllc.iiq.common.Utilities; 004import org.apache.commons.logging.Log; 005import org.apache.commons.logging.LogFactory; 006import sailpoint.api.SailPointContext; 007import sailpoint.api.TaskManager; 008import sailpoint.object.Attributes; 009import sailpoint.object.JasperResult; 010import sailpoint.object.PersistedFile; 011import sailpoint.object.TaskDefinition; 012import sailpoint.object.TaskResult; 013import sailpoint.object.TaskSchedule; 014import sailpoint.persistence.PersistedFileInputStream; 015import sailpoint.task.AbstractTaskExecutor; 016import sailpoint.task.TaskMonitor; 017import sailpoint.tools.GeneralException; 018import sailpoint.tools.JdbcUtil; 019import sailpoint.tools.Message; 020import sailpoint.tools.RFC4180LineIterator; 021import sailpoint.tools.RFC4180LineParser; 022import sailpoint.tools.Util; 023 024import java.io.BufferedReader; 025import java.io.IOException; 026import java.io.InputStreamReader; 027import java.sql.Connection; 028import java.sql.PreparedStatement; 029import java.sql.SQLException; 030import java.sql.Timestamp; 031import java.util.ArrayList; 032import java.util.HashMap; 033import java.util.List; 034import java.util.Map; 035import java.util.Optional; 036import java.util.concurrent.atomic.AtomicBoolean; 037 038public class ReportExporter extends AbstractTaskExecutor { 039 /** 040 * Opens the connection to the target database using the provided connection info 041 * @param context The sailpoint context, used to decrypt the password 042 * @param connectionInfo The provided connection info, extracted from the export task def 043 * @return The open connection 044 * @throws GeneralException if any failures occur 045 */ 046 public static Connection openConnection(SailPointContext context, ExportConnectionInfo connectionInfo) throws GeneralException { 047 String decryptedPassword = context.decrypt(connectionInfo.getEncryptedPassword()); 048 return JdbcUtil.getConnection(connectionInfo.getDriver(), null, connectionInfo.getUrl(), connectionInfo.getUsername(), decryptedPassword, connectionInfo.getOptions()); 049 } 050 051 052 private final Log log; 053 private final AtomicBoolean terminated; 054 055 public ReportExporter() { 056 this.terminated = new AtomicBoolean(); 057 this.log = LogFactory.getLog(ReportExporter.class); 058 } 059 060 @Override 061 public void execute(SailPointContext context, TaskSchedule taskSchedule, TaskResult taskResult, Attributes<String, Object> attributes) throws Exception { 062 List<String> reportTaskDefNames = attributes.getStringList("reports"); 063 String driver = attributes.getString("driver"); 064 String url = attributes.getString("url"); 065 String username = attributes.getString("username"); 066 String password = attributes.getString("password"); 067 068 ExportConnectionInfo connectionInfo = new ExportConnectionInfo(url, username, password); 069 connectionInfo.setDriver(driver); 070 071 TaskMonitor monitor = new TaskMonitor(context, taskResult); 072 073 Timestamp taskTimestamp = new Timestamp(System.currentTimeMillis()); 074 075 for(String reportName : reportTaskDefNames) { 076 TaskDefinition report = context.getObjectByName(TaskDefinition.class, reportName); 077 if (report == null) { 078 taskResult.addMessage(Message.warn("Unable to find report called: {0}", reportName)); 079 context.saveObject(taskResult); 080 context.commitTransaction(); 081 continue; 082 } 083 084 monitor.forceProgress("Executing: " + reportName); 085 086 TaskManager taskManager = new TaskManager(context); 087 TaskResult reportOutput = taskManager.runSync(report, new HashMap<>()); 088 089 boolean reportExported = false; 090 091 if (reportOutput != null) { 092 JasperResult jasperResult = reportOutput.getReport(); 093 094 if (jasperResult != null) { 095 List<PersistedFile> fileList = jasperResult.getFiles(); 096 if (fileList != null) { 097 Optional<PersistedFile> csvFileMaybe = Utilities.safeStream(fileList).filter(PersistedFile::isCsv).findFirst(); 098 if (csvFileMaybe.isPresent()) { 099 PersistedFile csvFile = csvFileMaybe.get(); 100 writeCsvContents(context, connectionInfo, taskTimestamp, report, csvFile); 101 } else { 102 log.warn("Report output did not contain a CSV file"); 103 } 104 } else { 105 log.warn("Report output did not contain a list of files; do you need to check CSV on the list?"); 106 } 107 } else { 108 log.warn("TaskResult did not contain a report object"); 109 } 110 } else { 111 log.warn("TaskResult did not contain a report object for task: " + reportName); 112 } 113 } 114 } 115 116 /** 117 * Writes the CSV contents from a report into the report export table 118 * @param context The IIQ context 119 * @param connectionInfo The connection info 120 * @param taskTimestamp The task timestamp (set at start) 121 * @param report The report taskdef 122 * @param csvFile The CSV file report output 123 * @throws SQLException if any DB failures occur 124 * @throws IOException if any file read failures occur 125 * @throws GeneralException if any IIQ failures occur 126 */ 127 private void writeCsvContents(SailPointContext context, ExportConnectionInfo connectionInfo, Timestamp taskTimestamp, TaskDefinition report, PersistedFile csvFile) throws SQLException, IOException, GeneralException { 128 try (Connection connection = openConnection(context, connectionInfo); PreparedStatement rowInsert = connection.prepareStatement("insert into de_report_data ( report_name, row_index, attribute, value, insert_date ) values (?, ?, ?, ?, ?)")) { 129 int batchCount = 0; 130 try (BufferedReader reader = new BufferedReader(new InputStreamReader(new PersistedFileInputStream(context, csvFile)))) { 131 RFC4180LineIterator lineIterator = new RFC4180LineIterator(reader); 132 RFC4180LineParser parser = new RFC4180LineParser(','); 133 try { 134 String header = lineIterator.readLine(); 135 if (header == null) { 136 // File is empty 137 log.info("File " + csvFile.getName() + " is empty"); 138 } else { 139 List<String> headerElements = new ArrayList<>(parser.parseLine(header)); 140 String line; 141 int rowIndex = 0; 142 143 while ((line = lineIterator.readLine()) != null) { 144 Map<String, String> row = new HashMap<>(); 145 List<String> csvElements = parser.parseLine(line); 146 for (int i = 0; i < csvElements.size() && i < headerElements.size(); i++) { 147 String col = headerElements.get(i); 148 String val = csvElements.get(i); 149 if (Util.isNotNullOrEmpty(val)) { 150 row.put(col, val); 151 } 152 } 153 154 if (!row.isEmpty()) { 155 exportRow(rowInsert, report, rowIndex, row, taskTimestamp); 156 batchCount++; 157 } 158 159 rowIndex++; 160 161 if (batchCount > 12) { 162 rowInsert.executeBatch(); 163 batchCount = 0; 164 } 165 166 } 167 168 rowInsert.executeBatch(); 169 } 170 } finally { 171 lineIterator.close(); 172 } 173 } 174 } 175 } 176 177 private void exportRow(PreparedStatement rowInsert, TaskDefinition report, int rowIndex, Map<String, String> row, Timestamp taskTimestamp) throws SQLException { 178 rowInsert.setString(1, report.getName()); 179 rowInsert.setInt(2, rowIndex); 180 rowInsert.setTimestamp(5, taskTimestamp); 181 182 for(String key : row.keySet()) { 183 String val = row.get(key); 184 rowInsert.setString(3, key); 185 rowInsert.setString(4, val); 186 187 rowInsert.addBatch(); 188 } 189 } 190 191 @Override 192 public boolean terminate() { 193 this.terminated.set(true); 194 return true; 195 } 196}