001package com.identityworksllc.iiq.common.task.export; 002 003import com.identityworksllc.iiq.common.TaskUtil; 004import com.identityworksllc.iiq.common.Utilities; 005import com.identityworksllc.iiq.common.annotation.Experimental; 006import org.apache.commons.logging.Log; 007import org.apache.commons.logging.LogFactory; 008import sailpoint.api.SailPointContext; 009import sailpoint.api.TaskManager; 010import sailpoint.object.Attributes; 011import sailpoint.object.JasperResult; 012import sailpoint.object.PersistedFile; 013import sailpoint.object.TaskDefinition; 014import sailpoint.object.TaskResult; 015import sailpoint.object.TaskSchedule; 016import sailpoint.persistence.PersistedFileInputStream; 017import sailpoint.task.AbstractTaskExecutor; 018import sailpoint.task.TaskMonitor; 019import sailpoint.tools.GeneralException; 020import sailpoint.tools.JdbcUtil; 021import sailpoint.tools.Message; 022import sailpoint.tools.RFC4180LineIterator; 023import sailpoint.tools.RFC4180LineParser; 024import sailpoint.tools.Util; 025 026import java.io.BufferedReader; 027import java.io.IOException; 028import java.io.InputStreamReader; 029import java.sql.Connection; 030import java.sql.PreparedStatement; 031import java.sql.SQLException; 032import java.sql.Timestamp; 033import java.util.*; 034import java.util.concurrent.atomic.AtomicBoolean; 035 036/** 037 * Runs a series of IIQ report tasks, then exports the contents of their CSV output 038 * to a database table. 039 * 040 * TODO: make an ad hoc copy of the report to force CSV output and suppress emails 041 */ 042@Experimental 043public class ReportExporter extends AbstractTaskExecutor { 044 private final Log log; 045 private final AtomicBoolean terminated; 046 047 public ReportExporter() { 048 this.terminated = new AtomicBoolean(); 049 this.log = LogFactory.getLog(ReportExporter.class); 050 } 051 052 @Override 053 public void execute(SailPointContext context, TaskSchedule taskSchedule, TaskResult taskResult, Attributes<String, Object> attributes) throws Exception { 054 List<String> reportTaskDefNames = attributes.getStringList("reports"); 055 String driver = attributes.getString("driver"); 056 String url = attributes.getString("url"); 057 String username = attributes.getString("username"); 058 String password = attributes.getString("password"); 059 060 ExportConnectionInfo connectionInfo = new ExportConnectionInfo(url, username, password); 061 connectionInfo.setDriver(driver); 062 063 TaskMonitor monitor = new TaskMonitor(context, taskResult); 064 065 Timestamp taskTimestamp = new Timestamp(System.currentTimeMillis()); 066 067 for(String reportIdOrName : reportTaskDefNames) { 068 if (terminated.get()) { 069 break; 070 } 071 072 TaskDefinition report = context.getObjectByName(TaskDefinition.class, reportIdOrName); 073 if (report == null) { 074 TaskUtil.withLockedMasterResult(monitor, (tr) -> { 075 tr.addMessage(Message.error("Unable to find report: {0}", reportIdOrName)); 076 }); 077 break; 078 } 079 080 String reportName = report.getName(); 081 082 monitor.forceProgress("Executing: " + reportName); 083 084 TaskUtil.withLockedMasterResult(monitor, (tr) -> { 085 tr.addMessage(Utilities.timestamp() + " " + reportName + ": Executing report task"); 086 }); 087 088 TaskManager taskManager = new TaskManager(context); 089 TaskSchedule reportTask = taskManager.run(report, new HashMap<>()); 090 091 TaskResult reportOutput = null; 092 093 boolean finished = false; 094 while (reportOutput == null && !terminated.get()) { 095 try { 096 reportOutput = taskManager.awaitTask(reportTask, 60); 097 } catch(GeneralException e) { 098 if (e.toString().contains("Timeout waiting")) { 099 log.debug("Still waiting for task " + reportTask.getName()); 100 } else { 101 throw e; 102 } 103 } 104 } 105 106 if (terminated.get()) { 107 break; 108 } 109 110 TaskUtil.withLockedMasterResult(monitor, (tr) -> { 111 tr.addMessage(Utilities.timestamp() + " " + reportName + ": Finished running report"); 112 }); 113 114 if (reportOutput != null) { 115 if (reportOutput.isError()) { 116 TaskUtil.withLockedMasterResult(monitor, (tr) -> { 117 tr.addMessage(Message.warn(Utilities.timestamp() + " " + reportName + ": Error running report {0}", tr.getErrors())); 118 }); 119 continue; 120 } 121 122 JasperResult jasperResult = reportOutput.getReport(); 123 124 if (jasperResult != null) { 125 List<PersistedFile> fileList = jasperResult.getFiles(); 126 if (fileList != null) { 127 Optional<PersistedFile> csvFileMaybe = Utilities.safeStream(fileList).filter(PersistedFile::isCsv).findFirst(); 128 if (csvFileMaybe.isPresent()) { 129 monitor.forceProgress("Exporting: " + reportName); 130 PersistedFile csvFile = csvFileMaybe.get(); 131 TaskUtil.withLockedMasterResult(monitor, (tr) -> { 132 tr.addMessage(Utilities.timestamp() + " " + reportName + ": exporting CSV file " + csvFile.getName()); 133 }); 134 String uuid = UUID.randomUUID().toString(); 135 writeCsvContents(context, connectionInfo, taskTimestamp, uuid, report, csvFile); 136 } else { 137 log.warn("Report output did not contain a CSV file"); 138 TaskUtil.withLockedMasterResult(monitor, (tr) -> { 139 tr.addMessage(Message.warn(Utilities.timestamp() + " " + reportName + ": report output did not contain a CSV file. Is it configured to produce one?")); 140 }); 141 } 142 } else { 143 log.warn("Report output did not contain a list of files; do you need to check CSV on the list?"); 144 TaskUtil.withLockedMasterResult(monitor, (tr) -> { 145 tr.addMessage(Message.warn(Utilities.timestamp() + " " + reportName + ": report output did not contain any files. Is it configured to produce one?")); 146 }); 147 } 148 } else { 149 log.warn("TaskResult did not contain a report object"); 150 TaskUtil.withLockedMasterResult(monitor, (tr) -> { 151 tr.addMessage(Message.warn(Utilities.timestamp() + " " + reportName + ": report task result does not appear to contain a Jasper report result")); 152 }); 153 } 154 } else { 155 log.warn("TaskResult did not contain a report object for task: " + reportName); 156 } 157 } 158 } 159 160 /** 161 * Stores the row to the databaes 162 * @param rowInsert The insert prepared statement 163 * @param report The report being invoked 164 * @param rowIndex The ordinal row index 165 * @param row The actual row data from the report 166 * @param taskTimestamp The task timestamp 167 * @throws SQLException if inserting the row fails 168 */ 169 private void exportRow(PreparedStatement rowInsert, TaskDefinition report, int rowIndex, Map<String, String> row, Timestamp taskTimestamp, String uuid) throws SQLException { 170 rowInsert.setString(1, report.getName()); 171 rowInsert.setString(2, uuid); 172 rowInsert.setInt(3, rowIndex); 173 rowInsert.setTimestamp(6, taskTimestamp); 174 175 for(String key : row.keySet()) { 176 String val = row.get(key); 177 rowInsert.setString(4, key); 178 rowInsert.setString(5, val); 179 180 rowInsert.addBatch(); 181 } 182 } 183 184 /** 185 * Opens the connection to the target database using the provided connection info 186 * @param context The sailpoint context, used to decrypt the password 187 * @param connectionInfo The provided connection info, extracted from the export task def 188 * @return The open connection 189 * @throws GeneralException if any failures occur 190 */ 191 public Connection openConnection(SailPointContext context, ExportConnectionInfo connectionInfo) throws GeneralException { 192 String decryptedPassword = context.decrypt(connectionInfo.getEncryptedPassword()); 193 return JdbcUtil.getConnection(connectionInfo.getDriver(), null, connectionInfo.getUrl(), connectionInfo.getUsername(), decryptedPassword, connectionInfo.getOptions()); 194 } 195 196 @Override 197 public boolean terminate() { 198 this.terminated.set(true); 199 return true; 200 } 201 202 /** 203 * Writes the CSV contents from a report into the report export table 204 * @param context The IIQ context 205 * @param connectionInfo The connection info 206 * @param taskTimestamp The task timestamp (set at start) 207 * @param report The report taskdef 208 * @param csvFile The CSV file report output 209 * @throws SQLException if any DB failures occur 210 * @throws IOException if any file read failures occur 211 * @throws GeneralException if any IIQ failures occur 212 */ 213 private void writeCsvContents(SailPointContext context, ExportConnectionInfo connectionInfo, Timestamp taskTimestamp, String uuid, TaskDefinition report, PersistedFile csvFile) throws SQLException, IOException, GeneralException { 214 try (Connection connection = openConnection(context, connectionInfo); PreparedStatement rowInsert = connection.prepareStatement("insert into de_report_data ( report_name, run_uuid, row_index, attribute, value, insert_date ) values (?, ?, ?, ?, ?)")) { 215 int batchCount = 0; 216 try (BufferedReader reader = new BufferedReader(new InputStreamReader(new PersistedFileInputStream(context, csvFile)))) { 217 RFC4180LineIterator lineIterator = new RFC4180LineIterator(reader); 218 RFC4180LineParser parser = new RFC4180LineParser(','); 219 try { 220 String header = lineIterator.readLine(); 221 if (header == null) { 222 // File is empty 223 log.info("File " + csvFile.getName() + " is empty"); 224 } else { 225 List<String> headerElements = new ArrayList<>(parser.parseLine(header)); 226 String line; 227 int rowIndex = 0; 228 229 while ((line = lineIterator.readLine()) != null && !terminated.get()) { 230 Map<String, String> row = new HashMap<>(); 231 List<String> csvElements = parser.parseLine(line); 232 for (int i = 0; i < csvElements.size() && i < headerElements.size(); i++) { 233 String col = headerElements.get(i); 234 String val = csvElements.get(i); 235 if (Util.isNotNullOrEmpty(val)) { 236 row.put(col, val); 237 } 238 } 239 240 if (!row.isEmpty()) { 241 exportRow(rowInsert, report, rowIndex, row, taskTimestamp, uuid); 242 batchCount++; 243 } 244 245 rowIndex++; 246 247 if (batchCount > 50) { 248 rowInsert.executeBatch(); 249 batchCount = 0; 250 } 251 252 } 253 254 rowInsert.executeBatch(); 255 } 256 } finally { 257 lineIterator.close(); 258 } 259 } 260 } 261 } 262}