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