Chris Chris - 3 months ago 5
C# Question

More Efficient DataTable Sorting

I parsing some .nessus files and I have worked out a way to use a

temp
table (
dtNessusFindings
) to store values. Before a value is added to the
temp
table I check my
perm
storage (
FindingsFactory.NessusFindingsTable
).

Finally, I ensure that the only findings left are unique based on pluginID and pass/fail (
ConsolidateFindings()
), since it's possible for one pluginID to pass on workstationA but fail on workstationB.

The purpose of the 2 tables is to perform a lookup on the
perm
storage so that I can consolidate
hostnames
and avoid manually consolidating the findings later.

My question, is that using the following class it takes over 14 minutes to parse the data. Is there a more efficient way of doing this?

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;

namespace Dixie
{
public class NessusParseLinqXMLConsolidated : MasterFindingsTable
{
private static DataTable dtNessuFindingsTable;

public static void ParseNessusXML(List<string> fileNames, string radioResultHost)
{
List<string> files = new List<string>();
foreach (string doc in fileNames)
{
files.Add(doc);
}

if (FindingsFactory.NessusFindingsTable == null)
FindingsFactory.InitializeNessusFindingsTable();

dtNessuFindingsTable = new DataTable(); //Column Number
dtNessuFindingsTable.Columns.Add("testID", typeof(string)); // 0
dtNessuFindingsTable.Columns.Add("passFail", typeof(string)); // 1
dtNessuFindingsTable.Columns.Add("description", typeof(string)); // 2
dtNessuFindingsTable.Columns.Add("vulLevel", typeof(string)); // 3
dtNessuFindingsTable.Columns.Add("recommendation", typeof(string)); // 4
dtNessuFindingsTable.Columns.Add("comments", typeof(string)); // 5
dtNessuFindingsTable.Columns.Add("title", typeof(string)); // 6
dtNessuFindingsTable.Columns.Add("testCheck", typeof(string)); // 7
dtNessuFindingsTable.Columns.Add("source", typeof(string)); // 8
dtNessuFindingsTable.Columns.Add("date", typeof(string)); // 9
dtNessuFindingsTable.Columns.Add("hostName", typeof(string)); // 10
dtNessuFindingsTable.Columns.Add("os", typeof(string)); // 11
dtNessuFindingsTable.Columns.Add("ipAddr", typeof(string)); // 12
dtNessuFindingsTable.Columns.Add("stigLevel", typeof(string)); // 13
dtNessuFindingsTable.Columns.Add("stigSeverity", typeof(string)); // 14
dtNessuFindingsTable.Columns.Add("sarStatus", typeof(string)); // 15
dtNessuFindingsTable.Columns.Add("iaControl", typeof(string)); // 16
dtNessuFindingsTable.Columns.Add("key", typeof(int));

int key = 0;
List<string> missingHostnames = new List<string>();
bool noHostName = false; //automatically assume hostname exist

foreach (string s in files)
{
XElement xelement = XElement.Load(s);
IEnumerable<XElement> findings = xelement.Elements();

var findingDetails = from f in findings.Descendants("ReportItem")
select new
{
title = f.Element("plugin_name").Value,
description = f.Element("synopsis").Value,
vulLevel = f.Element("risk_factor").Value,
fixText = f.Element("solution").Value,
testID = f.Attribute("pluginID").Value
};
var hostDetails = from hd in findings.Descendants("tag")
.Where(e => e.Attribute("name").Value == "operating-system")
select hd.Value;

var os = findings.Descendants("tag")
.Where(e => e.Attribute("name").Value == "operating-system")
.FirstOrDefault()?.Value;
if (os == null)
{
os = findings.Descendants("tag")
.Where(e => e.Attribute("name").Value == "os")
.FirstOrDefault()?.Value;
}
var hostname = findings.Descendants("tag")
.Where(e => e.Attribute("name").Value == "host-fqdn")
.FirstOrDefault()?.Value;
var ipAddress = findings.Descendants("tag")
.Where(e => e.Attribute("name").Value == "host-ip")
.FirstOrDefault()?.Value;
var dateTime = findings.Descendants("tag")
.Where(e => e.Attribute("name").Value == "HOST_END")
.FirstOrDefault()?.Value;

if (hostname == null)
{
missingHostnames.Add(s.ToString());
noHostName = true;
}

for (int i = 0; i < findingDetails.Count(); i++)
{
foreach (var fd in findingDetails)
{
bool exists = FindingsFactory.NessusFindingsTable
.Select().ToList().Exists(row => row["testID"].ToString() == fd.testID.ToString());

bool exists2 = dtNessuFindingsTable
.Select().ToList().Exists(row => row["testID"].ToString() == fd.testID.ToString());

if (exists | exists2)
{
foreach (DataRow dr in FindingsFactory.NessusFindingsTable.Rows)
{
if (dr["testID"].ToString() == fd.testID.ToString() && dr["passFail"].ToString() == "pass")
{
if (hostname != null && !dr["hostname"].ToString().Contains(hostname))
dr["hostName"] = dr["hostName"].ToString() + ", " + hostname;
else
dr["hostName"] = hostname;
}
else if (dr["testID"].ToString() == fd.testID.ToString() && dr["passFail"].ToString() == "fail")
{
if (!dr["hostname"].ToString().Contains(hostname))
dr["hostName"] = dr["hostName"].ToString() + ", " + hostname;
else
dr["hostName"] = hostname;
}
}
}
else
{
dtNessuFindingsTable.Rows.Add();
dtNessuFindingsTable.Rows[i]["testID"] = fd.testID.ToString();
dtNessuFindingsTable.Rows[i]["title"] = fd.title.ToString();
dtNessuFindingsTable.Rows[i]["vulLevel"] = fd.vulLevel.ToString();
dtNessuFindingsTable.Rows[i]["description"] = fd.description.ToString();
dtNessuFindingsTable.Rows[i]["recommendation"] = fd.fixText.ToString();
dtNessuFindingsTable.Rows[i]["source"] = "Tenable Nessus Pro";
dtNessuFindingsTable.Rows[i]["ipAddr"] = ipAddress.ToString();
dtNessuFindingsTable.Rows[i]["date"] = dateTime.ToString();
dtNessuFindingsTable.Rows[i]["os"] = os.ToString();
dtNessuFindingsTable.Rows[i]["passFail"] = GetPassFail(fd.vulLevel.ToString());
dtNessuFindingsTable.Rows[i]["stigSeverity"] = GetSTIGSeverityLevel(fd.vulLevel.ToString());
dtNessuFindingsTable.Rows[i]["sarStatus"] = GetSarStatus(GetPassFail(fd.vulLevel.ToString()));
dtNessuFindingsTable.Rows[i]["key"] = key;
if (noHostName == true)
dtNessuFindingsTable.Rows[i]["hostname"] = "Add <tag name=\"host-fqdn\">hostname to use</tag> to file: " + s.ToString();
else
dtNessuFindingsTable.Rows[i]["hostname"] = hostname;

i++;
key++;
}
}
}
noHostName = false;
for (int i = dtNessuFindingsTable.Rows.Count - 1; i >= 0; i--)
{
//remove empty rows from table
if (dtNessuFindingsTable.Rows[i]["testID"] == DBNull.Value)
dtNessuFindingsTable.Rows[i].Delete();
}
dtNessuFindingsTable.AcceptChanges();
FindingsFactory.NessusFindingsTable.Merge(dtNessuFindingsTable, false);
}
DataTable dtFinal = ConsolidateFindings(FindingsFactory.NessusFindingsTable).Copy();
FindingsFactory.NessusFindingsTable.Clear();
FindingsFactory.NessusFindingsTable = dtFinal.Copy();
FindingsFactory.NessusFindingsTable.AcceptChanges();
}
private static DataTable ConsolidateFindings(DataTable dtOrginal)
{
DataTable tblresult = dtOrginal.Clone(); // empty table, same columns
var rowGroups = dtOrginal.AsEnumerable()
.GroupBy(row => new
{
Id = row.Field<string>("testId"),
passFail = row.Field<string>("passFail")
});

foreach (var group in rowGroups)
{
DataRow row = tblresult.Rows.Add(); // already added now
row.SetField("testId", group.Key.Id);
row.SetField("passFail", group.Key.passFail);
row.SetField("description", group.Select(r => r.Field<string>("description")).First());
row.SetField("vulLevel", group.Select(r => r.Field<string>("vulLevel")).First());
row.SetField("recommendation", group.Select(r => r.Field<string>("recommendation")).First());
row.SetField("comments", group.Select(r => r.Field<string>("comments")).First());
row.SetField("title", group.Select(r => r.Field<string>("title")).First());
row.SetField("testCheck", group.Select(r => r.Field<string>("testCheck")).First());
row.SetField("date", group.Select(r => r.Field<string>("date")).First());
row.SetField("os", group.Select(r => r.Field<string>("os")).First());
row.SetField("ipAddr", group.Select(r => r.Field<string>("ipAddr")).First());
row.SetField("stigLevel", group.Select(r => r.Field<string>("stigLevel")).First());
row.SetField("stigSeverity", group.Select(r => r.Field<string>("stigSeverity")).First());
row.SetField("sarStatus", group.Select(r => r.Field<string>("sarStatus")).First());
row.SetField("iaControl", group.Select(r => r.Field<string>("iaControl")).First());
string source = String.Join(", ", group.Select(r => r.Field<string>("source")));
row.SetField("source", source);
string hostNames = String.Join(", ", group.Select(r => r.Field<string>("hostname")));
row.SetField("hostname", hostNames);
}
return tblresult;
}
public static string GetSTIGSeverityLevel(string riskLevel)
{
string stigSeverityLevel = null;
switch (riskLevel.ToLower())
{
case "high":
stigSeverityLevel = "I";
break;
case "very high":
stigSeverityLevel = "I";
break;
case "critical":
stigSeverityLevel = "I";
break;
case "moderate":
stigSeverityLevel = "II";
break;
case "low":
stigSeverityLevel = "III";
break;
case "very low":
stigSeverityLevel = "IV";
break;
case "none":
stigSeverityLevel = "N/A";
break;
}
return stigSeverityLevel;
}
private static string GetPassFail(string riskFactor)
{
string status = null;
if (riskFactor.ToLower() == "none")
status = "pass";
else
status = "fail";
return status;
}
private static string GetSarStatus(string passFail)
{
string sarStatus = null;
switch (passFail.ToLower())
{
case "critical":
sarStatus = "Other than Satisfied";
break;
case "very high":
sarStatus = "Other than Satisfied";
break;
case "high":
sarStatus = "Other than Satisfied";
break;
case "low":
sarStatus = "Other than Satisfied";
break;
case "moderate":
sarStatus = "Other than Satisfied";
break;
case "medium":
sarStatus = "Other than Satisfied";
break;
case "n/a":
sarStatus = "N/A";
break;
case "none":
sarStatus = "N/A";
break;
}
return sarStatus;
}
}
}

Answer

The main problem seems to be this double loop

            for (int i = 0; i < findingDetails.Count(); i++)
            {
                foreach (var fd in findingDetails)
                {

and the easiest fix would be to replace it with this

            int i = 0; 
            {
                foreach (var fd in findingDetails)
                {
Comments