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;
}
}
