Beispiel #1
0
        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
        }
Beispiel #2
0
        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);
            }
        }
Beispiel #3
0
        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);
            }
        }
Beispiel #5
0
        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);
            }
        }
Beispiel #6
0
        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);
            }
        }