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}