public FormImportActivities()
{
#region Initialization
InitializeComponent();
try
{
if (crypto.GetSettings("ExcelPlugIn", "ManagementServer") == null || crypto.GetSettings("ExcelPlugIn", "Token") == null)
{
MessageBox.Show("Please log into the SentinelOne Management Server first", "Please login", MessageBoxButtons.OK, MessageBoxIcon.Information);
Load += (s, e) => Close();
return;
}
// Restoring previously specified Activites parameters
if (crypto.GetSettings("ExcelPlugIn", "MaximumRecord") == null)
{
numericUpDownMaxRecord.Value = 100;
}
else
{
numericUpDownMaxRecord.Value = Convert.ToUInt32(crypto.GetSettings("ExcelPlugIn", "MaximumRecord"));
}
if (crypto.GetSettings("ExcelPlugIn", "NoDateLimit") == null)
{
checkBoxDateLimit.Checked = false;
}
else
{
checkBoxDateLimit.Checked = Convert.ToBoolean(crypto.GetSettings("ExcelPlugIn", "NoDateLimit"));
}
if (crypto.GetSettings("ExcelPlugIn", "NoRecordLimit") == null)
{
checkBoxRecordLimit.Checked = false;
}
else
{
checkBoxRecordLimit.Checked = Convert.ToBoolean(crypto.GetSettings("ExcelPlugIn", "NoRecordLimit"));
}
if (crypto.GetSettings("ExcelPlugIn", "StartDate") == null)
{
int year = DateTime.Now.Year;
DateTime firstDay = new DateTime(year, 1, 1);
dateTimePickerStart.Value = firstDay;
}
else
{
dateTimePickerStart.Value = DateTime.Parse(crypto.GetSettings("ExcelPlugIn", "StartDate"));
}
if (crypto.GetSettings("ExcelPlugIn", "EndDate") == null)
{
dateTimePickerEnd.Value = DateTime.Today;
}
else
{
dateTimePickerEnd.Value = DateTime.Parse(crypto.GetSettings("ExcelPlugIn", "EndDate"));
}
double diff = ((dateTimePickerEnd.Value - dateTimePickerStart.Value).TotalDays + 1);
textBoxDays.Text = diff == 1 ? diff.ToString() + " day" : diff.ToString() + " days";
// =============================================================================================
string token = crypto.Decrypt(crypto.GetSettings("ExcelPlugIn", "Token"));
string server = crypto.GetSettings("ExcelPlugIn", "ManagementServer");
string server2 = server.Substring(server.LastIndexOf('/') + 1);
string username = crypto.GetSettings("ExcelPlugIn", "Username");
string resourceString = server + "/web/api/v1.6/activities/types";
var restClient = new RestClientInterface(resourceString);
restClient.Method = HttpVerb.GET;
restClient.PostData = "";
var results = restClient.MakeRequest(token).ToString();
var JsonSettings = new Newtonsoft.Json.JsonSerializerSettings
{
NullValueHandling = Newtonsoft.Json.NullValueHandling.Include,
MissingMemberHandling = Newtonsoft.Json.MissingMemberHandling.Ignore
};
dynamic x = Newtonsoft.Json.JsonConvert.DeserializeObject(results, JsonSettings);
rowCount = (int)x.Count;
for (int i = 0; i < rowCount; i++)
{
ActivityType at = new ActivityType();
at.Text = x[i].action.ToString().Replace("Ad ", "AD ");
at.Value = x[i].id.ToString();
listBoxTotal.Items.Add(at);
}
if (crypto.GetSettings("ExcelPlugIn", "ActivityTypes") != null)
{
string act_types = crypto.GetSettings("ExcelPlugIn", "ActivityTypes");
string[] type_array = act_types.Split(',');
for (int i = 0; i < type_array.Length; i++)
{
for (int j = 0; j < listBoxTotal.Items.Count; j++)
{
if (listBoxTotal.Items[j].ToString() == type_array[i])
{
listBoxSelected.Items.Add(listBoxTotal.Items[j]);
listBoxTotal.Items.Remove(listBoxTotal.Items[j]);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error initialzing saved parameters, no harm done", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
#endregion
}
private void Generate()
{
try
{
// webBrowserExecutiveSummary.DocumentText = "<html><body>Gathering data, but if the message is here for a minute, there is an error somewhere...<br/></body></html>";
// webBrowserExecutiveSummary.ScriptErrorsSuppressed = true;
labelEmailMessage.Visible = false;
if (!checkBoxHTML.Checked && !checkBoxPDF.Checked)
{
MessageBox.Show("Please select at least one of HTML or PDF output", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (checkBoxEmail.Checked && CheckEmailConfig() == false)
{
return;
}
string token = crypto.Decrypt(crypto.GetSettings("ExcelPlugIn", "Token"));
userName = crypto.GetSettings("ExcelPlugIn", "Username");
// Agent Summary ==========================================================================================================
string resourceString = mgmtServer + "/web/api/v1.6/agents/count-by-filters?participating_fields=" +
"software_information__os_type,software_information__os_arch,hardware_information__machine_type," +
"network_information__domain,network_status,configuration__learning_mode,is_pending_uninstall," +
"is_up_to_date,infected,policy_id,is_active";
var restClient = new RestClientInterface(resourceString);
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var results = restClient.MakeRequest(token).ToString();
var JsonSettings = new Newtonsoft.Json.JsonSerializerSettings
{
NullValueHandling = Newtonsoft.Json.NullValueHandling.Include,
MissingMemberHandling = Newtonsoft.Json.MissingMemberHandling.Ignore
};
dynamic agent_summary = Newtonsoft.Json.JsonConvert.DeserializeObject(results, JsonSettings);
// webBrowserExecutiveSummary.DocumentText = "<html><body>" + results + "</body></html>";
string AgentsInstalled = agent_summary.total_count.ToString();
string AgentsIntalledFormatted = string.Format("{0:n0}", Convert.ToInt32(AgentsInstalled));
string Connected = agent_summary.network_status.connected == null ? "0" : agent_summary.network_status.connected.ToString();
string Disconnected = agent_summary.network_status.disconnected == null ? "0" : agent_summary.network_status.disconnected.ToString();
int PercentConnected = (int)Math.Round((double)(100 * Convert.ToInt32(Connected)) / (Convert.ToInt32(Disconnected) + Convert.ToInt32(Connected)));
string IsActiveTrue = agent_summary.is_active["true"] == null ? "0" : agent_summary.is_active["true"].ToString();
string IsActiveFalse = agent_summary.is_active["false"] == null ? "0" : agent_summary.is_active["false"].ToString();
// MessageBox.Show(IsActiveTrue + " - " + IsActiveFalse);
int PercentActive = (int)Math.Round((double)(100 * Convert.ToInt32(IsActiveTrue)) / (Convert.ToInt32(IsActiveTrue) + Convert.ToInt32(IsActiveFalse)));
// Get the assembly information
System.Reflection.Assembly assemblyInfo = System.Reflection.Assembly.GetExecutingAssembly();
// Location is where the assembly is run from
string assemblyLocation = assemblyInfo.CodeBase;
assemblyLocation = assemblyLocation.Substring(0, assemblyLocation.LastIndexOf('/'));
string timeStamp = DateTime.Now.ToString("yyyyMMdd_HHmmss");
string reportPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
System.IO.Directory.CreateDirectory(reportPath + "\\SentinelOne Reports");
string report_file = assemblyLocation + "/S1 Exec Report.html";
string report_target = reportPath + "\\SentinelOne Reports\\" + "s1_" + serverName.Substring(0, serverName.IndexOf(".")) + "_" + timeStamp + ".html";
string localPath = new Uri(report_file).LocalPath;
string localTarget = new Uri(report_target).LocalPath;
string workingdir = new Uri(assemblyLocation).LocalPath;
string report = File.ReadAllText(localPath);
#region Replace
report = report.Replace("$Server$", serverName);
report = report.Replace("$ReportPeriod$", Globals.ReportPeriod);
report = report.Replace("$StartDate$", Globals.StartDate);
report = report.Replace("$EndDate$", Globals.EndDate);
report = report.Replace("$AI$", AgentsIntalledFormatted);
report = report.Replace("$PA$", PercentActive.ToString() + "%");
report = report.Replace("$MG$", Globals.MostAtRiskGroup);
report = report.Replace("$MU$", Globals.MostAtRiskUser);
report = report.Replace("$ME$", Globals.MostAtRiskEndpoint);
report = report.Replace("$TABLE-THREATDATA$", Globals.ThreatData);
report = report.Replace("$TABLE-DETECTIONENGINE$", Globals.DetectionEngine);
report = report.Replace("$TABLE-INFECTEDFILES$", Globals.InfectedFiles);
report = report.Replace("$TABLE-MOSTATRISKGROUPS$", Globals.MostAtRiskGroups);
report = report.Replace("$TABLE-MOSTATRISKUSERS$", Globals.MostAtRiskUsers);
report = report.Replace("$TABLE-MOSTATRISKENDPOINTS$", Globals.MostAtRiskEndpoints);
report = report.Replace("$ThreatDataLabel$", Globals.ThreatDataLabel);
report = report.Replace("$DetectionEnginesLabel$", Globals.DetectionEnginesLabel);
report = report.Replace("$InfectedFilesLabel$", Globals.InfectedFilesLabel);
report = report.Replace("$MostAtRiskGroupsLabel$", Globals.MostAtRiskGroupsLabel);
report = report.Replace("$MostAtRiskUsersLabel$", Globals.MostAtRiskUsersLabel);
report = report.Replace("$MostAtRiskEndpointsLabel$", Globals.MostAtRiskEndpointsLabel);
report = report.Replace("$ThreatDataValue$", Globals.ThreatDataValue);
report = report.Replace("$DetectionEnginesValue$", Globals.DetectionEnginesValue);
report = report.Replace("$InfectedFilesValue$", Globals.InfectedFilesValue);
report = report.Replace("$MostAtRiskGroupsValue$", Globals.MostAtRiskGroupsValue);
report = report.Replace("$MostAtRiskUsersValue$", Globals.MostAtRiskUsersValue);
report = report.Replace("$MostAtRiskEndpointsValue$", Globals.MostAtRiskEndpointsValue);
report = report.Replace("$TABLE-NETWORKSTATUS$", Globals.NetworkStatus);
report = report.Replace("$TABLE-ENDPOINTOS$", Globals.EndpointOS);
report = report.Replace("$TABLE-ENDPOINTVERSION$", Globals.EndpointVersion);
report = report.Replace("$NetworkStatusLabel$", Globals.NetworkStatusLabel);
report = report.Replace("$EndpointOSLabel$", Globals.EndpointOSLabel);
report = report.Replace("$EndpointVersionLabel$", Globals.EndpointVersionLabel);
report = report.Replace("$NetworkStatusValue$", Globals.NetworkStatusValue);
report = report.Replace("$EndpointOSValue$", Globals.EndpointOSValue);
report = report.Replace("$EndpointVersionValue$", Globals.EndpointVersionValue);
report = report.Replace("$TABLE-TOPAPPLICATIONS$", Globals.TopApplications);
report = report.Replace("$TopApplicationsLabel$", Globals.TopApplicationsLabel);
report = report.Replace("$TopApplicationsValue$", Globals.TopApplicationsValue);
report = report.Replace("$ReportTimestamp$", DateTime.UtcNow.ToString("f") + " (UTC)");
report = report.Replace("$ReportUser$", userName);
#endregion
// Threat Summary ================================================================================================================
resourceString = mgmtServer + "/web/api/v1.6/threats/summary";
restClient = new RestClientInterface(resourceString);
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var resultsThreats = restClient.MakeRequest(token).ToString();
dynamic threat_summary = Newtonsoft.Json.JsonConvert.DeserializeObject(resultsThreats, JsonSettings);
string ActiveThreats = threat_summary.active.ToString();
string MitigatedThreats = threat_summary.mitigated.ToString();
string SuspiciousThreats = threat_summary.suspicious.ToString();
string BloackedThreats = threat_summary.blocked.ToString();
report = report.Replace("$TT$", string.Format("{0:n0}", Convert.ToInt32(Globals.TotalThreats)));
if (Globals.ActiveThreats == "")
{
Globals.ActiveThreats = "0";
}
report = report.Replace("$AT$", string.Format("{0:n0}", Convert.ToInt32(Globals.ActiveThreats)));
report = report.Replace("$AUT$", string.Format("{0:n0}", Convert.ToInt32(Globals.ActiveAndUnresolvedThreats)));
report = report.Replace("$UnresolvedThreatOnly$", Globals.UnresolvedThreatOnly.ToString().ToLower());
File.WriteAllText(localTarget, report);
HTMLAttachment = localTarget;
if (checkBoxHTML.Checked)
{
System.Diagnostics.Process.Start(localTarget);
}
// webBrowserExecutiveSummary.Url = new Uri("file://127.0.0.1/c$/temp/S1Report.html");
// webBrowserExecutiveSummary.IsWebBrowserContextMenuEnabled = false;
// webBrowserExecutiveSummary.Url = new Uri(report_target);
if (checkBoxPDF.Checked)
{
Process p = new Process();
p.StartInfo.WorkingDirectory = workingdir;
p.StartInfo.FileName = workingdir + "\\wkhtmltopdf.exe";
p.StartInfo.Arguments = "--lowquality " +
"--print-media-type " +
"--page-size Letter " +
"--footer-spacing 2 " +
"--footer-right \"[page] of [toPage]\" " +
"--footer-left \"[isodate] [time]\" " +
"--footer-font-size 6 " +
"\"" + localTarget + "\"" +
" \"" + Path.ChangeExtension(localTarget, ".pdf") + "\"";
// Stop the process from opening a new window
p.StartInfo.CreateNoWindow = true;
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardError = true;
p.Start();
string output = p.StandardError.ReadToEnd();
p.WaitForExit();
p.Close();
PDFAttachment = Path.ChangeExtension(localTarget, ".pdf");
System.Diagnostics.Process.Start(Path.ChangeExtension(localTarget, ".pdf"));
}
if (checkBoxEmail.Checked)
{
SendEmail();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error extracting report data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void GetData()
{
try
{
#region Create worksheet
// Creates a worksheet for "Threats" if one does not already exist
// =================================================================================================================
Excel.Worksheet threatsWorksheet;
try
{
threatsWorksheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Activity Data");
threatsWorksheet.Activate();
}
catch
{
threatsWorksheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
threatsWorksheet.Name = "Activity Data";
threatsWorksheet.Activate();
}
#endregion
#region Clear worksheet
// Clear spreadsheet
eHelper.Clear();
#endregion
#region Get Data
#region Get data from server
// Extract data from SentinelOne Management Server
// =================================================================================================================
string mgmtServer = crypto.GetSettings("ExcelPlugIn", "ManagementServer");
string token = crypto.Decrypt(crypto.GetSettings("ExcelPlugIn", "Token"));
string created_at__lte = "";
string created_at__gte = "";
string daterange = "";
string activity_type = "&activity_type__in=";
int MaxRecords = 1000000;
if (checkBoxRecordLimit.Checked)
{
// Leave MaxRecords with default
}
else
{
MaxRecords = Convert.ToInt32(numericUpDownMaxRecord.Value);
}
int BatchRecords = Globals.ApiBatch;
if (MaxRecords <= BatchRecords)
{
BatchRecords = MaxRecords;
}
int numberOfSelected = listBoxSelected.Items.Count;
if (numberOfSelected > 0)
{
for (int i = 0; i < numberOfSelected; i++)
{
activity_type = activity_type + listBoxSelected.Items[i].ToString() + ",";
}
activity_type = activity_type.TrimEnd(',');
}
else
{
MessageBox.Show("No activity types selected", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (checkBoxDateLimit.Checked)
{
daterange = "";
}
else
{
created_at__gte = "created_at__gte=" + eHelper.DateTimeToUnixTimestamp(dateTimePickerStart.Value).ToString();
created_at__lte = "created_at__lte=" + eHelper.DateTimeToUnixTimestamp(dateTimePickerEnd.Value.AddDays(1)).ToString();
daterange = "&" + created_at__lte + "&" + created_at__gte;
}
crypto.SetSettings("ExcelPlugIn", "StartDate", dateTimePickerStart.Text);
crypto.SetSettings("ExcelPlugIn", "EndDate", dateTimePickerEnd.Text);
crypto.SetSettings("ExcelPlugIn", "NoDateLimit", checkBoxDateLimit.Checked.ToString());
crypto.SetSettings("ExcelPlugIn", "ActivityTypes", activity_type.Substring(activity_type.IndexOf('=') + 1));
crypto.SetSettings("ExcelPlugIn", "MaximumRecord", numericUpDownMaxRecord.Text);
crypto.SetSettings("ExcelPlugIn", "NoRecordLimit", checkBoxRecordLimit.Checked.ToString());
string limit = "limit=" + BatchRecords.ToString();
bool Gogo = true;
string skip = "&skip=";
int skip_count = 0;
string results = "";
int maxColumnWidth = 80;
dynamic threats = "";
rowCount = 0;
int rowCountTemp = 0;
string resourceString = "";
var JsonSettings = new Newtonsoft.Json.JsonSerializerSettings
{
NullValueHandling = Newtonsoft.Json.NullValueHandling.Include,
MissingMemberHandling = Newtonsoft.Json.MissingMemberHandling.Ignore
};
var restClient = new RestClientInterface();
formMsg.StopProcessing = false;
formMsg.StartMessage();
this.Opacity = 0;
this.ShowInTaskbar = false;
stopWatch.Start();
while (Gogo)
{
resourceString = mgmtServer + "/web/api/v1.6/activities?" + limit + skip + skip_count.ToString() + daterange + activity_type;
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var batch_string = restClient.MakeRequest(token).ToString();
threats = Newtonsoft.Json.JsonConvert.DeserializeObject(batch_string, JsonSettings);
rowCountTemp = (int)threats.Count;
skip_count = skip_count + BatchRecords;
results = results + ", " + batch_string.TrimStart('[').TrimEnd(']', '\r', '\n');
rowCount = rowCount + rowCountTemp;
if (rowCountTemp < BatchRecords || rowCount >= MaxRecords)
{
Gogo = false;
}
if ((MaxRecords - rowCount) < BatchRecords)
{
limit = "limit=" + (MaxRecords - rowCount).ToString();
}
formMsg.UpdateMessage("Loading activity data: " + rowCount.ToString("N0"),
eHelper.ToReadableStringUpToSec(stopWatch.Elapsed) + " elapsed");
if (formMsg.StopProcessing == true)
{
formMsg.Hide();
return;
}
}
stopWatch.Stop();
formMsg.Hide();
Globals.ApiUrl = resourceString;
results = "[" + results.TrimStart(',').TrimEnd(',', ' ') + "]";
threats = Newtonsoft.Json.JsonConvert.DeserializeObject(results, JsonSettings);
#endregion
#region Parse attribute headers
JArray ja = JArray.Parse(results);
// Stop processing if no data found
if (ja.Count == 0)
{
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "No activity data found";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
return;
}
Dictionary <string, object> dictObj = ja[0].ToObject <Dictionary <string, object> >();
List <KeyValuePair <string, string> > AttribCollection = new List <KeyValuePair <string, string> >();
int AttribNo = 0;
for (int index = 0; index < dictObj.Count; index++)
{
var Level1 = dictObj.ElementAt(index);
string dataType = "String";
dataType = eHelper.GetDataType(Level1);
if (dataType == "Object")
{
foreach (KeyValuePair <string, object> Level2 in ((JObject)Level1.Value).ToObject <Dictionary <string, object> >())
{
dataType = eHelper.GetDataType(Level2);
if (dataType == "Object")
{
foreach (KeyValuePair <string, object> Level3 in ((JObject)Level2.Value).ToObject <Dictionary <string, object> >())
{
dataType = eHelper.GetDataType(Level3);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key + "." + Level3.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key + "." + Level2.Key + "." + Level3.Key);
}
}
else
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key + "." + Level2.Key);
}
}
}
else if (dataType == "Array" && Level1.Key == "engine_data")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "engine", "String"));
AttribNo++;
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "asset_name", "String"));
AttribNo++;
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "asset_version", "String"));
AttribNo++;
}
else if (Level1.Key == "activity_type")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("activity_type_description", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "activity_type_description");
}
else if (Level1.Key == "user_id")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("user_full_name", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "user_full_name");
}
else if (Level1.Key == "agent_id")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("agent_name", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "agent_name");
}
else
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
}
}
colCount = AttribCollection.Count;
#endregion
#endregion
#region Create Headings
// Create headings
// =================================================================================================================
Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", eHelper.ExcelColumnLetter(colCount - 1) + "1");
titleRow.Select();
titleRow.RowHeight = 33;
titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", eHelper.ExcelColumnLetter(colCount - 1) + "3");
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); //
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick
#endregion
#region Write data
// Write all the data rows
// =================================================================================================================
string[,] dataBlock = new string[rowCount, colCount];
dynamic temp = "";
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < AttribCollection.Count; j++)
{
string[] prop = AttribCollection[j].Key.Split('.');
if (prop.Length == 1)
{
temp = (threats[i][prop[0]] == null) ? "null" : threats[i][prop[0]].ToString();
}
else if (prop.Length == 2 && prop[0] == "engine_data")
{
if (threats[i][prop[0]].ToString() == "[]")
{
temp = "null";
continue;
}
temp = (threats[i][prop[0]][0] == null || threats[i][prop[0]][0][prop[1]] == null) ? "null" : threats[i][prop[0]][0][prop[1]].ToString();
}
else if (prop.Length == 2)
{
temp = (threats[i][prop[0]][prop[1]] == null) ? "null" : threats[i][prop[0]][prop[1]].ToString();
}
else if (prop.Length == 3)
{
temp = (threats[i][prop[0]][prop[1]][prop[2]] == null) ? "null" : threats[i][prop[0]][prop[1]][prop[2]].ToString();
}
else if (prop.Length == 4)
{
temp = (threats[i][prop[0]][prop[1]][prop[2]][prop[3]] == null) ? "null" : threats[i][prop[0]][prop[1]][prop[2]][prop[3]].ToString();
}
if (prop[0] == "created_date" || prop[0] == "meta_data" || prop[0] == "last_active_date")
{
DateTime d2 = DateTime.Parse(temp, null, System.Globalization.DateTimeStyles.RoundtripKind);
temp = "=DATE(" + d2.ToString("yyyy,MM,dd") + ")+TIME(" + d2.ToString("HH,mm,ss") + ")";
}
try
{
if (prop[0] == "activity_type_description")
{
Excel.Worksheet lookupSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Lookup Tables");
int rows = Convert.ToInt32(lookupSheet.Cells[3, 1].Value) + 4;
temp = "=IFERROR(VLOOKUP(" + eHelper.ExcelColumnLetter(j - 1) + (i + 5).ToString() + ",'Lookup Tables'!A4:B" + rows.ToString() + ",2,FALSE),\"null\")";
// =VLOOKUP(N5,'Lookup Tables'!A4:B82,2,FALSE)
}
if (prop[0] == "agent_name")
{
Excel.Worksheet lookupSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Lookup Tables");
int rows = Convert.ToInt32(lookupSheet.Cells[3, 6].Value) + 4;
temp = "=IFERROR(VLOOKUP(" + eHelper.ExcelColumnLetter(j - 1) + (i + 5).ToString() + ",'Lookup Tables'!F4:G" + rows.ToString() + ",2,FALSE),\"null\")";
// =VLOOKUP(N5,'Lookup Tables'!A4:B82,2,FALSE)
}
if (prop[0] == "user_full_name")
{
Excel.Worksheet lookupSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Lookup Tables");
int rows = Convert.ToInt32(lookupSheet.Cells[3, 15].Value) + 4;
temp = "=IFERROR(VLOOKUP(" + eHelper.ExcelColumnLetter(j - 1) + (i + 5).ToString() + ",'Lookup Tables'!O4:P" + rows.ToString() + ",2,FALSE),\"null\")";
}
}
catch { }
dataBlock[i, j] = temp;
}
}
Excel.Range range;
range = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A5", Missing.Value);
if (threats.Count > 0)
{
range = range.Resize[rowCount, colCount];
range.Cells.ClearFormats();
range.Value = dataBlock;
// range.Formula = range.Value;
range.Font.Size = "10";
range.RowHeight = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Height;
}
#endregion
#region Column Headers
// Column Headers
// =================================================================================================================
eHelper.WriteHeaders("Activity Data", colCount, rowCount, stopWatch);
// This block writes the column headers
string hd = "";
for (int m = 0; m < AttribCollection.Count; m++)
{
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
hd = textInfo.ToTitleCase(AttribCollection[m].Key).Replace('_', ' ');
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[4, m + 1] = hd;
}
#endregion
#region Worksheet formatting
// Create the data filter
// =================================================================================================================
Excel.Range range2;
range2 = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A4", Missing.Value);
range2 = range2.Resize[rowCount + 1, colCount];
range2.Select();
// range2.AutoFilter("1", "<>", Excel.XlAutoFilterOperator.xlOr, "", true);
range2.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlOr, Type.Missing, true);
// Format number columns
// =================================================================================================================
for (int i = 0; i < integerCol.Count; i++)
{
Excel.Range rangeInt;
rangeInt = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)integerCol[i]) + "5", eHelper.ExcelColumnLetter((int)integerCol[i]) + (rowCount + 5).ToString());
rangeInt.NumberFormat = "0";
rangeInt.Cells.HorizontalAlignment = -4152; // Right align the number
rangeInt.Value = rangeInt.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
}
// Format date time columns
// =================================================================================================================
for (int j = 0; j < dateTimeCol.Count; j++)
{
Excel.Range rangeTime;
rangeTime = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)dateTimeCol[j]) + "5", eHelper.ExcelColumnLetter((int)dateTimeCol[j]) + (rowCount + 5).ToString());
rangeTime.NumberFormat = "[$-409]yyyy/mm/dd hh:mm AM/PM;@";
rangeTime.Cells.HorizontalAlignment = -4152; // Right align the Time
rangeTime.Value = rangeTime.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
}
// Format reference columns
// =================================================================================================================
for (int k = 0; k < referenceCol.Count; k++)
{
Excel.Range rangeReference;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)referenceCol[k]) + "5", eHelper.ExcelColumnLetter((int)referenceCol[k]) + (rowCount + 4).ToString());
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_ref, g_color_ref, b_color_ref));
rangeReference.Formula = rangeReference.Value;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)referenceCol[k]) + "4", eHelper.ExcelColumnLetter((int)referenceCol[k]) + "4");
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_refh, g_color_refh, b_color_refh));
// rangeReference.Value = rangeReference.Value;
}
if (referenceCol.Count > 0)
{
Excel.Range rangeReference;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("E2", "E2");
rangeReference.Value = "Reference lookup data";
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_refh, g_color_refh, b_color_refh));
}
// Autofit column width
// Formats the column width nicely to see the content, but not too wide and limit to maximum of 80
// =================================================================================================================
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
for (int i = 0; i < colCount; i++)
{
Excel.Range rangeCol;
rangeCol = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter(i) + "1", eHelper.ExcelColumnLetter(i) + "1");
if (Convert.ToInt32(rangeCol.ColumnWidth) > maxColumnWidth)
{
rangeCol.ColumnWidth = maxColumnWidth;
}
}
// Place the cursor in cell A2 - which is at the start of the document
// =================================================================================================================
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
#endregion
Globals.ApiResults = JArray.Parse(results.ToString()).ToString();
}
catch (Exception ex)
{
formMsg.Hide();
MessageBox.Show(ex.Message, "Error extracting Activity data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void GetData()
{
try
{
#region Create worksheet
// Creates a worksheet for "Applications" if one does not already exist
// =================================================================================================================
Excel.Worksheet threatsWorksheet;
try
{
threatsWorksheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Application Data");
threatsWorksheet.Activate();
}
catch
{
threatsWorksheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
threatsWorksheet.Name = "Application Data";
threatsWorksheet.Activate();
}
#endregion
#region Clear worksheet
// Clear spreadsheet
eHelper.Clear();
#endregion
#region Get Data
#region Find All the Agents from the hidden Lookup Table sheet
Excel.Worksheet lookupSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Lookup Tables");
int rows = Convert.ToInt32(lookupSheet.Cells[3, 6].Value) + 4;
Excel.Range AgentIDs = lookupSheet.Range["F5:H" + rows.ToString()];
AgentArray = AgentIDs.Value;
AgentArrayItems = AgentArray.GetLength(0);
#endregion
#region Get data from server (Looping Call)
// Extract data from SentinelOne Management Server (Looping Calls)
// =================================================================================================================
mgmtServer = crypto.GetSettings("ExcelPlugIn", "ManagementServer");
string token = crypto.Decrypt(crypto.GetSettings("ExcelPlugIn", "Token"));
userName = crypto.GetSettings("ExcelPlugIn", "Username");
StringBuilder results = new StringBuilder("[");
int maxColumnWidth = 80;
dynamic threats = "";
int rowCountTemp = 0;
int percentComplete = 0;
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
var JsonSettings = new Newtonsoft.Json.JsonSerializerSettings
{
NullValueHandling = Newtonsoft.Json.NullValueHandling.Include,
MissingMemberHandling = Newtonsoft.Json.MissingMemberHandling.Ignore
};
string resourceString = "";
var restClient = new RestClientInterface();
if (AgentArrayItems > maxIterations)
{
AgentArrayItems = maxIterations;
}
formMsg.StopProcessing = false;
formMsg.Message("Loading application data: " + rowCount.ToString("N0"),
eHelper.ToReadableStringUpToSec(stopWatch.Elapsed) + " elapsed", allowCancel: true);
for (int i = 1; i <= AgentArrayItems; i++)
{
resourceString = mgmtServer + "/web/api/v2.0/agents/applications?ids=" + AgentArray.GetValue(i, 1).ToString();
// resourceString = mgmtServer + "/web/api/v1.6/agents/" + AgentArray.GetValue(i, 1).ToString() + "/passphrase";
Globals.ApiUrl = resourceString;
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var batch_string = restClient.MakeRequest(token).ToString();
if (batch_string.Length < 4)
{
continue;
}
if (i % UpdateInterval == 0)
{
percentComplete = (int)Math.Round((double)(100 * i) / AgentArrayItems);
formMsg.Message("Collecting applications from " + i.ToString("N0") + " of " + AgentArrayItems.ToString("N0") + " agents (" + percentComplete.ToString() + "%)...",
rowCount.ToString("N0") + " applications found, " + eHelper.ToReadableStringUpToSec(stopWatch.Elapsed) + " elapsed", allowCancel: true);
if (formMsg.StopProcessing == true)
{
formMsg.Hide();
return;
}
}
JArray batchArray = JArray.Parse(batch_string);
string appName = "";
for (int j = batchArray.Count; j-- > 0;)
{
appName = ((JObject)batchArray[j])["name"].ToString();
if (appName.StartsWith("Update for Microsoft") ||
appName.StartsWith("Security Update for Microsoft") ||
appName.StartsWith("Microsoft Visual C++") ||
appName.StartsWith("Sentinel")
)
{
batchArray[j].Remove();
}
else
{
var PropertyComputerName = new JProperty("computer_name", AgentArray.GetValue(i, 2) == null ? "N/A" : AgentArray.GetValue(i, 2).ToString());
var PropertyOperatingSystem = new JProperty("operating_system", AgentArray.GetValue(i, 3) == null ? "N/A" : AgentArray.GetValue(i, 3).ToString());
((JObject)batchArray[j]).AddFirst(PropertyOperatingSystem);
((JObject)batchArray[j]).AddFirst(PropertyComputerName);
}
}
batch_string = batchArray.ToString();
threats = Newtonsoft.Json.JsonConvert.DeserializeObject(batch_string, JsonSettings);
rowCountTemp = (int)threats.Count;
results.Append(batchArray.ToString().TrimStart('[').TrimEnd(']', '\r', '\n')).Append(",");
rowCount = rowCount + rowCountTemp;
}
stopWatch.Stop();
formMsg.Hide();
// results.ToString().TrimEnd(',', ' ');
results.Length--;
results.Append("]");
threats = Newtonsoft.Json.JsonConvert.DeserializeObject(results.ToString(), JsonSettings);
#endregion
#region Parse attribute headers
JArray ja = JArray.Parse(results.ToString());
// Stop processing if no data found
if (ja.Count == 0)
{
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "No application data found";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
formMsg.Hide();
return;
}
Dictionary <string, object> dictObj = ja[0].ToObject <Dictionary <string, object> >();
List <KeyValuePair <string, string> > AttribCollection = new List <KeyValuePair <string, string> >();
int AttribNo = 0;
for (int index = 0; index < dictObj.Count; index++)
{
var Level1 = dictObj.ElementAt(index);
string dataType = "String";
dataType = eHelper.GetDataType(Level1);
if (dataType == "Object")
{
foreach (KeyValuePair <string, object> Level2 in ((JObject)Level1.Value).ToObject <Dictionary <string, object> >())
{
dataType = eHelper.GetDataType(Level2);
if (dataType == "Object")
{
foreach (KeyValuePair <string, object> Level3 in ((JObject)Level2.Value).ToObject <Dictionary <string, object> >())
{
dataType = eHelper.GetDataType(Level3);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key + "." + Level3.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key + "." + Level2.Key + "." + Level3.Key);
}
}
else
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key + "." + Level2.Key);
}
}
}
else if (Level1.Key == "computer_name")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("computer_name", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "computer_name");
}
else if (Level1.Key == "operating_system")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("operating_system", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "operating_system");
}
else if (Level1.Key == "version")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("name_version", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "name_version");
}
else
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
}
}
colCount = AttribCollection.Count;
#endregion
#endregion
#region Create Headings
// Create headings
// =================================================================================================================
Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", eHelper.ExcelColumnLetter(colCount - 1) + "1");
titleRow.Select();
titleRow.RowHeight = 33;
titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", eHelper.ExcelColumnLetter(colCount - 1) + "3");
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); //
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick
#endregion
#region Write data
// Write all the data rows
// =================================================================================================================
string[,] dataBlock = new string[rowCount, colCount];
dynamic temp = "";
string name = "";
string version = "";
string header0 = "";
for (int i = 0; i < rowCount; i++)
{
name = "";
version = "";
for (int j = 0; j < AttribCollection.Count; j++)
{
try
{
string[] prop = AttribCollection[j].Key.Split('.');
header0 = prop[0];
if (prop.Length == 1)
{
temp = (threats[i][prop[0]] == null) ? "null" : threats[i][prop[0]].ToString();
}
else if (prop.Length == 2)
{
temp = (threats[i][prop[0]][prop[1]] == null) ? "null" : threats[i][prop[0]][prop[1]].ToString();
}
else if (prop.Length == 3)
{
temp = (threats[i][prop[0]][prop[1]][prop[2]] == null) ? "null" : threats[i][prop[0]][prop[1]][prop[2]].ToString();
}
else if (prop.Length == 4)
{
temp = (threats[i][prop[0]][prop[1]][prop[2]][prop[3]] == null) ? "null" : threats[i][prop[0]][prop[1]][prop[2]][prop[3]].ToString();
}
switch (header0)
{
#region Lookups
case "installed_date":
{
if (temp == "null")
{
temp = "";
}
else
{
DateTime d2 = DateTime.Parse(temp, null, System.Globalization.DateTimeStyles.RoundtripKind);
temp = "=DATE(" + d2.ToString("yyyy,MM,dd") + ")+TIME(" + d2.ToString("HH,mm,ss") + ")";
}
break;
}
case "name":
name = temp;
break;
case "version":
version = temp;
break;
case "name_version":
temp = name + " - " + version;
break;
#endregion
}
dataBlock[i, j] = temp;
}
catch { }
}
}
Excel.Range range;
range = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A5", Missing.Value);
if (threats.Count > 0)
{
range = range.Resize[rowCount, colCount];
range.Cells.ClearFormats();
// Writes the array into Excel
// This is probably the single thing that sped up the report the most, by writing array
range.Value = dataBlock;
range.Font.Size = "10";
range.RowHeight = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Height;
}
#endregion
#region Column Headers
// Column Headers
// =================================================================================================================
eHelper.WriteHeaders("Application Data", colCount, rowCount, stopWatch);
// This block writes the column headers
string hd = "";
for (int m = 0; m < AttribCollection.Count; m++)
{
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
hd = textInfo.ToTitleCase(AttribCollection[m].Key).Replace('_', ' ');
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[4, m + 1] = hd;
}
#endregion
#region Formatting
// Create the data filter
// =================================================================================================================
Excel.Range range2;
range2 = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A4", Missing.Value);
range2 = range2.Resize[rowCount + 1, colCount];
range2.Select();
// range2.AutoFilter("1", "<>", Excel.XlAutoFilterOperator.xlOr, "", true);
range2.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlOr, Type.Missing, true);
// Format number columns
// =================================================================================================================
for (int i = 0; i < integerCol.Count; i++)
{
Excel.Range rangeInt;
rangeInt = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)integerCol[i]) + "5", eHelper.ExcelColumnLetter((int)integerCol[i]) + (rowCount + 5).ToString());
rangeInt.NumberFormat = "0";
rangeInt.Cells.HorizontalAlignment = -4152; // Right align the number
rangeInt.Value = rangeInt.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
}
// Format date time columns
// =================================================================================================================
for (int j = 0; j < dateTimeCol.Count; j++)
{
Excel.Range rangeTime;
rangeTime = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)dateTimeCol[j]) + "5", eHelper.ExcelColumnLetter((int)dateTimeCol[j]) + (rowCount + 5).ToString());
rangeTime.NumberFormat = "[$-409]yyyy/mm/dd hh:mm AM/PM;@";
rangeTime.Cells.HorizontalAlignment = -4152; // Right align the Time
rangeTime.Value = rangeTime.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
}
// Format reference columns
// =================================================================================================================
for (int k = 0; k < referenceCol.Count; k++)
{
Excel.Range rangeReference;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)referenceCol[k]) + "5", eHelper.ExcelColumnLetter((int)referenceCol[k]) + (rowCount + 4).ToString());
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_ref, g_color_ref, b_color_ref));
rangeReference.Formula = rangeReference.Value;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)referenceCol[k]) + "4", eHelper.ExcelColumnLetter((int)referenceCol[k]) + "4");
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_refh, g_color_refh, b_color_refh));
// rangeReference.Value = rangeReference.Value;
}
if (referenceCol.Count > 0)
{
Excel.Range rangeReference;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("E2", "E2");
rangeReference.Value = "Reference lookup data";
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_refh, g_color_refh, b_color_refh));
}
// Autofit column width
// Formats the column width nicely to see the content, but not too wide and limit to maximum of 80
// =================================================================================================================
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
for (int i = 0; i < colCount; i++)
{
Excel.Range rangeCol;
rangeCol = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter(i) + "1", eHelper.ExcelColumnLetter(i) + "1");
if (Convert.ToInt32(rangeCol.ColumnWidth) > maxColumnWidth)
{
rangeCol.ColumnWidth = maxColumnWidth;
}
}
// Place the cursor in cell A2 - which is at the start of the document
// =================================================================================================================
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
#endregion
formMsg.Message("Creating pivot tables and charts for the application data...", "", allowCancel: false);
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ScreenUpdating = false;
GenerateReport();
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ScreenUpdating = true;
formMsg.Hide();
// This is saved for viewing in the Show API window
Globals.ApiResults = JArray.Parse(results.ToString()).ToString();
}
catch (Exception ex)
{
formMsg.Hide();
MessageBox.Show(ex.Message, "Error extracting Application data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void GetData()
{
try
{
#region Create worksheet
// Creates a worksheet for "Threats" if one does not already exist
// =================================================================================================================
Excel.Worksheet threatsWorksheet;
try
{
threatsWorksheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Agent Data");
threatsWorksheet.Activate();
}
catch
{
threatsWorksheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
threatsWorksheet.Name = "Agent Data";
threatsWorksheet.Activate();
}
#endregion
#region Clear worksheet
// Clear spreadsheet
eHelper.Clear();
#endregion
#region Get Data
#region Get data from server
// Extract data from SentinelOne Management Server (Looping Calls)
// =================================================================================================================
mgmtServer = crypto.GetSettings("ExcelPlugIn", "ManagementServer");
string token = crypto.Decrypt(crypto.GetSettings("ExcelPlugIn", "Token"));
userName = crypto.GetSettings("ExcelPlugIn", "Username");
crypto.SetSettings("ExcelPlugIn", "AgentQuery", textBoxQuery.Text);
crypto.SetSettings("ExcelPlugIn", "AgentReturnAll", checkBoxAll.Checked.ToString());
string query = "";
if (textBoxQuery.Text != "" && checkBoxAll.Checked == false)
{
query = "&query=" + textBoxQuery.Text;
}
else
{
}
string limit = "limit=" + Globals.ApiBatch.ToString();
bool Gogo = true;
string skip = "&skip=";
string last_id = "";
int skip_count = 0;
StringBuilder results = new StringBuilder("[");
int maxColumnWidth = 80;
dynamic threats = "";
dynamic agents = "";
dynamic agentsIterate = "";
int rowCountTemp = 0;
var JsonSettings = new Newtonsoft.Json.JsonSerializerSettings
{
NullValueHandling = Newtonsoft.Json.NullValueHandling.Include,
MissingMemberHandling = Newtonsoft.Json.MissingMemberHandling.Ignore
};
formMsg.StopProcessing = false;
formMsg.Message("Loading agent data: " + rowCount.ToString("N0"),
eHelper.ToReadableStringUpToSec(stopWatch.Elapsed) + " elapsed", allowCancel: true);
this.Opacity = 0;
this.ShowInTaskbar = false;
int percentComplete = 0;
var restClient = new RestClientInterface();
stopWatch.Start();
if (Globals.ServerVersion.Contains("v1."))
{
#region v1 API using limit
while (Gogo)
{
resourceString = mgmtServer + "/web/api/v1.6/agents?" + limit + skip + skip_count.ToString() + query;
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var batch_string = restClient.MakeRequest(token).ToString();
threats = Newtonsoft.Json.JsonConvert.DeserializeObject(batch_string, JsonSettings);
rowCountTemp = (int)threats.Count;
skip_count = skip_count + Globals.ApiBatch;
results.Append(batch_string.TrimStart('[').TrimEnd(']', '\r', '\n')).Append(",");
rowCount = rowCount + rowCountTemp;
if (rowCountTemp < Globals.ApiBatch)
{
Gogo = false;
}
percentComplete = (int)Math.Round((double)(100 * rowCount) / Globals.TotalAgents);
formMsg.Message("Loading " + rowCount.ToString("N0") + " of " + Globals.TotalAgents.ToString("N0") + " agents (" + percentComplete.ToString() + "%)...",
eHelper.ToReadableStringUpToSec(stopWatch.Elapsed) + " elapsed", allowCancel: true);
if (formMsg.StopProcessing == true)
{
formMsg.Hide();
return;
}
}
#endregion
}
else
{
#region v2 API using iterator
while (Gogo)
{
resourceString = mgmtServer + "/web/api/v1.6/agents/iterator?" + limit + last_id + query;
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var res = restClient.MakeRequest(token).ToString();
agentsIterate = Newtonsoft.Json.JsonConvert.DeserializeObject(res, JsonSettings);
rowCountTemp = (int)agentsIterate.data.Count;
agents = agentsIterate.data;
last_id = "&last_id=" + agentsIterate.last_id;
skip_count = skip_count + Globals.ApiBatch;
results.Append(agents.ToString().TrimStart('[').TrimEnd(']', '\r', '\n')).Append(",");
rowCount = rowCount + rowCountTemp;
if (agentsIterate.last_id == null)
{
Gogo = false;
}
percentComplete = (int)Math.Round((double)(100 * rowCount) / Globals.TotalAgents);
formMsg.Message("Iterating data for " + rowCount.ToString("N0") + " of " + Globals.TotalAgents.ToString("N0") + " agents for reference (" + percentComplete.ToString() + "%)...",
eHelper.ToReadableStringUpToSec(stopWatch.Elapsed) + " elapsed", allowCancel: true);
if (formMsg.StopProcessing == true)
{
formMsg.Hide();
return;
}
}
#endregion
}
stopWatch.Stop();
formMsg.Hide();
Globals.ApiUrl = resourceString;
// results.ToString().TrimEnd(',', ' ');
results.Length--;
results.Append("]");
threats = Newtonsoft.Json.JsonConvert.DeserializeObject(results.ToString(), JsonSettings);
#endregion
#region Parse attribute headers
JArray ja = JArray.Parse(results.ToString());
// Stop processing if no data found
if (ja.Count == 0)
{
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "No agent data found";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
formMsg.Hide();
return;
}
Dictionary <string, object> dictObj = ja[0].ToObject <Dictionary <string, object> >();
List <KeyValuePair <string, string> > AttribCollection = new List <KeyValuePair <string, string> >();
int AttribNo = 0;
for (int index = 0; index < dictObj.Count; index++)
{
var Level1 = dictObj.ElementAt(index);
string dataType = "String";
dataType = eHelper.GetDataType(Level1);
if (dataType == "Object")
{
foreach (KeyValuePair <string, object> Level2 in ((JObject)Level1.Value).ToObject <Dictionary <string, object> >())
{
dataType = eHelper.GetDataType(Level2);
if (dataType == "Object")
{
foreach (KeyValuePair <string, object> Level3 in ((JObject)Level2.Value).ToObject <Dictionary <string, object> >())
{
dataType = eHelper.GetDataType(Level3);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key + "." + Level3.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key + "." + Level2.Key + "." + Level3.Key);
}
}
else if (dataType == "Array" && Level1.Key == "network_information")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key, dataType));
AttribNo++;
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "ip_addresses", "String"));
AttribNo++;
// AttribCollection.Insert(AttribNo, new KeyValuePair<string, string>(Level1.Key + "." + "ip_address_2", "String"));
// AttribNo++;
// AttribCollection.Insert(AttribNo, new KeyValuePair<string, string>(Level1.Key + "." + "ip_address_3", "String"));
// AttribNo++;
}
else
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key + "." + Level2.Key);
}
}
}
else if (dataType == "Array" && Level1.Key == "engine_data")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "engine", "String"));
AttribNo++;
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "asset_name", "String"));
AttribNo++;
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "asset_version", "String"));
AttribNo++;
}
else if (Level1.Key == "group_id")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("group_name", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "group_name");
}
else
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
}
}
colCount = AttribCollection.Count;
#endregion
#endregion
#region Create Headings
// Create headings
// =================================================================================================================
Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", eHelper.ExcelColumnLetter(colCount - 1) + "1");
titleRow.Select();
titleRow.RowHeight = 33;
titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", eHelper.ExcelColumnLetter(colCount - 1) + "3");
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); //
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick
#endregion
#region Write data
// Write all the data rows
// =================================================================================================================
string[,] dataBlock = new string[rowCount, colCount];
dynamic temp = "";
string[] prop;
string header0 = "";
string[] ip_interfaces;
bool byPass2 = false;
bool byPass3 = false;
Excel.Worksheet lookupSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Lookup Tables");
int rows = Convert.ToInt32(lookupSheet.Cells[3, 25].Value) + 4;
formMsg.Message(rowCount.ToString("N0") + " agent data loaded, now processing locally in Excel...", "", allowCancel: false);
for (int i = 0; i < rowCount; i++)
{
byPass2 = false;
byPass3 = false;
for (int j = 0; j < AttribCollection.Count; j++)
{
prop = AttribCollection[j].Key.Split('.');
header0 = prop[0];
if (prop.Length == 1)
{
temp = (threats[i][prop[0]] == null) ? "null" : threats[i][prop[0]].ToString();
}
else if (prop.Length == 2)
{
if (prop[1] == "ip_addresses")
{
JArray ipa = JArray.Parse(threats[i][prop[0]]["interfaces"].ToString());
int ipa_count = ipa.Count;
temp = "";
for (int k = 0; k < ipa.Count; k++)
{
if (ipa[k]["physical"].ToString() != "00:00:00:00:00:00" && ipa[k]["inet"].ToObject <string[]>().Length > 0)
{
temp = temp + ipa[k]["inet"].ToObject <string[]>()[0] + "; ";
}
}
temp = temp.ToString().TrimEnd(';', ' ');
/*
* if (prop[1] == "ip_address_1" && ipa_count > 0)
* {
* if (ipa[0]["physical"].ToString() != "00:00:00:00:00:00")
* {
* ip_interfaces = ipa[0]["inet"].ToObject<string[]>();
* temp = ip_interfaces.Length > 0 ? ip_interfaces[0] : "";
* }
* else if (ipa_count > 1 && ipa[1]["physical"].ToString() != "00:00:00:00:00:00")
* {
* ip_interfaces = ipa[1]["inet"].ToObject<string[]>();
* temp = ip_interfaces.Length > 0 ? ip_interfaces[0] : "";
* byPass2 = true;
* }
* else if (ipa_count > 2 && ipa[2]["physical"].ToString() != "00:00:00:00:00:00")
* {
* ip_interfaces = ipa[2]["inet"].ToObject<string[]>();
* temp = ip_interfaces.Length > 0 ? ip_interfaces[0] : "";
* byPass3 = true;
* }
* }
* else if (prop[1] == "ip_address_2" && ipa_count > 1)
* {
* if (ipa[1]["physical"].ToString() != "00:00:00:00:00:00" && byPass2 == false)
* {
* ip_interfaces = ipa[1]["inet"].ToObject<string[]>();
* temp = ip_interfaces.Length > 0 ? ip_interfaces[0] : "";
* }
* else if (ipa_count > 2 && ipa[2]["physical"].ToString() != "00:00:00:00:00:00" && byPass3 == false)
* {
* ip_interfaces = ipa[2]["inet"].ToObject<string[]>();
* temp = ip_interfaces.Length > 0 ? ip_interfaces[0] : "";
* byPass3 = true;
* }
* }
* else if (prop[1] == "ip_address_3" && ipa_count > 2)
* {
* if (ipa[2]["physical"].ToString() != "00:00:00:00:00:00" && byPass3 == false)
* {
* ip_interfaces = ipa[2]["inet"].ToObject<string[]>();
* temp = ip_interfaces.Length > 0 ? ip_interfaces[0] : "";
* }
* }
*/
}
else
{
temp = (threats[i][prop[0]][prop[1]] == null) ? "null" : threats[i][prop[0]][prop[1]].ToString();
}
}
else if (prop.Length == 3)
{
temp = (threats[i][prop[0]][prop[1]][prop[2]] == null) ? "null" : threats[i][prop[0]][prop[1]][prop[2]].ToString();
}
else if (prop.Length == 4)
{
temp = (threats[i][prop[0]][prop[1]][prop[2]][prop[3]] == null) ? "null" : threats[i][prop[0]][prop[1]][prop[2]][prop[3]].ToString();
}
if (prop.Length == 2 && prop[1] == "os_start_time")
{
DateTime d2 = DateTime.Parse(temp, null, System.Globalization.DateTimeStyles.RoundtripKind);
temp = "=DATE(" + d2.ToString("yyyy,MM,dd") + ")+TIME(" + d2.ToString("HH,mm,ss") + ")";
}
switch (header0)
{
#region Lookups
case "group_name":
{
temp = "=IFERROR(VLOOKUP(" + eHelper.ExcelColumnLetter(j - 1) + (i + 5).ToString() + ",'Lookup Tables'!Y4:Z" + rows.ToString() + ",2,FALSE),\"null\")";
break;
}
case "created_date":
case "last_active_date":
case "meta_data":
{
if (temp != "null")
{
DateTime d2 = DateTime.Parse(temp, null, System.Globalization.DateTimeStyles.RoundtripKind);
temp = "=DATE(" + d2.ToString("yyyy,MM,dd") + ")+TIME(" + d2.ToString("HH,mm,ss") + ")";
}
else
{
temp = "";
}
break;
}
#endregion
}
dataBlock[i, j] = temp;
}
}
Excel.Range range;
range = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A5", Missing.Value);
if (threats.Count > 0)
{
range = range.Resize[rowCount, colCount];
range.Cells.ClearFormats();
// Writes the array into Excel
// This is probably the single thing that sped up the report the most, by writing array
range.Value = dataBlock;
range.Font.Size = "10";
range.RowHeight = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Height;
}
#endregion
#region Column Headers
// Column Headers
// =================================================================================================================
eHelper.WriteHeaders("Agent Data", colCount, rowCount, stopWatch);
// This block writes the column headers
string hd = "";
for (int m = 0; m < AttribCollection.Count; m++)
{
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
hd = textInfo.ToTitleCase(AttribCollection[m].Key).Replace('_', ' ');
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[4, m + 1] = hd;
}
#endregion
#region Formatting
// Create the data filter
// =================================================================================================================
Excel.Range range2;
range2 = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A4", Missing.Value);
range2 = range2.Resize[rowCount + 1, colCount];
range2.Select();
// range2.AutoFilter("1", "<>", Excel.XlAutoFilterOperator.xlOr, "", true);
range2.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlOr, Type.Missing, true);
// Format number columns
// =================================================================================================================
for (int i = 0; i < integerCol.Count; i++)
{
Excel.Range rangeInt;
rangeInt = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)integerCol[i]) + "5", eHelper.ExcelColumnLetter((int)integerCol[i]) + (rowCount + 5).ToString());
rangeInt.NumberFormat = "0";
rangeInt.Cells.HorizontalAlignment = -4152; // Right align the number
rangeInt.Value = rangeInt.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
}
// Format date time columns
// =================================================================================================================
for (int j = 0; j < dateTimeCol.Count; j++)
{
Excel.Range rangeTime;
rangeTime = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)dateTimeCol[j]) + "5", eHelper.ExcelColumnLetter((int)dateTimeCol[j]) + (rowCount + 5).ToString());
rangeTime.NumberFormat = "[$-409]yyyy/mm/dd hh:mm AM/PM;@";
rangeTime.Cells.HorizontalAlignment = -4152; // Right align the Time
rangeTime.Value = rangeTime.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
}
// Format reference columns
// =================================================================================================================
for (int k = 0; k < referenceCol.Count; k++)
{
Excel.Range rangeReference;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)referenceCol[k]) + "5", eHelper.ExcelColumnLetter((int)referenceCol[k]) + (rowCount + 4).ToString());
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_ref, g_color_ref, b_color_ref));
rangeReference.Formula = rangeReference.Value;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)referenceCol[k]) + "4", eHelper.ExcelColumnLetter((int)referenceCol[k]) + "4");
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_refh, g_color_refh, b_color_refh));
// rangeReference.Value = rangeReference.Value;
}
if (referenceCol.Count > 0)
{
Excel.Range rangeReference;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("E2", "E2");
rangeReference.Value = "Reference lookup data";
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_refh, g_color_refh, b_color_refh));
}
// Autofit column width
// Formats the column width nicely to see the content, but not too wide and limit to maximum of 80
// =================================================================================================================
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
for (int i = 0; i < colCount; i++)
{
Excel.Range rangeCol;
rangeCol = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter(i) + "1", eHelper.ExcelColumnLetter(i) + "1");
if (Convert.ToInt32(rangeCol.ColumnWidth) > maxColumnWidth)
{
rangeCol.ColumnWidth = maxColumnWidth;
}
}
// Place the cursor in cell A2 - which is at the start of the document
// =================================================================================================================
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
#endregion
formMsg.Message("Creating pivot tables and charts for the agent data...", "", allowCancel: false);
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ScreenUpdating = false;
GenerateReport();
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ScreenUpdating = true;
formMsg.Hide();
// This is saved for viewing in the Show API window
Globals.ApiResults = JArray.Parse(results.ToString()).ToString();
}
catch (Exception ex)
{
formMsg.Hide();
MessageBox.Show(ex.Message, "Error extracting Agent data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void buttonLogin_Click(object sender, EventArgs e)
{
try
{
if (checkBoxToken.Checked)
{
string resourceString = comboBoxURL.Text.TrimEnd('/') + "/web/api/v1.6/threats/summary";
var restClient = new RestClientInterface(
endpoint: resourceString,
method: HttpVerb.GET);
var results = restClient.MakeRequest(textBoxToken.Text);
token = textBoxToken.Text;
}
else
{
string resourceString = comboBoxURL.Text.TrimEnd('/') + "/web/api/v1.6/users/login";
var restClient = new RestClientInterface(
endpoint: resourceString,
method: HttpVerb.POST,
postData: "{\"username\":\"" + comboBoxUsername.Text + "\", \"password\":\"" + textBoxPassword.Text + "\"}");
var results = restClient.MakeRequest();
dynamic x = Newtonsoft.Json.JsonConvert.DeserializeObject(results);
token = x.token;
}
labelLoginMessage.Visible = true;
SaveAll();
}
catch (Exception ex)
{
#region 2FA
if (ex.Message.Contains("Required2FAConfiguration"))
{
MessageBox.Show("Two factor authentication has been enabled for this account.\r\n\r\n" + "You need to use the SentinelOne management console to configure a two factor authenticator before logging in here.", "2FA Configuration Required", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (ex.Message.Contains("Required2FAAutentication"))
{
try
{
string authCode = "";
FormPrompt promptWin = new FormPrompt();
promptWin.StartPosition = FormStartPosition.CenterParent;
var dialogResult = promptWin.ShowDialog();
if (dialogResult == DialogResult.OK)
{
authCode = promptWin.AuthCode;
string authToken = "";
dynamic x = Newtonsoft.Json.JsonConvert.DeserializeObject(ex.Message.Substring(ex.Message.IndexOf("{")));
authToken = x.token;
string resourceString = comboBoxURL.Text.TrimEnd('/') + "/web/api/v1.6/users/auth/app?token=" + authToken;
var restClient = new RestClientInterface(
endpoint: resourceString,
method: HttpVerb.POST,
postData: "{\"code\":\"" + authCode + "\"}");
var results = restClient.MakeRequest(authToken);
string newToken = "";
dynamic y = Newtonsoft.Json.JsonConvert.DeserializeObject(results);
newToken = y.token;
token = newToken;
labelLoginMessage.Visible = true;
SaveAll();
}
else
{
return;
}
}
catch (Exception ex2)
{
MessageBox.Show(ex2.Message, "2FA Login Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
#endregion
}
else
{
MessageBox.Show(ex.Message, "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
public void GetData(string selectedObject)
{
try
{
if (!loggedIn)
{
return;
}
#region Create worksheet
CultureInfo ci = new CultureInfo("en-us");
PluralizationService ps = PluralizationService.CreateService(ci);
string selectedObjSingular = ps.Singularize(selectedObject);
// Creates a worksheet for "Threats" if one does not already exist
// =================================================================================================================
Excel.Worksheet threatsWorksheet;
try
{
threatsWorksheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item(selectedObjSingular.Replace('/', ' ') + " Data");
threatsWorksheet.Activate();
}
catch
{
threatsWorksheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
threatsWorksheet.Name = selectedObjSingular.Replace('/', ' ') + " Data";
threatsWorksheet.Activate();
}
#endregion
#region Clear worksheet
// Clear spreadsheet
eHelper.Clear();
#endregion
#region Get Data
#region Get data from server
// Extract data from SentinelOne Management Server
// =================================================================================================================
string mgmtServer = crypto.GetSettings("ExcelPlugIn", "ManagementServer");
string token = crypto.Decrypt(crypto.GetSettings("ExcelPlugIn", "Token"));
string limit = "limit=" + Globals.ApiBatch.ToString();
bool Gogo = true;
string skip = "&skip=";
int skip_count = 0;
StringBuilder results = new StringBuilder("[");
int maxColumnWidth = 80;
dynamic res = "";
int rowCountTemp = 0;
stopWatch.Start();
var JsonSettings = new Newtonsoft.Json.JsonSerializerSettings
{
NullValueHandling = Newtonsoft.Json.NullValueHandling.Include,
MissingMemberHandling = Newtonsoft.Json.MissingMemberHandling.Ignore
};
formMsg.StopProcessing = false;
formMsg.StartMessage();
this.Opacity = 0;
this.ShowInTaskbar = false;
string resourceString = "";
var restClient = new RestClientInterface();
#region Agent Passphrase
if (selectedObject == "Agents/Passphrase" || selectedObject == "Agents/Configuration")
{
#region Find All the Agents from the hidden Lookup Table sheet
Excel.Worksheet lookupSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Lookup Tables");
int rows = Convert.ToInt32(lookupSheet.Cells[3, 6].Value) + 4;
Excel.Range AgentIDs = lookupSheet.Range["F5:H" + rows.ToString()];
System.Array AgentArray;
int AgentArrayItems = 0;
AgentArray = AgentIDs.Value;
AgentArrayItems = AgentArray.GetLength(0);
int UpdateInterval = Globals.PassphraseBatch;
#endregion
for (int i = 1; i <= AgentArrayItems; i++)
{
// resourceString = mgmtServer + "/web/api/v1.6/agents/" + AgentArray.GetValue(i, 1).ToString() + "/passphrase";
resourceString = mgmtServer + "/web/api/v1.6/agents/" + AgentArray.GetValue(i, 1).ToString() + selectedObject.ToLower().Substring(selectedObject.IndexOf("/"));
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var batch_string = restClient.MakeRequest(token).ToString();
batch_string = batch_string.Replace("\"configuration\": \"", "\"configuration\": ");
batch_string = batch_string.Replace("\\\"", "\"");
batch_string = batch_string.Replace("\\\\\\\\", "\\\\");
batch_string = batch_string.Replace("}\", \"updated_at\"", "}, \"updated_at\"");
int percentComplete = 0;
if (i % UpdateInterval == 0)
{
percentComplete = (int)Math.Round((double)(100 * i) / AgentArrayItems);
formMsg.UpdateMessage("Collecting " + selectedObject.ToLower().Substring(selectedObject.IndexOf("/") + 1) + " from " + i.ToString("N0") + " of " + AgentArrayItems.ToString("N0") + " agents (" + percentComplete.ToString() + "%)...",
eHelper.ToReadableStringUpToSec(stopWatch.Elapsed) + " elapsed");
if (formMsg.StopProcessing == true)
{
formMsg.Hide();
return;
}
}
JObject oneAgent = JObject.Parse(batch_string);
var PropertyComputerName = new JProperty("computer_name", AgentArray.GetValue(i, 2) == null ? "N/A" : AgentArray.GetValue(i, 2).ToString());
var PropertyOperatingSystem = new JProperty("operating_system", AgentArray.GetValue(i, 3) == null ? "N/A" : AgentArray.GetValue(i, 3).ToString());
oneAgent.AddFirst(PropertyOperatingSystem);
oneAgent.AddFirst(PropertyComputerName);
// oneAgent.Add("operating_system", AgentArray.GetValue(i, 3) == null ? "N/A" : AgentArray.GetValue(i, 3).ToString());
// oneAgent.Add("computer_name", AgentArray.GetValue(i, 2) == null ? "N/A" : AgentArray.GetValue(i, 2).ToString());
results.Append(oneAgent.ToString()).Append(",");
rowCount++;
}
// MessageBox.Show(results.ToString());
}
#endregion
else if (selectedObject == "Groups")
{
resourceString = mgmtServer + "/web/api/v1.6/" + selectedObject.ToLower();
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var batch_string = restClient.MakeRequest(token).ToString();
res = Newtonsoft.Json.JsonConvert.DeserializeObject(batch_string, JsonSettings);
rowCountTemp = (int)res.Count;
skip_count = skip_count + Globals.ApiBatch;
results.Append(batch_string.TrimStart('[').TrimEnd(']', '\r', '\n')).Append(",");
rowCount = rowCount + rowCountTemp;
}
#region All other objects
else
{
while (Gogo)
{
resourceString = mgmtServer + "/web/api/v1.6/" + selectedObject.ToLower() + "?" + limit + skip + skip_count.ToString();
restClient.EndPoint = resourceString;
restClient.Method = HttpVerb.GET;
var batch_string = restClient.MakeRequest(token).ToString();
res = Newtonsoft.Json.JsonConvert.DeserializeObject(batch_string, JsonSettings);
rowCountTemp = (int)res.Count;
skip_count = skip_count + Globals.ApiBatch;
results.Append(batch_string.TrimStart('[').TrimEnd(']', '\r', '\n')).Append(",");
rowCount = rowCount + rowCountTemp;
if (rowCountTemp < Globals.ApiBatch)
{
Gogo = false;
}
formMsg.UpdateMessage("Loading " + selectedObject.ToLower() + " data: " + rowCount.ToString(),
eHelper.ToReadableStringUpToSec(stopWatch.Elapsed) + " elapsed");
if (formMsg.StopProcessing == true)
{
formMsg.Hide();
return;
}
}
}
#endregion
stopWatch.Stop();
formMsg.Hide();
Globals.ApiUrl = resourceString;
// results.ToString().TrimEnd(',', ' ');
results.Length--;
results.Append("]");
// MessageBox.Show(results.ToString());
if (results.ToString() == "[,]")
{
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "No " + selectedObject + " data found";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
formMsg.Hide();
return;
}
dynamic tempItem = Newtonsoft.Json.JsonConvert.DeserializeObject(results.ToString(), JsonSettings);
// =======================================================================================
Newtonsoft.Json.Linq.JArray threats = new JArray();
if (tempItem.GetType() == typeof(Newtonsoft.Json.Linq.JObject))
{
threats.Add(tempItem);
results.Append("]");
}
else
{
threats = tempItem;
}
rowCount = (int)threats.Count;
#endregion
#region Parse attribute headers
JArray ja = JArray.Parse(results.ToString());
// This is saved for viewing in the Show API window
Globals.ApiResults = ja.ToString();
// Stop processing if no data found
if (ja.Count == 0)
{
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "No " + selectedObject + " data found";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
return;
}
Dictionary <string, object> dictObj = ja[0].ToObject <Dictionary <string, object> >();
List <KeyValuePair <string, string> > AttribCollection = new List <KeyValuePair <string, string> >();
int AttribNo = 0;
for (int index = 0; index < dictObj.Count; index++)
{
var Level1 = dictObj.ElementAt(index);
string dataType = "String";
dataType = eHelper.GetDataType(Level1);
if (dataType == "Object")
{
foreach (KeyValuePair <string, object> Level2 in ((JObject)Level1.Value).ToObject <Dictionary <string, object> >())
{
dataType = eHelper.GetDataType(Level2);
if (dataType == "Object")
{
foreach (KeyValuePair <string, object> Level3 in ((JObject)Level2.Value).ToObject <Dictionary <string, object> >())
{
dataType = eHelper.GetDataType(Level3);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key + "." + Level3.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key + "." + Level2.Key + "." + Level3.Key);
}
}
else
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + Level2.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key + "." + Level2.Key);
}
}
}
else if (dataType == "Array" && Level1.Key == "engine_data")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "engine", "String"));
AttribNo++;
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "asset_name", "String"));
AttribNo++;
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key + "." + "asset_version", "String"));
AttribNo++;
}
else if (Level1.Key == "user_id")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("user_full_name", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "user_full_name");
}
else if (Level1.Key == "policy_id")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("policy_name", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "policy_name");
}
else if (Level1.Key == "group_id")
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>("group_name", "Reference"));
AttribNo++;
AddFormatter(AttribNo - 1, "Reference", "group_name");
}
else
{
AttribCollection.Insert(AttribNo, new KeyValuePair <string, string>(Level1.Key, dataType));
AttribNo++;
AddFormatter(AttribNo - 1, dataType, Level1.Key);
}
}
colCount = AttribCollection.Count;
#endregion
#endregion
#region Create Headings
// Create headings
// =================================================================================================================
Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", eHelper.ExcelColumnLetter(colCount - 1) + "1");
titleRow.Select();
titleRow.RowHeight = 33;
titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", eHelper.ExcelColumnLetter(colCount - 1) + "3");
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); //
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick
#endregion
#region Write data
// Write all the data rows
// =================================================================================================================
string[,] dataBlock = new string[rowCount, colCount];
dynamic temp = "";
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < AttribCollection.Count; j++)
{
try
{
string[] prop = AttribCollection[j].Key.Split('.');
if (prop.Length == 1)
{
temp = (threats[i][prop[0]] == null) ? "null" : threats[i][prop[0]].ToString();
}
else if (prop.Length == 2)
{
temp = (threats[i][prop[0]][prop[1]] == null) ? "null" : threats[i][prop[0]][prop[1]].ToString();
}
else if (prop.Length == 3)
{
if (threats[i][prop[0]][prop[1]] != null && threats[i][prop[0]][prop[1]][prop[2]] != null)
{
temp = (threats[i][prop[0]][prop[1]][prop[2]] == null) ? "null" : threats[i][prop[0]][prop[1]][prop[2]].ToString();
}
else
{
temp = "null";
}
}
else if (prop.Length == 4)
{
temp = (threats[i][prop[0]][prop[1]][prop[2]][prop[3]] == null) ? "null" : threats[i][prop[0]][prop[1]][prop[2]][prop[3]].ToString();
}
if (prop[0] == "created_date" || prop[0] == "meta_data" || prop[0] == "last_active_date")
{
DateTime d2 = DateTime.Parse(temp, null, System.Globalization.DateTimeStyles.RoundtripKind);
temp = "=DATE(" + d2.ToString("yyyy,MM,dd") + ")+TIME(" + d2.ToString("HH,mm,ss") + ")";
}
dataBlock[i, j] = temp;
}
catch { }
}
}
Excel.Range range;
range = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A5", Missing.Value);
if (threats.Count > 0)
{
range = range.Resize[rowCount, colCount];
range.Cells.ClearFormats();
// Writes the array into Excel
// This is probably the single thing that sped up the report the most, by writing array
range.Value = dataBlock;
range.Font.Size = "10";
range.RowHeight = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Height;
}
#endregion
#region Column Headers
// Column Headers
// =================================================================================================================
eHelper.WriteHeaders(selectedObjSingular.Replace('/', ' ') + " Data", colCount, rowCount, stopWatch);
// This block writes the column headers
string hd = "";
for (int m = 0; m < AttribCollection.Count; m++)
{
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
hd = textInfo.ToTitleCase(AttribCollection[m].Key).Replace('_', ' ');
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[4, m + 1] = hd;
}
#endregion
#region Formatting
// Create the data filter
// =================================================================================================================
Excel.Range range2;
range2 = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A4", Missing.Value);
range2 = range2.Resize[rowCount + 1, colCount];
range2.Select();
// range2.AutoFilter("1", "<>", Excel.XlAutoFilterOperator.xlOr, "", true);
range2.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlOr, Type.Missing, true);
// Format number columns
// =================================================================================================================
for (int i = 0; i < integerCol.Count; i++)
{
Excel.Range rangeInt;
rangeInt = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)integerCol[i]) + "5", eHelper.ExcelColumnLetter((int)integerCol[i]) + (rowCount + 5).ToString());
rangeInt.NumberFormat = "0";
rangeInt.Cells.HorizontalAlignment = -4152; // Right align the number
rangeInt.Value = rangeInt.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
}
// Format date time columns
// =================================================================================================================
for (int j = 0; j < dateTimeCol.Count; j++)
{
Excel.Range rangeTime;
rangeTime = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)dateTimeCol[j]) + "5", eHelper.ExcelColumnLetter((int)dateTimeCol[j]) + (rowCount + 5).ToString());
rangeTime.NumberFormat = "[$-409]yyyy/mm/dd hh:mm AM/PM;@";
rangeTime.Cells.HorizontalAlignment = -4152; // Right align the Time
rangeTime.Value = rangeTime.Value; // Strange technique and workaround to get numbers into Excel. Otherwise, Excel sees them as Text
}
// Format reference columns
// =================================================================================================================
for (int k = 0; k < referenceCol.Count; k++)
{
Excel.Range rangeReference;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)referenceCol[k]) + "5", eHelper.ExcelColumnLetter((int)referenceCol[k]) + (rowCount + 4).ToString());
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_ref, g_color_ref, b_color_ref));
rangeReference.Formula = rangeReference.Value;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter((int)referenceCol[k]) + "4", eHelper.ExcelColumnLetter((int)referenceCol[k]) + "4");
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_refh, g_color_refh, b_color_refh));
// rangeReference.Value = rangeReference.Value;
}
if (referenceCol.Count > 0)
{
Excel.Range rangeReference;
rangeReference = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("E2", "E2");
rangeReference.Value = "Reference lookup data";
rangeReference.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color_refh, g_color_refh, b_color_refh));
}
// Autofit column width
// Formats the column width nicely to see the content, but not too wide and limit to maximum of 80
// =================================================================================================================
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Columns.AutoFit();
for (int i = 0; i < colCount; i++)
{
Excel.Range rangeCol;
rangeCol = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range(eHelper.ExcelColumnLetter(i) + "1", eHelper.ExcelColumnLetter(i) + "1");
if (Convert.ToInt32(rangeCol.ColumnWidth) > maxColumnWidth)
{
rangeCol.ColumnWidth = maxColumnWidth;
}
}
// Place the cursor in cell A2 - which is at the start of the document
// =================================================================================================================
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
#endregion
}
catch (Exception ex)
{
formMsg.Hide();
MessageBox.Show(ex.Message, "Error extracting data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
