更有效的数据表排序
本文关键字：排序 数据表 有效 | 更新日期: 2023-09-27 18:04:32
我解析了一些.nessus文件，并找到了一种使用temp表(dtNessusFindings)来存储值的方法。在将值添加到temp表之前，我检查了perm存储(FindingsFactory.NessusFindingsTable)。
最后，我确保剩下的唯一发现是基于pluginID和通过/失败(ConsolidateFindings())的唯一发现，因为一个pluginID可能在工作站a上通过，但在工作站b上失败。
这两个表的目的是在perm存储上执行查找，这样我就可以合并hostnames，避免以后手动合并结果。
我的问题是，使用下面的类需要超过14分钟来解析数据。有没有更有效的方法?
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;
}
}
}
主要问题似乎是这个双循环
for (int i = 0; i < findingDetails.Count(); i++)
{
foreach (var fd in findingDetails)
{
，最简单的修复方法是将其替换为
int i = 0;
{
foreach (var fd in findingDetails)
{