private void WriteFindingsToReport() { try { using (SQLiteCommand sqliteCommand = DatabaseBuilder.sqliteConnection.CreateCommand()) { sqliteCommand.Parameters.Add(new SQLiteParameter("UserName", Properties.Settings.Default.ActiveUser)); FilterTextCreator filterTextCreator = new FilterTextCreator(); string groupFilter = filterTextCreator.Group(sqliteCommand, "SCAP & STIG Discrepancies"); string severityFilter = filterTextCreator.Severity(sqliteCommand, "SCAP & STIG Discrepancies"); string statusFilter = filterTextCreator.Status(sqliteCommand, "SCAP & STIG Discrepancies"); sqliteCommand.CommandText = _ddlReader.ReadDdl(_storedProcedureBase + "Select.StigDiscrepanciesVulnerabilities.dml", assembly); if (!string.IsNullOrWhiteSpace(groupFilter) || !string.IsNullOrWhiteSpace(severityFilter) || !string.IsNullOrWhiteSpace(statusFilter)) { Regex regex = new Regex(Properties.Resources.RegexSqlGroupBy); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"{Environment.NewLine}"); if (!string.IsNullOrWhiteSpace(groupFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, groupFilter); } if (!string.IsNullOrWhiteSpace(severityFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"(PrimaryRawRiskIndicator {severityFilter}) "); } sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, Environment.NewLine); if (!string.IsNullOrWhiteSpace(statusFilter)) { regex = new Regex(Properties.Resources.RegexStigDiscrepanciesStatus); MatchCollection matches = regex.Matches(sqliteCommand.CommandText); for (int i = matches.Count - 1; i >= 0; i--) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(matches[i].Index, "AND "); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(matches[i].Index, matches[i].ToString().Contains("Mitigated") ? $"MitigatedStatus {statusFilter}" : $"Status {statusFilter} "); } } } using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader()) { while (sqliteDataReader.Read()) { if (string.IsNullOrWhiteSpace(sqliteDataReader["XccdfStatus"].ToString()) || string.IsNullOrWhiteSpace(sqliteDataReader["CklStatus"].ToString()) || sqliteDataReader["XccdfStatus"].Equals(sqliteDataReader["CklStatus"])) { continue; } WriteFindingToReport(sqliteDataReader); } } } } catch (Exception exception) { LogWriter.LogError("Unable to write findings to the 'STIG Discrepancies' workbook."); throw exception; } }
private void WriteFindingsToReport() { try { using (SQLiteCommand sqliteCommand = DatabaseBuilder.sqliteConnection.CreateCommand()) { sqliteCommand.Parameters.Add(new SQLiteParameter("UserName", Properties.Settings.Default.ActiveUser)); FilterTextCreator filterTextCreator = new FilterTextCreator(); string findingTypeFilter = filterTextCreator.FindingType(sqliteCommand, "Navy RAR"); string groupFilter = filterTextCreator.Group(sqliteCommand, "Navy RAR"); string severityFilter = filterTextCreator.Severity(sqliteCommand, "Navy RAR"); string statusFilter = filterTextCreator.Status(sqliteCommand, "Navy RAR"); string rmfOverrideFilter = filterTextCreator.RmfOverride(sqliteCommand, "Navy RAR"); sqliteCommand.CommandText = _ddlReader.ReadDdl(_storedProcedureBase + "Select.NavyRarVulnerabilities.dml", assembly); if (!string.IsNullOrWhiteSpace(findingTypeFilter) || !string.IsNullOrWhiteSpace(groupFilter) || !string.IsNullOrWhiteSpace(severityFilter) || !string.IsNullOrWhiteSpace(statusFilter)) { Regex regex = new Regex(Properties.Resources.RegexSqlGroupBy); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, "WHERE "); if (!string.IsNullOrWhiteSpace(findingTypeFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, findingTypeFilter); } if (!string.IsNullOrWhiteSpace(groupFilter)) { if (!string.IsNullOrWhiteSpace(findingTypeFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); } sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, groupFilter); } if (!string.IsNullOrWhiteSpace(severityFilter)) { if (!string.IsNullOrWhiteSpace(findingTypeFilter) || !string.IsNullOrWhiteSpace(groupFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); } sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"(PrimaryRawRiskIndicator {severityFilter}) "); } if (!string.IsNullOrWhiteSpace(statusFilter)) { if (!string.IsNullOrWhiteSpace(findingTypeFilter) || !string.IsNullOrWhiteSpace(groupFilter) || !string.IsNullOrWhiteSpace(severityFilter)) { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"AND {Environment.NewLine}"); } sqliteCommand.CommandText = string.IsNullOrWhiteSpace(rmfOverrideFilter) ? sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"(RawStatus {statusFilter} OR UniqueMitigatedStatus {statusFilter}) ") : sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"(RawStatus {statusFilter} OR UniqueMitigatedStatus {statusFilter} OR GroupMitigatedStatus {statusFilter}) "); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, Environment.NewLine); regex = new Regex(Properties.Resources.RegexGroupsMitigationsOrConditionsVulnerabilities); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, $"{Environment.NewLine}WHERE (MitigatedStatus {statusFilter}) "); } else { sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, Environment.NewLine); } } if (!string.IsNullOrWhiteSpace(rmfOverrideFilter)) { Regex regex = new Regex(Properties.Resources.RegexSqlFindingTypes); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, rmfOverrideFilter); regex = new Regex(Properties.Resources.RegexSqlFromUniqueFindings); sqliteCommand.CommandText = sqliteCommand.CommandText.Insert(regex.Match(sqliteCommand.CommandText).Index, Properties.Resources.StringGroupRmfFields); } using (SQLiteDataReader sqliteDataReader = sqliteCommand.ExecuteReader()) { while (sqliteDataReader.Read()) { if (sqliteDataReader["UniqueVulnerabilityIdentifier"].ToString().Equals("Plugin")) { continue; } WriteFindingToReport(sqliteDataReader); } } } } catch (Exception exception) { LogWriter.LogError("Unable to write findings to the 'Navy RAR' workbook."); throw exception; } }