public override bool Execute(ProgramOptions programOptions) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.ReportJobFilePath; stepTimingFunction.StepName = programOptions.ReportJob.Status.ToString(); stepTimingFunction.StepID = (int)programOptions.ReportJob.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = 0; this.DisplayJobStepStartingStatus(programOptions); this.FilePathMap = new FilePathMap(programOptions); try { #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("Snowflake Grant Report Version {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "Snowflake Grant Differences Report"; excelReport.Workbook.Properties.Subject = String.Format("{0}<->{1}", programOptions.LeftReportFolderPath, programOptions.RightReportFolderPath); excelReport.Workbook.Properties.Comments = String.Format("Command line {0}", programOptions); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); var objectToRolePermissionCellStyle = sheet.Workbook.Styles.CreateNamedStyle("ShortPermissionStyle"); objectToRolePermissionCellStyle.Style.Font.Size = 8; fillReportParametersSheet(sheet, programOptions, excelReport.Workbook.Properties.Title); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots sheet = excelReport.Workbook.Worksheets.Add(SHEET_DIFFERENCES); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DIFFERENCES_TYPE); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DIFFERENCES_TYPE); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Converted Data"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DIFFERENCES); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); #endregion #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Differences sheet sheet = excelReport.Workbook.Worksheets[SHEET_DIFFERENCES]; logger.Info("{0} Sheet", sheet.Name); loggerConsole.Info("{0} Sheet", sheet.Name); EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.Report_RoleGrant_Differences_FilePath(), 0, typeof(GrantDifference), sheet, LIST_SHEET_START_TABLE_AT, 1); logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_DIFFERENCES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Privilege"].Position + 1).Width = 20; sheet.Column(table.Columns["ObjectType"].Position + 1).Width = 20; sheet.Column(table.Columns["ObjectName"].Position + 1).Width = 30; sheet.Column(table.Columns["GrantedTo"].Position + 1).Width = 30; sheet.Column(table.Columns["Difference"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUTCLeft"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUTCRight"].Position + 1).Width = 20; ExcelAddress cfAddressDifference = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["Difference"].Position + 1, sheet.Dimension.Rows, table.Columns["Difference"].Position + 1); var cfUserExperience = sheet.ConditionalFormatting.AddEqual(cfAddressDifference); cfUserExperience.Style.Font.Color.Color = Color.Black; cfUserExperience.Style.Fill.BackgroundColor.Color = colorDifferent; cfUserExperience.Formula = String.Format(@"=""{0}""", DIFFERENCE_DIFFERENT); cfUserExperience = sheet.ConditionalFormatting.AddEqual(cfAddressDifference); cfUserExperience.Style.Font.Color.Color = Color.Black; cfUserExperience.Style.Fill.BackgroundColor.Color = colorExtra; cfUserExperience.Formula = String.Format(@"=""{0}""", DIFFERENCE_EXTRA); cfUserExperience = sheet.ConditionalFormatting.AddEqual(cfAddressDifference); cfUserExperience.Style.Font.Color.Color = Color.Black; cfUserExperience.Style.Fill.BackgroundColor.Color = colorMissing; cfUserExperience.Formula = String.Format(@"=""{0}""", DIFFERENCE_MISSING); sheet = excelReport.Workbook.Worksheets[SHEET_DIFFERENCES_TYPE]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_DIFFERENCES_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "ObjectType", eSortType.Ascending); addRowFieldToPivot(pivot, "ObjectName", eSortType.Ascending); addRowFieldToPivot(pivot, "Privilege", eSortType.Ascending); addRowFieldToPivot(pivot, "GrantedTo", eSortType.Ascending); addRowFieldToPivot(pivot, "DifferenceDetails", eSortType.Ascending); addColumnFieldToPivot(pivot, "Difference", eSortType.Ascending); addDataFieldToPivot(pivot, "UniqueIdentifier", DataFieldFunctions.Count, "NumDifferences"); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; ExcelChart chart = sheet.Drawings.AddChart(GRAPH_DIFFERENCES_TYPE, eChartType.ColumnStacked, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); } #endregion #region Objects / Roles Differences sheet // Build the table // Object | Role 1 | Role 2 | ... | Role N // ----------------------------------------------------- // DB1 | +U, O | | | +U // DB2 | -U, O | ~U | | // DB2 | -U, O | U | | List <GrantDifference> grantDifferencesList = FileIOHelper.ReadListFromCSVFile <GrantDifference>(FilePathMap.Report_RoleGrant_Differences_FilePath(), new GrantDifferenceMap()); if (grantDifferencesList != null) { var groupObjectTypesGrouped = grantDifferencesList.GroupBy(g => g.ObjectType); foreach (var groupObjectType in groupObjectTypesGrouped) { string objectType = groupObjectType.Key; Dictionary <string, int> roleToHeaderMapping = new Dictionary <string, int>(); loggerConsole.Info("Processing grants differences for {0}", objectType); List <GrantDifference> grantDifferencesOfObjectTypeList = groupObjectType.ToList(); sheet = excelReport.Workbook.Worksheets.Add(getShortenedNameForExcelSheet(String.Format(SHEET_GRANT_DIFFERENCES_PER_OBJECT_TYPE, objectType))); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "Left"; sheet.Cells[2, 2].Value = programOptions.LeftReportFolderPath; sheet.Cells[3, 1].Value = "Right"; sheet.Cells[3, 2].Value = programOptions.RightReportFolderPath; sheet.Cells[4, 1].Value = "Type"; sheet.Cells[4, 2].Value = objectType; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 2, 3); logger.Info("{0} Sheet", sheet.Name); loggerConsole.Info("{0} Sheet", sheet.Name); int headerRowIndex = LIST_SHEET_START_TABLE_AT + 1; int roleColumnBeginIndex = 3; int roleColumnMaxIndex = roleColumnBeginIndex; // Header row sheet.Cells[headerRowIndex, 1].Value = "Full Name"; sheet.Cells[headerRowIndex, 2].Value = "Short Name"; int currentRowIndex = headerRowIndex; currentRowIndex++; var groupObjectNameGrouped = grantDifferencesOfObjectTypeList.GroupBy(g => g.ObjectName); foreach (var groupObjectName in groupObjectNameGrouped) { GrantDifference grantDifferenceObjectToOperateOn = groupObjectName.First(); sheet.Cells[currentRowIndex, 1].Value = grantDifferenceObjectToOperateOn.ObjectName; sheet.Cells[currentRowIndex, 2].Value = grantDifferenceObjectToOperateOn.EntityName; List <GrantDifference> grantDifferencesOfThisObjectList = groupObjectName.ToList(); var grantsByRoleNameGroups = grantDifferencesOfThisObjectList.GroupBy(g => g.GrantedTo); foreach (var grantsByRoleNameGroup in grantsByRoleNameGroups) { GrantDifference firstGrantDifference = grantsByRoleNameGroup.First(); int thisRoleColumnIndex = 0; if (roleToHeaderMapping.ContainsKey(firstGrantDifference.GrantedTo) == false) { // Add another Role to the header thisRoleColumnIndex = roleColumnMaxIndex; roleToHeaderMapping.Add(firstGrantDifference.GrantedTo, thisRoleColumnIndex); sheet.Cells[headerRowIndex, thisRoleColumnIndex].Value = firstGrantDifference.GrantedTo; roleColumnMaxIndex++; } else { // Previously seen thisRoleColumnIndex = roleToHeaderMapping[firstGrantDifference.GrantedTo]; } sheet.Cells[currentRowIndex, thisRoleColumnIndex].Value = grantsByRoleNameGroup.ToList().Count(); outputGrantDifferencestToCell(sheet.Cells[currentRowIndex, thisRoleColumnIndex], grantsByRoleNameGroup.ToList()); } currentRowIndex++; } range = sheet.Cells[headerRowIndex, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; try { table = sheet.Tables.Add(range, getExcelTableOrSheetSafeString(String.Format(TABLE_GRANT_DIFFERENCES_PER_OBJECT_TYPE, objectType))); } catch (ArgumentException ex) { if (ex.Message == "Tablename is not unique") { table = sheet.Tables.Add(range, String.Format("{0}_1", getExcelTableOrSheetSafeString(String.Format(TABLE_GRANT_DIFFERENCES_PER_OBJECT_TYPE, objectType)))); } } table.ShowHeader = true; table.TableStyle = TableStyles.Light18; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(1).Width = 30; sheet.Column(2).Width = 20; // Make the column for permissions headers angled downwards 45 degrees for (int i = roleColumnBeginIndex; i <= table.Columns.Count; i++) { sheet.Cells[headerRowIndex, i].Style.TextRotation = 135; sheet.Column(i).Width = 7; } // Format the cells ExcelRangeBase rangeToFormat = sheet.Cells[headerRowIndex + 1, 3, sheet.Dimension.Rows, sheet.Dimension.Columns]; rangeToFormat.StyleName = "ShortDifferencesStyle"; var cfMoreThanOne = sheet.ConditionalFormatting.AddContainsText(rangeToFormat); cfMoreThanOne.Style.Font.Color.Color = Color.Black; cfMoreThanOne.Style.Fill.BackgroundColor.Color = Color.MediumOrchid; cfMoreThanOne.Text = "-and-"; cfMoreThanOne.StopIfTrue = true; var cfMissing = sheet.ConditionalFormatting.AddContainsText(rangeToFormat); cfMissing.Style.Font.Color.Color = Color.Black; cfMissing.Style.Fill.BackgroundColor.Color = colorMissing; cfMissing.Text = "<<"; var cfExtra = sheet.ConditionalFormatting.AddContainsText(rangeToFormat); cfExtra.Style.Font.Color.Color = Color.Black; cfExtra.Style.Fill.BackgroundColor.Color = colorExtra; cfExtra.Text = ">>"; var cfDifferent = sheet.ConditionalFormatting.AddContainsText(rangeToFormat); cfDifferent.Style.Font.Color.Color = Color.Black; cfDifferent.Style.Fill.BackgroundColor.Color = colorDifferent; cfDifferent.Text = "~~"; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); } } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file string reportFilePath = FilePathMap.GrantsDifferencesExcelReportFilePath(); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(programOptions, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(jobConfiguration) == false) { return(true); } if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_WEB) == 0) { return(true); } try { loggerConsole.Info("Prepare Detected WEB Entities Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Detected WEB Entities Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Detected WEB Entities Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_WEB_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_WEB_PAGES_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WEB_PAGES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_WEB_PAGES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WEB_PAGES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_PAGE_RESOURCES_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_PAGE_RESOURCES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_PAGE_RESOURCES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_PAGE_RESOURCES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_PAGE_BUSINESS_TRANSACTIONS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_PAGE_BUSINESS_TRANSACTIONS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_PAGE_BUSINESS_TRANSACTIONS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_PAGE_BUSINESS_TRANSACTIONS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_GEO_LOCATIONS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_GEO_LOCATIONS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_GEO_LOCATIONS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_GEO_LOCATIONS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); #endregion loggerConsole.Info("Fill Detected WEB Entities Report File"); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications - All loggerConsole.Info("List of Applications - All"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications loggerConsole.Info("List of Applications"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_WEB_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Web Pages loggerConsole.Info("List of Web Pages"); sheet = excelReport.Workbook.Worksheets[SHEET_WEB_PAGES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBPagesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Web Page Resources loggerConsole.Info("List of Web Page Resources"); sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_RESOURCES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBPageResourcesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Web Page Business Transactions loggerConsole.Info("List of Web Page Business Transactions"); sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_BUSINESS_TRANSACTIONS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBPageBusinessTransactionsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Geo Locations loggerConsole.Info("List of Geo Locations"); sheet = excelReport.Workbook.Worksheets[SHEET_GEO_LOCATIONS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBGeoLocationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Detected WEB Entities Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Applications - All // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["Description"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; } #endregion #region Applications // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_WEB_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_WEB); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumPages"].Position + 1, sheet.Dimension.Rows, table.Columns["NumPages"].Position + 1); var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumAJAXRequests"].Position + 1, sheet.Dimension.Rows, table.Columns["NumAJAXRequests"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumVirtualPages"].Position + 1, sheet.Dimension.Rows, table.Columns["NumVirtualPages"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumIFrames"].Position + 1, sheet.Dimension.Rows, table.Columns["NumIFrames"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumActivity"].Position + 1, sheet.Dimension.Rows, table.Columns["NumActivity"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumNoActivity"].Position + 1, sheet.Dimension.Rows, table.Columns["NumNoActivity"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); } #endregion #region Web Pages // Make table sheet = excelReport.Workbook.Worksheets[SHEET_WEB_PAGES_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_WEB_PAGES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["PageType"].Position + 1).Width = 10; sheet.Column(table.Columns["PageName"].Position + 1).Width = 20; sheet.Column(table.Columns["FirstSegment"].Position + 1).Width = 20; sheet.Column(table.Columns["From"].Position + 1).Width = 20; sheet.Column(table.Columns["To"].Position + 1).Width = 20; sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_WEB_PAGES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_WEB_PAGES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "HasActivity"); addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending); addFilterFieldToPivot(pivot, "NumNameSegments", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "PageType"); addRowFieldToPivot(pivot, "FirstSegment"); addRowFieldToPivot(pivot, "PageName"); addDataFieldToPivot(pivot, "PageID", DataFieldFunctions.Count, "NumPages"); addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART"); addDataFieldToPivot(pivot, "TimeTotal", DataFieldFunctions.Sum, "Time"); addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls"); addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM"); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_WEB_PAGES_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; } #endregion #region Web Page Resources // Make table sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_RESOURCES_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_PAGE_RESOURCES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["PageType"].Position + 1).Width = 10; sheet.Column(table.Columns["PageName"].Position + 1).Width = 20; sheet.Column(table.Columns["ChildPageType"].Position + 1).Width = 10; sheet.Column(table.Columns["ChildPageName"].Position + 1).Width = 20; sheet.Column(table.Columns["From"].Position + 1).Width = 20; sheet.Column(table.Columns["To"].Position + 1).Width = 20; sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_RESOURCES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_PAGE_RESOURCES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "HasActivity"); addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "PageName"); addRowFieldToPivot(pivot, "ChildPageType"); addRowFieldToPivot(pivot, "ChildPageName"); addDataFieldToPivot(pivot, "ChildPageID", DataFieldFunctions.Count, "NumPages"); addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART"); addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls"); addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM"); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_PAGE_RESOURCES_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; } #endregion #region Web Page Business Transactions // Make table sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_BUSINESS_TRANSACTIONS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_PAGE_BUSINESS_TRANSACTIONS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["PageType"].Position + 1).Width = 10; sheet.Column(table.Columns["PageName"].Position + 1).Width = 20; sheet.Column(table.Columns["TierName"].Position + 1).Width = 20; sheet.Column(table.Columns["BTName"].Position + 1).Width = 20; sheet.Column(table.Columns["BTType"].Position + 1).Width = 15; sheet.Column(table.Columns["From"].Position + 1).Width = 20; sheet.Column(table.Columns["To"].Position + 1).Width = 20; sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_BUSINESS_TRANSACTIONS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_PAGE_RESOURCES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "HasActivity"); addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "PageName"); addRowFieldToPivot(pivot, "TierName"); addRowFieldToPivot(pivot, "BTName"); addDataFieldToPivot(pivot, "BTID", DataFieldFunctions.Count, "NumBTs"); addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART"); addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls"); addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM"); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_PAGE_BUSINESS_TRANSACTIONS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; } #endregion #region Geo Locations // Make table sheet = excelReport.Workbook.Worksheets[SHEET_GEO_LOCATIONS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_GEO_LOCATIONS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["LocationName"].Position + 1).Width = 15; sheet.Column(table.Columns["Country"].Position + 1).Width = 15; sheet.Column(table.Columns["Region"].Position + 1).Width = 15; sheet.Column(table.Columns["GeoCode"].Position + 1).Width = 15; sheet.Column(table.Columns["From"].Position + 1).Width = 20; sheet.Column(table.Columns["To"].Position + 1).Width = 20; sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_GEO_LOCATIONS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_PAGE_RESOURCES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "HasActivity"); addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "LocationType"); addRowFieldToPivot(pivot, "Country"); addRowFieldToPivot(pivot, "Region"); addRowFieldToPivot(pivot, "LocationName"); addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART"); addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls"); addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM"); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_GEO_LOCATIONS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet.Column(6).Width = 20; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file if (Directory.Exists(FilePathMap.ReportFolderPath()) == false) { Directory.CreateDirectory(FilePathMap.ReportFolderPath()); } string reportFilePath = FilePathMap.WEBEntitiesExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(programOptions, jobConfiguration) == false) { return(true); } try { loggerConsole.Info("Prepare APM Health Check Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER APM Health Check Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); var timelineStyle = sheet.Workbook.Styles.CreateNamedStyle("TimelineStyle"); timelineStyle.Style.Font.Name = "Consolas"; timelineStyle.Style.Font.Size = 8; fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER APM Health Check Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivot // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_CHECK_RULE_RESULTS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Rating"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.Cells[3, 1].Value = "See Summary"; sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_CHECK_RULE_RESULTS_DESCRIPTION_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT - 4 + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); #endregion #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion loggerConsole.Info("Fill APM Health Check Report File"); #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications loggerConsole.Info("List of Applications - All"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, typeof(ControllerApplication), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Health Check Results loggerConsole.Info("List of Health Check Results"); sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.APMHealthCheckRuleResultsReportFilePath(), 0, typeof(HealthCheckRuleResult), sheet, LIST_SHEET_START_TABLE_AT, 1); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerHealthCheckRuleResultsReportFilePath(), 1, typeof(HealthCheckRuleResult), sheet, sheet.Dimension.Rows + 1, 1); } else { EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerHealthCheckRuleResultsReportFilePath(), 0, typeof(HealthCheckRuleResult), sheet, LIST_SHEET_START_TABLE_AT, 1); } #endregion loggerConsole.Info("Finalize APM Health Check Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Applications // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["Description"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; } #endregion #region Health Check Results // Make table sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_HEALTH_CHECK_RULE_RESULTS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["Application"].Position + 1).Width = 20; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20; sheet.Column(table.Columns["Category"].Position + 1).Width = 30; sheet.Column(table.Columns["Code"].Position + 1).Width = 15; sheet.Column(table.Columns["Name"].Position + 1).Width = 50; sheet.Column(table.Columns["Description"].Position + 1).Width = 40; ExcelAddress cfAddressGrade = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["Grade"].Position + 1, sheet.Dimension.Rows, table.Columns["Grade"].Position + 1); var cfGrade = sheet.ConditionalFormatting.AddThreeColorScale(cfAddressGrade); cfGrade.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.LowValue.Color = colorRedFor3ColorScales; cfGrade.LowValue.Value = 1; cfGrade.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.MiddleValue.Value = 3; cfGrade.MiddleValue.Color = colorYellowFor3ColorScales; cfGrade.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.HighValue.Color = colorGreenFor3ColorScales; cfGrade.HighValue.Value = 5; sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS_DESCRIPTION_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT - 4, 1], range, PIVOT_HEALTH_CHECK_RULE_RESULTS_DESCRIPTION_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "Application"); addRowFieldToPivot(pivot, "Category"); addRowFieldToPivot(pivot, "EntityType"); addRowFieldToPivot(pivot, "Name"); addRowFieldToPivot(pivot, "Description"); addColumnFieldToPivot(pivot, "Grade", eSortType.Ascending); addDataFieldToPivot(pivot, "Name", DataFieldFunctions.Count, "Rating"); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 30; sheet.Column(6).Width = 30; } #endregion #region Health Check Results Display sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY]; List <HealthCheckRuleResult> healthCheckRuleResults = new List <HealthCheckRuleResult>(); List <HealthCheckRuleResult> healthCheckRuleResultsController = FileIOHelper.ReadListFromCSVFile(FilePathMap.ControllerHealthCheckRuleResultsReportFilePath(), new HealthCheckRuleResultReportMap()); if (healthCheckRuleResultsController != null) { healthCheckRuleResults.AddRange(healthCheckRuleResultsController); } List <HealthCheckRuleResult> healthCheckRuleResultsAPM = FileIOHelper.ReadListFromCSVFile(FilePathMap.APMHealthCheckRuleResultsReportFilePath(), new HealthCheckRuleResultReportMap()); if (healthCheckRuleResultsAPM != null) { healthCheckRuleResults.AddRange(healthCheckRuleResultsAPM); } if (healthCheckRuleResults != null) { #region Output summary of Applications by Category table // Make this following table out of the list of health rule evaluations // Controller | Application | Category1 | Category2 | Category 3 // ------------------------------------------------------------- // CntrVal | AppName | Avg(Grade)| Avg(Grade)| Avg(Grade) // To do this, we measure number of rows (Controller/Application pairs) and Columns (Category values), and build a table int numRows = healthCheckRuleResults.Select(h => String.Format("{0}/{1}", h.Controller, h.Application)).Distinct().Count(); int numColumns = healthCheckRuleResults.Select(h => h.Category).Distinct().Count(); Dictionary <string, int> categoryTableRowsLookup = new Dictionary <string, int>(numRows); Dictionary <string, int> categoryTableColumnsLookup = new Dictionary <string, int>(numColumns); List <HealthCheckRuleResult>[,] categoryTableValues = new List <HealthCheckRuleResult> [numRows, numColumns]; foreach (HealthCheckRuleResult healthCheckRuleResult in healthCheckRuleResults) { int rowIndex = 0; int columnIndex = 0; string rowIndexValue = String.Format("{0}/{1}", healthCheckRuleResult.Controller, healthCheckRuleResult.Application); string columnIndexValue = healthCheckRuleResult.Category; if (categoryTableRowsLookup.ContainsKey(rowIndexValue) == true) { rowIndex = categoryTableRowsLookup[rowIndexValue]; } else { rowIndex = categoryTableRowsLookup.Count; categoryTableRowsLookup.Add(rowIndexValue, rowIndex); } if (categoryTableColumnsLookup.ContainsKey(columnIndexValue) == true) { columnIndex = categoryTableColumnsLookup[columnIndexValue]; } else { columnIndex = categoryTableColumnsLookup.Count; categoryTableColumnsLookup.Add(columnIndexValue, columnIndex); } // Fill in the cell List <HealthCheckRuleResult> healthCheckRuleResultsInCell = categoryTableValues[rowIndex, columnIndex]; if (healthCheckRuleResultsInCell == null) { healthCheckRuleResultsInCell = new List <HealthCheckRuleResult>(); categoryTableValues[rowIndex, columnIndex] = healthCheckRuleResultsInCell; } healthCheckRuleResultsInCell.Add(healthCheckRuleResult); } // Output headers int rowTableStart = 4; int gradeColumnStart = 4; int fromRow = rowTableStart; int fromColumn = gradeColumnStart; sheet.Cells[fromRow, 1].Value = "Controller"; sheet.Cells[fromRow, 2].Value = "Application"; sheet.Cells[fromRow, 3].Value = "ApplicationID"; foreach (KeyValuePair <string, int> categoriesKVP in categoryTableColumnsLookup) { sheet.Cells[fromRow, fromColumn].Value = categoriesKVP.Key; sheet.Cells[fromRow - 1, fromColumn].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<See Details>"")", String.Format(SHEET_HEALTH_CHECK_RULE_CATEGORY_RESULTS_DISPLAY, categoriesKVP.Key)); sheet.Cells[fromRow - 1, fromColumn].StyleName = "HyperLinkStyle"; fromColumn++; } fromRow++; // Output table for (int rowIndex = 0; rowIndex < numRows; rowIndex++) { for (int columnIndex = 0; columnIndex < numColumns; columnIndex++) { List <HealthCheckRuleResult> healthCheckRuleResultsInCell = categoryTableValues[rowIndex, columnIndex]; if (healthCheckRuleResultsInCell != null && healthCheckRuleResultsInCell.Count > 0) { double gradeAverage = Math.Round((double)healthCheckRuleResultsInCell.Sum(h => h.Grade) / healthCheckRuleResultsInCell.Count, 1); sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].Value = gradeAverage; sheet.Cells[fromRow + rowIndex, 1].Value = healthCheckRuleResultsInCell[0].Controller; sheet.Cells[fromRow + rowIndex, 2].Value = healthCheckRuleResultsInCell[0].Application; sheet.Cells[fromRow + rowIndex, 3].Value = healthCheckRuleResultsInCell[0].ApplicationID; } else { sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].Value = "-"; } } } fromRow = fromRow + numRows; // Insert the table range = sheet.Cells[4, 1, 4 + numRows, 3 + numColumns]; table = sheet.Tables.Add(range, TABLE_HEALTH_CHECK_RULE_APPLICATIONS); table.ShowHeader = true; table.TableStyle = TableStyles.None; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; // Resize the columns sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["Application"].Position + 1).Width = 25; for (int columnIndex = 0; columnIndex < numColumns; columnIndex++) { sheet.Column(gradeColumnStart + columnIndex).Width = 15; // Make the header column cells wrap text for Categories headings sheet.Cells[rowTableStart, gradeColumnStart + columnIndex].Style.WrapText = true; } // Make header row taller sheet.Row(rowTableStart).Height = 40; if (sheet.Dimension.Rows > rowTableStart) { // Color code it ExcelAddress cfGradeNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, 4, sheet.Dimension.Rows, 4 + numColumns); var cfGrade = sheet.ConditionalFormatting.AddThreeColorScale(cfGradeNum); cfGrade.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.LowValue.Color = colorRedFor3ColorScales; cfGrade.LowValue.Value = 1; cfGrade.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.MiddleValue.Value = 3; cfGrade.MiddleValue.Color = colorYellowFor3ColorScales; cfGrade.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.HighValue.Color = colorGreenFor3ColorScales; cfGrade.HighValue.Value = 5; } #endregion #region Output individual categories on separate sheets // Get list of categories for which we'll be making things List <string> listOfCategories = healthCheckRuleResults.Select(h => h.Category).Distinct().ToList <string>(); foreach (string category in listOfCategories) { sheet = excelReport.Workbook.Worksheets.Add(String.Format(SHEET_HEALTH_CHECK_RULE_CATEGORY_RESULTS_DISPLAY, category)); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.Cells[3, 1].Value = "See Display"; sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_CHECK_RULE_RESULTS_DISPLAY); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); // Make this following table out of the list of health rule evaluations // Controller | Application | EntityType | EntityName | Rule 1 | Rule 2 | Rule 3 // ----------------------------------------------------------------------------- // CntrVal | AppName | APMApp | AppName | Grade | Grade | Grade (with comment of value) // To do this, we measure number of rows (Controller/Application/EntityType/EntityName quads) and Columns (Rules within Category), and build a table numRows = healthCheckRuleResults.Where(h => h.Category == category).Select(h => String.Format("{0}/{1}/{2}/{3}", h.Controller, h.Application, h.EntityType, h.EntityName)).Distinct().Count(); numColumns = healthCheckRuleResults.Where(h => h.Category == category).Select(h => h.Name).Distinct().Count(); Dictionary <string, int> nameTableRowsLookup = new Dictionary <string, int>(numRows); Dictionary <string, int> nameTableColumnsLookup = new Dictionary <string, int>(numColumns); List <HealthCheckRuleResult>[,] nameTableValues = new List <HealthCheckRuleResult> [numRows, numColumns]; foreach (HealthCheckRuleResult healthCheckRuleResult in healthCheckRuleResults) { // Only process the rules with the desired category if (healthCheckRuleResult.Category != category) { continue; } int rowIndex = 0; int columnIndex = 0; string rowIndexValue = String.Format("{0}/{1}/{2}/{3}", healthCheckRuleResult.Controller, healthCheckRuleResult.Application, healthCheckRuleResult.EntityType, healthCheckRuleResult.EntityName); string columnIndexValue = healthCheckRuleResult.Name; if (nameTableRowsLookup.ContainsKey(rowIndexValue) == true) { rowIndex = nameTableRowsLookup[rowIndexValue]; } else { rowIndex = nameTableRowsLookup.Count; nameTableRowsLookup.Add(rowIndexValue, rowIndex); } if (nameTableColumnsLookup.ContainsKey(columnIndexValue) == true) { columnIndex = nameTableColumnsLookup[columnIndexValue]; } else { columnIndex = nameTableColumnsLookup.Count; nameTableColumnsLookup.Add(columnIndexValue, columnIndex); } // Fill in the cell List <HealthCheckRuleResult> healthCheckRuleResultsInCell = nameTableValues[rowIndex, columnIndex]; if (healthCheckRuleResultsInCell == null) { healthCheckRuleResultsInCell = new List <HealthCheckRuleResult>(); nameTableValues[rowIndex, columnIndex] = healthCheckRuleResultsInCell; } healthCheckRuleResultsInCell.Add(healthCheckRuleResult); } // Output headers rowTableStart = 4; gradeColumnStart = 6; fromRow = rowTableStart; fromColumn = gradeColumnStart; sheet.Cells[fromRow, 1].Value = "Controller"; sheet.Cells[fromRow, 2].Value = "Application"; sheet.Cells[fromRow, 3].Value = "ApplicationID"; sheet.Cells[fromRow, 4].Value = "EntityType"; sheet.Cells[fromRow, 5].Value = "EntityName"; foreach (KeyValuePair <string, int> namesKVP in nameTableColumnsLookup) { sheet.Cells[fromRow, fromColumn].Value = namesKVP.Key; fromColumn++; } fromRow++; // Output table for (int rowIndex = 0; rowIndex < numRows; rowIndex++) { for (int columnIndex = 0; columnIndex < numColumns; columnIndex++) { List <HealthCheckRuleResult> healthCheckRuleResultsInCell = nameTableValues[rowIndex, columnIndex]; if (healthCheckRuleResultsInCell != null && healthCheckRuleResultsInCell.Count > 0) { double gradeAverage = Math.Round((double)healthCheckRuleResultsInCell.Sum(h => h.Grade) / healthCheckRuleResultsInCell.Count, 1); sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].Value = gradeAverage; sheet.Cells[fromRow + rowIndex, 1].Value = healthCheckRuleResultsInCell[0].Controller; sheet.Cells[fromRow + rowIndex, 2].Value = healthCheckRuleResultsInCell[0].Application; sheet.Cells[fromRow + rowIndex, 3].Value = healthCheckRuleResultsInCell[0].ApplicationID; sheet.Cells[fromRow + rowIndex, 4].Value = healthCheckRuleResultsInCell[0].EntityType; sheet.Cells[fromRow + rowIndex, 5].Value = healthCheckRuleResultsInCell[0].EntityName; StringBuilder sb = new StringBuilder(healthCheckRuleResultsInCell.Count * 128); for (int k = 0; k < healthCheckRuleResultsInCell.Count; k++) { HealthCheckRuleResult healthCheckRuleResult = healthCheckRuleResultsInCell[k]; sb.AppendFormat("{0}: {1}\n", k + 1, wordWrapString(healthCheckRuleResult.Description, 100)); } // Limit the size of the comment generated to ~2K of text because I think the Excel barfs when the comments are too long. if (sb.Length > 2500) { sb.Length = 2547; sb.Append("..."); } // Excessive comments in the workbook lead to poor use experience. Need to rethink and refactor this ExcelComment comment = sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].AddComment(sb.ToString(), healthCheckRuleResultsInCell[0].Code); comment.AutoFit = true; } else { sheet.Cells[fromRow + rowIndex, gradeColumnStart + columnIndex].Value = "-"; } } } fromRow = fromRow + numRows; // Insert the table range = sheet.Cells[4, 1, 4 + numRows, 5 + numColumns]; table = sheet.Tables.Add(range, getExcelTableOrSheetSafeString(String.Format(TABLE_HEALTH_CHECK_RULE_CATEGORY_RESULTS, category))); table.ShowHeader = true; table.TableStyle = TableStyles.None; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; // Resize the columns sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["Application"].Position + 1).Width = 25; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 25; sheet.Column(table.Columns["EntityType"].Position + 1).Width = 25; for (int columnIndex = 0; columnIndex < numColumns; columnIndex++) { sheet.Column(gradeColumnStart + columnIndex).Width = 15; // Make the header column cells wrap text for Categories headings sheet.Cells[rowTableStart, gradeColumnStart + columnIndex].Style.WrapText = true; } // Make header row taller sheet.Row(rowTableStart).Height = 50; if (sheet.Dimension.Rows > rowTableStart) { // Color code it ExcelAddress cfGradeNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, 5, sheet.Dimension.Rows, 5 + numColumns); var cfGrade = sheet.ConditionalFormatting.AddThreeColorScale(cfGradeNum); cfGrade.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.LowValue.Color = colorRedFor3ColorScales; cfGrade.LowValue.Value = 1; cfGrade.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.MiddleValue.Value = 3; cfGrade.MiddleValue.Color = colorYellowFor3ColorScales; cfGrade.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num; cfGrade.HighValue.Color = colorGreenFor3ColorScales; cfGrade.HighValue.Value = 5; } } #endregion } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath()); string reportFilePath = FilePathMap.HealthCheckResultsExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(programOptions, jobConfiguration) == false) { return(true); } if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_BIQ) == 0) { logger.Warn("No {0} targets to process", APPLICATION_TYPE_BIQ); loggerConsole.Warn("No {0} targets to process", APPLICATION_TYPE_BIQ); return(true); } try { loggerConsole.Info("Prepare Detected BIQ Entities Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Detected BIQ Entities Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Detected BIQ Entities Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_BIQ_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_SEARCHES_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_SEARCHES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_SEARCHES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_SEARCHES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_WIDGETS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WIDGETS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_WIDGETS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WIDGETS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_SAVED_METRICS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_SAVED_METRICS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_SAVED_METRICS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_SAVED_METRICS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_BUSINESS_JOURNEYS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_BUSINESS_JOURNEYS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_BUSINESS_JOURNEYS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_BUSINESS_JOURNEYS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EXPERIENCE_LEVELS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EXPERIENCE_LEVELS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EXPERIENCE_LEVELS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EXPERIENCE_LEVELS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_SCHEMAS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_FIELDS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_FIELDS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_FIELDS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_FIELDS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1); #endregion loggerConsole.Info("Fill Detected BIQ Entities Report File"); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications - All loggerConsole.Info("List of Applications - All"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, typeof(ControllerApplication), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications loggerConsole.Info("List of Applications"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_BIQ_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQApplicationsReportFilePath(), 0, typeof(BIQApplication), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Searches loggerConsole.Info("List of Searches"); sheet = excelReport.Workbook.Worksheets[SHEET_SEARCHES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQSearchesReportFilePath(), 0, typeof(BIQSearch), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Widgets loggerConsole.Info("List of Widgets"); sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQWidgetsReportFilePath(), 0, typeof(BIQWidget), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Saved Metrics loggerConsole.Info("List of Saved Metrics"); sheet = excelReport.Workbook.Worksheets[SHEET_SAVED_METRICS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQMetricsReportFilePath(), 0, typeof(BIQMetric), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Business Journeys loggerConsole.Info("List of Business Journeys"); sheet = excelReport.Workbook.Worksheets[SHEET_BUSINESS_JOURNEYS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQBusinessJourneysReportFilePath(), 0, typeof(BIQBusinessJourney), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Experience Levels loggerConsole.Info("List of Experience Levels"); sheet = excelReport.Workbook.Worksheets[SHEET_EXPERIENCE_LEVELS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQExperienceLevelsReportFilePath(), 0, typeof(BIQExperienceLevel), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Schemas loggerConsole.Info("List of Schemas"); sheet = excelReport.Workbook.Worksheets[SHEET_SCHEMAS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQSchemasReportFilePath(), 0, typeof(BIQSchema), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Schema Fields loggerConsole.Info("List of Schema Fields"); sheet = excelReport.Workbook.Worksheets[SHEET_FIELDS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BIQSchemaFieldsReportFilePath(), 0, typeof(BIQSchema), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Detected BIQ Entities Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Applications - All // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["Description"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; } #endregion #region Applications // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_BIQ_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_BIQ); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSearches"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSearches"].Position + 1); var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumMultiSearches"].Position + 1, sheet.Dimension.Rows, table.Columns["NumMultiSearches"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSingleSearches"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSingleSearches"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumLegacySearches"].Position + 1, sheet.Dimension.Rows, table.Columns["NumLegacySearches"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSavedMetrics"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSavedMetrics"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumBusinessJourneys"].Position + 1, sheet.Dimension.Rows, table.Columns["NumBusinessJourneys"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumExperienceLevels"].Position + 1, sheet.Dimension.Rows, table.Columns["NumExperienceLevels"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSchemas"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSchemas"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumFields"].Position + 1, sheet.Dimension.Rows, table.Columns["NumFields"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); } #endregion #region Searches // Make table sheet = excelReport.Workbook.Worksheets[SHEET_SEARCHES_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_SEARCHES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["SearchName"].Position + 1).Width = 20; sheet.Column(table.Columns["SearchType"].Position + 1).Width = 10; sheet.Column(table.Columns["SearchMode"].Position + 1).Width = 10; sheet.Column(table.Columns["Visualization"].Position + 1).Width = 10; sheet.Column(table.Columns["ViewMode"].Position + 1).Width = 10; sheet.Column(table.Columns["DataSource"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_SEARCHES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_SEARCHES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "ViewMode"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "DataSource"); addRowFieldToPivot(pivot, "SearchName"); addColumnFieldToPivot(pivot, "SearchMode"); addColumnFieldToPivot(pivot, "SearchType"); addDataFieldToPivot(pivot, "NumWidgets", DataFieldFunctions.Sum, "NumWidgets"); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_SEARCHES_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; } #endregion #region Widgets // Make table sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_WIDGETS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["SearchName"].Position + 1).Width = 20; sheet.Column(table.Columns["SearchType"].Position + 1).Width = 10; sheet.Column(table.Columns["WidgetName"].Position + 1).Width = 15; sheet.Column(table.Columns["WidgetType"].Position + 1).Width = 10; sheet.Column(table.Columns["DataSource"].Position + 1).Width = 15; sheet.Column(table.Columns["StartTime"].Position + 1).Width = 20; sheet.Column(table.Columns["EndTime"].Position + 1).Width = 20; sheet.Column(table.Columns["StartTimeUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["EndTime"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_WIDGETS_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "Resolution", eSortType.Ascending); addFilterFieldToPivot(pivot, "IsStacking"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "DataSource"); addRowFieldToPivot(pivot, "SearchName"); addRowFieldToPivot(pivot, "WidgetName"); addColumnFieldToPivot(pivot, "WidgetType"); addDataFieldToPivot(pivot, "WidgetID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_WIDGETS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; } #endregion #region Saved Metrics // Make table sheet = excelReport.Workbook.Worksheets[SHEET_SAVED_METRICS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_SAVED_METRICS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["MetricName"].Position + 1).Width = 20; sheet.Column(table.Columns["DataSource"].Position + 1).Width = 15; sheet.Column(table.Columns["EventType"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_SAVED_METRICS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_SAVED_METRICS_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "IsEnabled"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "DataSource"); addRowFieldToPivot(pivot, "MetricName"); addColumnFieldToPivot(pivot, "LastExecStatus"); addDataFieldToPivot(pivot, "MetricName", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_SAVED_METRICS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; } #endregion #region Business Journeys // Make table sheet = excelReport.Workbook.Worksheets[SHEET_BUSINESS_JOURNEYS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_BUSINESS_JOURNEYS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["JourneyName"].Position + 1).Width = 20; sheet.Column(table.Columns["Stages"].Position + 1).Width = 30; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_BUSINESS_JOURNEYS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_BUSINESS_JOURNEY_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "NumStages", eSortType.Ascending); addFilterFieldToPivot(pivot, "IsEnabled"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "KeyField"); addRowFieldToPivot(pivot, "JourneyName"); addDataFieldToPivot(pivot, "JourneyID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_BUSINESS_JOURNEY_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; } #endregion #region Experience Levels // Make table sheet = excelReport.Workbook.Worksheets[SHEET_EXPERIENCE_LEVELS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_EXPERIENCE_LEVELS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["ExperienceLevelName"].Position + 1).Width = 20; sheet.Column(table.Columns["DataSource"].Position + 1).Width = 15; sheet.Column(table.Columns["EventField"].Position + 1).Width = 15; sheet.Column(table.Columns["StartOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["StartOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_EXPERIENCE_LEVELS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_EXPERIENCE_LEVELS_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "IsActive"); addFilterFieldToPivot(pivot, "EventField", eSortType.Ascending); addFilterFieldToPivot(pivot, "ThresholdOperator", eSortType.Ascending); addFilterFieldToPivot(pivot, "ThresholdValue", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "DataSource"); addRowFieldToPivot(pivot, "Criteria"); addRowFieldToPivot(pivot, "ExperienceLevelName"); addColumnFieldToPivot(pivot, "Period"); addDataFieldToPivot(pivot, "ExperienceLevelName", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_EXPERIENCE_LEVELS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; } #endregion #region Schemas // Make table sheet = excelReport.Workbook.Worksheets[SHEET_SCHEMAS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_SCHEMAS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["SchemaName"].Position + 1).Width = 20; } #endregion #region Schema Fields // Make table sheet = excelReport.Workbook.Worksheets[SHEET_FIELDS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_FIELDS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["SchemaName"].Position + 1).Width = 20; sheet.Column(table.Columns["FieldName"].Position + 1).Width = 20; sheet.Column(table.Columns["FieldType"].Position + 1).Width = 15; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_FIELDS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_FIELD_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "Category", eSortType.Ascending); addFilterFieldToPivot(pivot, "NumParents", eSortType.Ascending); addFilterFieldToPivot(pivot, "IsHidden"); addFilterFieldToPivot(pivot, "IsDeleted"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "SchemaName"); addRowFieldToPivot(pivot, "FieldName"); addColumnFieldToPivot(pivot, "FieldType"); addDataFieldToPivot(pivot, "FieldName", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_FIELD_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath()); string reportFilePath = FilePathMap.BIQEntitiesExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(jobConfiguration) == false) { return(true); } try { loggerConsole.Info("Prepare Snapshots Method Calls Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Snapshots Method Call Lines Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(REPORT_SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Snapshots Method Call Lines Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(REPORT_SHEET_TOC); #endregion #region Entity sheets and their associated pivot // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(REPORT_SNAPSHOTS_SHEET_CONTROLLERS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_SNAPSHOTS_SHEET_APPLICATIONS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Type"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.Cells[3, 1].Value = "See Location"; sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.Cells[4, 1].Value = "See Timeline"; sheet.Cells[4, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT); sheet.Cells[4, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_SNAPSHOTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_SNAPSHOTS_PIVOT_SHEET_CHART_HEIGHT + 5, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_SNAPSHOTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_SNAPSHOTS_PIVOT_SHEET_CHART_HEIGHT + 3, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_SNAPSHOTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_SNAPSHOTS_PIVOT_SHEET_CHART_HEIGHT + 9, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_OCCURRENCES); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_OCCURRENCES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_OCCURRENCES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_OCCURRENCES); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_SNAPSHOTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_SNAPSHOTS_PIVOT_SHEET_CHART_HEIGHT + 4, 1); #endregion #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion loggerConsole.Info("Fill Snapshots Method Call Lines Report File"); #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_CONTROLLERS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllersReportFilePath(), 0, sheet, REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications loggerConsole.Info("List of Applications"); sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_APPLICATIONS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationSnapshotsReportFilePath(), 0, sheet, REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Method Call Lines loggerConsole.Info("List of Method Call Lines"); sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.SnapshotsMethodCallLinesReportFilePath(), 0, sheet, REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, 1); #endregion #region Method Call Occurrences loggerConsole.Info("List of Method Call Occurrences"); sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_OCCURRENCES]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.SnapshotsMethodCallLinesOccurrencesReportFilePath(), 0, sheet, REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Snapshots Method Call Lines Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_CONTROLLERS]; logger.Info("Controllers Sheet ({0} rows)", sheet.Dimension.Rows); loggerConsole.Info("Controllers Sheet ({0} rows)", sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_SNAPSHOTS_TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["UserName"].Position + 1).Width = 25; } #endregion #region Applications // Make table sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_APPLICATIONS]; logger.Info("Applications Sheet ({0} rows)", sheet.Dimension.Rows); loggerConsole.Info("Applications Sheet ({0} rows)", sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_SNAPSHOTS_TABLE_APPLICATIONS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; adjustColumnsOfEntityRowTableInMetricReport(EntityApplication.ENTITY_TYPE, sheet, table); ExcelAddress cfAddressNum = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshots"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshots"].Position + 1); var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsNormal"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsNormal"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsVerySlow"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsVerySlow"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsStall"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsStall"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsSlow"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsSlow"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsError"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsError"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); } #endregion #region Method Call Lines // Make table sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES]; logger.Info("Method Call Lines Sheet ({0} rows)", sheet.Dimension.Rows); loggerConsole.Info("Method Call Lines Sheet ({0} rows)", sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1) { range = sheet.Cells[REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_SNAPSHOTS_TABLE_METHOD_CALL_LINES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["TierName"].Position + 1).Width = 20; sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20; sheet.Column(table.Columns["BTName"].Position + 1).Width = 20; sheet.Column(table.Columns["SegmentUserExperience"].Position + 1).Width = 10; sheet.Column(table.Columns["SnapshotUserExperience"].Position + 1).Width = 10; sheet.Column(table.Columns["RequestID"].Position + 1).Width = 20; sheet.Column(table.Columns["SegmentID"].Position + 1).Width = 10; sheet.Column(table.Columns["Type"].Position + 1).Width = 10; sheet.Column(table.Columns["Framework"].Position + 1).Width = 15; sheet.Column(table.Columns["FullNameIndent"].Position + 1).Width = 45; sheet.Column(table.Columns["ExitCalls"].Position + 1).Width = 15; sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20; sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20; ExcelAddress cfAddressUserExperience = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["SegmentUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SegmentUserExperience"].Position + 1); addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience); cfAddressUserExperience = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["SnapshotUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SnapshotUserExperience"].Position + 1); addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience); sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_SNAPSHOTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_SNAPSHOTS_PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, REPORT_SNAPSHOTS_PIVOT_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "ElementType"); addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending); addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending); addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending); addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "TierName"); addRowFieldToPivot(pivot, "BTName"); addRowFieldToPivot(pivot, "FullName"); addColumnFieldToPivot(pivot, "Type", eSortType.Ascending); addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending); addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average); ExcelChart chart = sheet.Drawings.AddChart(REPORT_SNAPSHOTS_PIVOT_METHOD_CALL_LINES_GRAPH_TYPE_EXEC_AVERAGE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_SNAPSHOTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_SNAPSHOTS_PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, REPORT_SNAPSHOTS_PIVOT_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "ElementType"); addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending); addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending); addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending); addRowFieldToPivot(pivot, "Type"); addRowFieldToPivot(pivot, "Framework"); addRowFieldToPivot(pivot, "FullName"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "TierName"); addRowFieldToPivot(pivot, "BTName"); addColumnFieldToPivot(pivot, "ExecRange", eSortType.Ascending); addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Count); chart = sheet.Drawings.AddChart(REPORT_SNAPSHOTS_PIVOT_METHOD_CALL_LINES_GRAPH_LOCATION_EXEC_AVERAGE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet.Column(6).Width = 20; sheet.Column(7).Width = 20; sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_SNAPSHOTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_SNAPSHOTS_PIVOT_SHEET_CHART_HEIGHT + 6, 1], range, REPORT_SNAPSHOTS_PIVOT_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "ElementType"); addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending); addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending); addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending); addFilterFieldToPivot(pivot, "Class", eSortType.Ascending); addFilterFieldToPivot(pivot, "Method", eSortType.Ascending); addFilterFieldToPivot(pivot, "FullName", eSortType.Ascending); addFilterFieldToPivot(pivot, "BTName", eSortType.Ascending); addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending); ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]); fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes); fieldR.Compact = false; fieldR.Outline = false; addColumnFieldToPivot(pivot, "Type", eSortType.Ascending); addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending); addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average); chart = sheet.Drawings.AddChart(REPORT_SNAPSHOTS_PIVOT_METHOD_CALL_LINES_GRAPH_TIMELINE_EXEC_AVERAGE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; } #endregion #region Method Call Occurrences // Make table sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_OCCURRENCES]; logger.Info("Method Call Occurrences Sheet ({0} rows)", sheet.Dimension.Rows); loggerConsole.Info("Method Call Occurrences Sheet ({0} rows)", sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_SNAPSHOTS_TABLE_METHOD_CALL_LINES_OCCURRENCES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["TierName"].Position + 1).Width = 20; sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20; sheet.Column(table.Columns["BTName"].Position + 1).Width = 20; sheet.Column(table.Columns["SegmentUserExperience"].Position + 1).Width = 10; sheet.Column(table.Columns["SnapshotUserExperience"].Position + 1).Width = 10; sheet.Column(table.Columns["RequestID"].Position + 1).Width = 20; sheet.Column(table.Columns["SegmentID"].Position + 1).Width = 10; sheet.Column(table.Columns["Type"].Position + 1).Width = 10; sheet.Column(table.Columns["Framework"].Position + 1).Width = 15; sheet.Column(table.Columns["FullName"].Position + 1).Width = 45; ExcelAddress cfAddressUserExperience = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["SegmentUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SegmentUserExperience"].Position + 1); addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience); cfAddressUserExperience = new ExcelAddress(REPORT_SNAPSHOTS_LIST_SHEET_START_TABLE_AT + 1, table.Columns["SnapshotUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SnapshotUserExperience"].Position + 1); addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience); sheet = excelReport.Workbook.Worksheets[REPORT_SNAPSHOTS_SHEET_METHOD_CALL_LINES_OCCURRENCES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_SNAPSHOTS_PIVOT_SHEET_START_PIVOT_AT + REPORT_SNAPSHOTS_PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, REPORT_SNAPSHOTS_PIVOT_METHOD_CALL_LINES_OCCURRENCES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending); addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending); addFilterFieldToPivot(pivot, "BTName", eSortType.Ascending); addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending); addRowFieldToPivot(pivot, "NumCalls", eSortType.Ascending); addRowFieldToPivot(pivot, "FullName", eSortType.Ascending); addColumnFieldToPivot(pivot, "Type", eSortType.Ascending); addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending); addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average); ExcelChart chart = sheet.Drawings.AddChart(REPORT_SNAPSHOTS_PIVOT_METHOD_CALL_LINES_OCCURRENCES_GRAPH_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[REPORT_SHEET_TOC]; sheet.Cells[1, 1].Value = "Sheet Name"; sheet.Cells[1, 2].Value = "# Entities"; sheet.Cells[1, 3].Value = "Link"; int rowNum = 1; foreach (ExcelWorksheet s in excelReport.Workbook.Worksheets) { rowNum++; sheet.Cells[rowNum, 1].Value = s.Name; sheet.Cells[rowNum, 3].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", s.Name); sheet.Cells[rowNum, 3].StyleName = "HyperLinkStyle"; if (s.Tables.Count > 0) { table = s.Tables[0]; sheet.Cells[rowNum, 2].Value = table.Address.Rows - 1; } } range = sheet.Cells[1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_SNAPSHOTS_TABLE_TOC); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Sheet Name"].Position + 1).Width = 25; sheet.Column(table.Columns["# Entities"].Position + 1).Width = 25; #endregion #region Save file if (Directory.Exists(FilePathMap.ReportFolderPath()) == false) { Directory.CreateDirectory(FilePathMap.ReportFolderPath()); } string reportFilePath = FilePathMap.SnapshotMethodCallsExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(jobConfiguration) == false) { return(true); } try { loggerConsole.Info("Prepare Events and Health Rule Violations Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Events and Health Rule Violations Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Events and Health Rule Violations Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENTS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENTS_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.Cells[3, 1].Value = "See Duration"; sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENTS_TIMELINE_PIVOT); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENTS_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_EVENTS_TIMELINE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_EVENTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 7, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_RULE_VIOLATIONS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_RULE_VIOLATIONS_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_RULE_VIOLATIONS_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_HEALTH_RULE_VIOLATIONS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_AUDIT_EVENTS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_AUDIT_EVENTS_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.Cells[3, 1].Value = "See Duration"; sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", AUDIT_SHEET_EVENTS_TIMELINE_PIVOT); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_AUDIT_EVENTS_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_AUDIT_EVENTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1); sheet = excelReport.Workbook.Worksheets.Add(AUDIT_SHEET_EVENTS_TIMELINE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_AUDIT_EVENTS); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_NOTIFICATIONS); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); #endregion #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion loggerConsole.Info("Fill Events and Health Rule Violations Report File"); #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications loggerConsole.Info("List of Applications"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventsSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Events loggerConsole.Info("List of Events"); sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationEventsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Health Rule Violation Events loggerConsole.Info("List of Health Rule Violation Events"); sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_RULE_VIOLATIONS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationHealthRuleViolationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Audit Events loggerConsole.Info("List of Audit Events"); sheet = excelReport.Workbook.Worksheets[SHEET_AUDIT_EVENTS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.AuditEventsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Notifications loggerConsole.Info("List of Notifications"); sheet = excelReport.Workbook.Worksheets[SHEET_NOTIFICATIONS]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.NotificationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Events and Health Rule Violations Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Applications // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; adjustColumnsOfEntityRowTableInMetricReport(APMApplication.ENTITY_TYPE, sheet, table); ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEvents"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEvents"].Position + 1); var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsInfo"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsInfo"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsWarning"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsWarning"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumEventsError"].Position + 1, sheet.Dimension.Rows, table.Columns["NumEventsError"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolations"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolations"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolationsWarning"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolationsWarning"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumHRViolationsCritical"].Position + 1, sheet.Dimension.Rows, table.Columns["NumHRViolationsCritical"].Position + 1); cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); } #endregion #region Events // Make table sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_EVENTS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["EventID"].Position + 1).Width = 10; sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20; sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["Summary"].Position + 1).Width = 35; sheet.Column(table.Columns["Type"].Position + 1).Width = 20; sheet.Column(table.Columns["SubType"].Position + 1).Width = 20; sheet.Column(table.Columns["TierName"].Position + 1).Width = 20; sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20; sheet.Column(table.Columns["BTName"].Position + 1).Width = 20; sheet.Column(table.Columns["TriggeredEntityType"].Position + 1).Width = 20; sheet.Column(table.Columns["TriggeredEntityName"].Position + 1).Width = 20; sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_EVENTS_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "Type"); addRowFieldToPivot(pivot, "SubType"); addRowFieldToPivot(pivot, "TierName"); addRowFieldToPivot(pivot, "BTName"); addRowFieldToPivot(pivot, "NodeName"); addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending); addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_EVENTS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet.Column(6).Width = 20; sheet.Column(7).Width = 20; sheet = excelReport.Workbook.Worksheets[SHEET_EVENTS_TIMELINE_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1], range, PIVOT_EVENTS_TIMELINE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "ApplicationName"); addFilterFieldToPivot(pivot, "TierName"); addFilterFieldToPivot(pivot, "BTName"); addFilterFieldToPivot(pivot, "TriggeredEntityName"); addFilterFieldToPivot(pivot, "ApplicationName"); ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]); fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes); fieldR.Compact = false; fieldR.Outline = false; addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending); addColumnFieldToPivot(pivot, "Type", eSortType.Ascending); addColumnFieldToPivot(pivot, "SubType", eSortType.Ascending); addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count); chart = sheet.Drawings.AddChart(GRAPH_EVENTS_TIMELINE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; } #endregion #region Health Rule Violation Events // Make table sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_RULE_VIOLATIONS]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_HEALTH_RULE_VIOLATION_EVENTS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["EventID"].Position + 1).Width = 10; sheet.Column(table.Columns["From"].Position + 1).Width = 25; sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["To"].Position + 1).Width = 25; sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["HealthRuleName"].Position + 1).Width = 20; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20; sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_RULE_VIOLATIONS_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_HEALTH_RULE_VIOLATION_EVENTS_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "Status"); addRowFieldToPivot(pivot, "HealthRuleName"); addRowFieldToPivot(pivot, "EntityType"); addRowFieldToPivot(pivot, "EntityName"); addColumnFieldToPivot(pivot, "Severity", eSortType.Ascending); addDataFieldToPivot(pivot, "EventID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_HEALTH_RULE_VIOLATION_EVENTS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet.Column(6).Width = 20; } #endregion #region Audit Events // Make table sheet = excelReport.Workbook.Worksheets[SHEET_AUDIT_EVENTS]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_AUDIT_EVENTS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["Username"].Position + 1).Width = 20; sheet.Column(table.Columns["LoginType"].Position + 1).Width = 15; sheet.Column(table.Columns["Action"].Position + 1).Width = 15; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 30; sheet.Column(table.Columns["EntityType"].Position + 1).Width = 20; sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20; sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20; sheet = excelReport.Workbook.Worksheets[SHEET_AUDIT_EVENTS_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_AUDIT_EVENTS_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "Action"); addRowFieldToPivot(pivot, "EntityType"); addRowFieldToPivot(pivot, "EntityName"); addColumnFieldToPivot(pivot, "LoginType", eSortType.Ascending); addColumnFieldToPivot(pivot, "UserName", eSortType.Ascending); addDataFieldToPivot(pivot, "EntityID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_AUDIT_EVENTS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 30; sheet = excelReport.Workbook.Worksheets[AUDIT_SHEET_EVENTS_TIMELINE_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_AUDIT_EVENTS_TIMELINE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "UserName"); ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]); fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes); fieldR.Compact = false; fieldR.Outline = false; addColumnFieldToPivot(pivot, "Action", eSortType.Ascending); addColumnFieldToPivot(pivot, "EntityType", eSortType.Ascending); addColumnFieldToPivot(pivot, "EntityName", eSortType.Ascending); addDataFieldToPivot(pivot, "EntityID", DataFieldFunctions.Count); chart = sheet.Drawings.AddChart(GRAPH_AUDIT_EVENTS_TIMELINE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; } #endregion #region Notifications // Make table sheet = excelReport.Workbook.Worksheets[SHEET_NOTIFICATIONS]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_NOTIFICATIONS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["EventID"].Position + 1).Width = 10; sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20; sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["Summary"].Position + 1).Width = 35; sheet.Column(table.Columns["Type"].Position + 1).Width = 20; sheet.Column(table.Columns["SubType"].Position + 1).Width = 20; sheet.Column(table.Columns["TierName"].Position + 1).Width = 20; sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20; sheet.Column(table.Columns["BTName"].Position + 1).Width = 20; sheet.Column(table.Columns["TriggeredEntityType"].Position + 1).Width = 20; sheet.Column(table.Columns["TriggeredEntityName"].Position + 1).Width = 20; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file if (Directory.Exists(FilePathMap.ReportFolderPath()) == false) { Directory.CreateDirectory(FilePathMap.ReportFolderPath()); } string reportFilePath = FilePathMap.EventsAndHealthRuleViolationsExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(jobConfiguration) == false) { return(true); } try { loggerConsole.Info("Prepare Licenses Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Licenses Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Licenses Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_ACCOUNTS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSES_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSES_USAGE_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSES_USAGE_TIMELINE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSES_USAGE_TIMELINE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSES_USAGE_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSE_RULES_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSE_RULES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSE_RULES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSE_RULES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSE_RULES_USAGE_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSE_RULES_USAGE_TIMELINE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSE_RULES_USAGE_TIMELINE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSE_RULES_USAGE_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 5, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSE_RULE_SCOPES_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSE_RULE_SCOPES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_LICENSE_RULE_SCOPES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_LICENSE_RULE_SCOPES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1); #endregion loggerConsole.Info("Fill Licenses Report File"); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications - All loggerConsole.Info("List of Applications - All"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Accounts loggerConsole.Info("List of Accounts"); sheet = excelReport.Workbook.Worksheets[SHEET_ACCOUNTS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.LicenseAccountReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Licenses loggerConsole.Info("List of Licenses"); sheet = excelReport.Workbook.Worksheets[SHEET_LICENSES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.LicensesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region License Usage loggerConsole.Info("License Usage"); sheet = excelReport.Workbook.Worksheets[SHEET_LICENSES_USAGE_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.LicenseUsageAccountReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region License Rules loggerConsole.Info("List of License Rules"); sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.LicenseRulesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region License Rules Usage loggerConsole.Info("License Rules Usage"); sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULES_USAGE_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.LicenseUsageRulesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region License Rule Scopes loggerConsole.Info("List of License Rule Scopes"); sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULE_SCOPES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.LicenseRuleScopesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Dashboards Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Applications - All // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["Description"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; } #endregion #region Accounts // Make table sheet = excelReport.Workbook.Worksheets[SHEET_ACCOUNTS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_ACCOUNTS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["AccountName"].Position + 1).Width = 15; sheet.Column(table.Columns["AccountNameGlobal"].Position + 1).Width = 20; sheet.Column(table.Columns["AccessKey1"].Position + 1).Width = 15; sheet.Column(table.Columns["AccessKey2"].Position + 1).Width = 35; sheet.Column(table.Columns["ExpirationDate"].Position + 1).Width = 20; } #endregion #region Licenses // Make table sheet = excelReport.Workbook.Worksheets[SHEET_LICENSES_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_LICENSES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["AccountName"].Position + 1).Width = 15; sheet.Column(table.Columns["AgentType"].Position + 1).Width = 20; sheet.Column(table.Columns["StartDate"].Position + 1).Width = 20; sheet.Column(table.Columns["ExpirationDate"].Position + 1).Width = 20; sheet.Column(table.Columns["From"].Position + 1).Width = 20; sheet.Column(table.Columns["To"].Position + 1).Width = 20; sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_LICENSES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_LICENSES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "Licenses", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addColumnFieldToPivot(pivot, "AgentType"); addDataFieldToPivot(pivot, "Peak", DataFieldFunctions.Sum); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_LICENSES_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; } #endregion #region License Usage // Make table sheet = excelReport.Workbook.Worksheets[SHEET_LICENSES_USAGE_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_LICENSES_USAGE); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["AccountName"].Position + 1).Width = 15; sheet.Column(table.Columns["RuleName"].Position + 1).Width = 15; sheet.Column(table.Columns["AgentType"].Position + 1).Width = 20; sheet.Column(table.Columns["LicenseEventTime"].Position + 1).Width = 20; sheet.Column(table.Columns["LicenseEventTimeUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_LICENSES_USAGE_TIMELINE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_LICENSES_USAGE_TIMELINE); setDefaultPivotTableSettings(pivot); ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["LicenseEventTime"]); fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours); fieldR.Compact = false; fieldR.Outline = false; addColumnFieldToPivot(pivot, "Controller", eSortType.Ascending); addColumnFieldToPivot(pivot, "AgentType", eSortType.Ascending); addDataFieldToPivot(pivot, "Value", DataFieldFunctions.Average); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_LICENSES_USAGE_TIMELINE, eChartType.Line, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region License Rules // Make table sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULES_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_LICENSE_RULES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["AccountName"].Position + 1).Width = 15; sheet.Column(table.Columns["RuleName"].Position + 1).Width = 25; sheet.Column(table.Columns["AgentType"].Position + 1).Width = 20; sheet.Column(table.Columns["From"].Position + 1).Width = 20; sheet.Column(table.Columns["To"].Position + 1).Width = 20; sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_LICENSE_RULES_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "NumApplications"); addRowFieldToPivot(pivot, "RuleName"); addColumnFieldToPivot(pivot, "AgentType"); addDataFieldToPivot(pivot, "Licenses", DataFieldFunctions.Sum); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_LICENSE_RULES_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 10; sheet.Column(3).Width = 20; } #endregion #region License Rules Usage // Make table sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULES_USAGE_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_LICENSE_RULES_USAGE); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["AccountName"].Position + 1).Width = 15; sheet.Column(table.Columns["RuleName"].Position + 1).Width = 25; sheet.Column(table.Columns["AgentType"].Position + 1).Width = 20; sheet.Column(table.Columns["LicenseEventTime"].Position + 1).Width = 20; sheet.Column(table.Columns["LicenseEventTimeUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULES_USAGE_TIMELINE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_LICENSE_RULES_USAGE_TIMELINE); setDefaultPivotTableSettings(pivot); ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["LicenseEventTime"]); fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours); fieldR.Compact = false; fieldR.Outline = false; addColumnFieldToPivot(pivot, "Controller", eSortType.Ascending); addColumnFieldToPivot(pivot, "RuleName", eSortType.Ascending); addColumnFieldToPivot(pivot, "AgentType", eSortType.Ascending); addDataFieldToPivot(pivot, "Value", DataFieldFunctions.Average); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_LICENSE_RULES_USAGE_TIMELINE, eChartType.Line, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region License Rule Scopes // Make table sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULE_SCOPES_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_LICENSE_RULE_SCOPES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["AccountName"].Position + 1).Width = 15; sheet.Column(table.Columns["RuleName"].Position + 1).Width = 25; sheet.Column(table.Columns["ScopeSelector"].Position + 1).Width = 15; sheet.Column(table.Columns["MatchType"].Position + 1).Width = 10; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 25; sheet.Column(table.Columns["EntityType"].Position + 1).Width = 15; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_LICENSE_RULE_SCOPES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_LICENSE_RULE_SCOPES_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "RuleName"); addRowFieldToPivot(pivot, "MatchType"); addRowFieldToPivot(pivot, "EntityName"); addColumnFieldToPivot(pivot, "EntityType"); addDataFieldToPivot(pivot, "RuleID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_LICENSE_RULE_SCOPES_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file if (Directory.Exists(FilePathMap.ReportFolderPath()) == false) { Directory.CreateDirectory(FilePathMap.ReportFolderPath()); } string reportFilePath = FilePathMap.LicensesExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(jobConfiguration) == false) { return(true); } try { loggerConsole.Info("Prepare Users, Groups, Roles and Permissions Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER RBAC Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER RBAC Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "Types of Users"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USERS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USERS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USERS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUPS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "Types of Groups"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_GROUPS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUPS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_GROUPS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLES_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "Types of Roles"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_ROLES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_ROLES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_PERMISSIONS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "Types of Permissions"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_PERMISSIONS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_PERMISSIONS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_PERMISSIONS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "Types of User Permissions"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(REPORT_RBAC_LIST_SHEET_START_TABLE_AT + 1, 1); #endregion loggerConsole.Info("Fill Users, Groups, Roles and Permissions Report File"); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RBACControllerSummaryReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Users loggerConsole.Info("List of Users"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.UsersReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Groups loggerConsole.Info("List of Groups"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.GroupsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Roles loggerConsole.Info("List of Roles"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RolesReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Permissions loggerConsole.Info("List of Permissions"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.PermissionsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region User Permissions loggerConsole.Info("List of User Permissions"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.UserPermissionsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Group Memberships loggerConsole.Info("List of Group Memberships"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.GroupMembershipsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion #region Role Memberships loggerConsole.Info("List of Role Memberships"); sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.RoleMembershipsReportFilePath(), 0, sheet, REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Users, Groups, Roles and Permissions Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["SecurityProvider"].Position + 1).Width = 20; } #endregion #region Users // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_USERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["UserName"].Position + 1).Width = 20; sheet.Column(table.Columns["DisplayName"].Position + 1).Width = 20; sheet.Column(table.Columns["SecurityProvider"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USERS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_USERS); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "CreatedBy"); addFilterFieldToPivot(pivot, "UpdatedBy"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "UserName"); addColumnFieldToPivot(pivot, "SecurityProvider", eSortType.Ascending); addDataFieldToPivot(pivot, "UserID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_USERS_GRAPH, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region Groups // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_GROUPS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["GroupName"].Position + 1).Width = 20; sheet.Column(table.Columns["Description"].Position + 1).Width = 20; sheet.Column(table.Columns["SecurityProvider"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUPS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_GROUPS); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "CreatedBy"); addFilterFieldToPivot(pivot, "UpdatedBy"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "GroupName"); addColumnFieldToPivot(pivot, "SecurityProvider", eSortType.Ascending); addDataFieldToPivot(pivot, "GroupID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_GROUPS_GRAPH, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region Roles // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_ROLES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["RoleName"].Position + 1).Width = 30; sheet.Column(table.Columns["Description"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_ROLES); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "CreatedBy"); addFilterFieldToPivot(pivot, "UpdatedBy"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "RoleName"); addDataFieldToPivot(pivot, "RoleID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_ROLES_GRAPH, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region Permissions // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_PERMISSIONS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["RoleName"].Position + 1).Width = 20; sheet.Column(table.Columns["PermissionName"].Position + 1).Width = 20; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_PERMISSIONS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_PERMISSIONS); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "RoleName"); addRowFieldToPivot(pivot, "EntityName"); addRowFieldToPivot(pivot, "PermissionName"); addColumnFieldToPivot(pivot, "Allowed"); addDataFieldToPivot(pivot, "PermissionID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_PERMISSIONS_GRAPH, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; } #endregion #region User Permissions // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_USER_PERMISSIONS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["UserName"].Position + 1).Width = 20; sheet.Column(table.Columns["UserSecurityProvider"].Position + 1).Width = 15; sheet.Column(table.Columns["GroupName"].Position + 1).Width = 20; sheet.Column(table.Columns["GroupSecurityProvider"].Position + 1).Width = 15; sheet.Column(table.Columns["RoleName"].Position + 1).Width = 30; sheet.Column(table.Columns["PermissionName"].Position + 1).Width = 20; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_USER_PERMISSIONS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[REPORT_RBAC_PIVOT_SHEET_START_PIVOT_AT + REPORT_RBAC_PIVOT_SHEET_CHART_HEIGHT, 1], range, REPORT_RBAC_PIVOT_USER_PERMISSIONS); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "UserName"); addRowFieldToPivot(pivot, "GroupName"); addRowFieldToPivot(pivot, "RoleName"); addRowFieldToPivot(pivot, "EntityName"); addRowFieldToPivot(pivot, "PermissionName"); addColumnFieldToPivot(pivot, "Allowed"); addDataFieldToPivot(pivot, "PermissionID", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(REPORT_RBAC_PIVOT_USER_PERMISSIONS_GRAPH, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet.Column(6).Width = 20; } #endregion #region Group Memberships // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_GROUP_MEMBERSHIPS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_GROUP_MEMBERSHIPS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["GroupName"].Position + 1).Width = 20; sheet.Column(table.Columns["UserName"].Position + 1).Width = 20; } #endregion #region Role Memberships // Make table sheet = excelReport.Workbook.Worksheets[REPORT_RBAC_SHEET_ROLE_MEMBERSHIPS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > REPORT_RBAC_LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[REPORT_RBAC_LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, REPORT_RBAC_TABLE_ROLE_MEMBERSHIPS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 20; sheet.Column(table.Columns["RoleName"].Position + 1).Width = 30; sheet.Column(table.Columns["EntityName"].Position + 1).Width = 20; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file if (Directory.Exists(FilePathMap.ReportFolderPath()) == false) { Directory.CreateDirectory(FilePathMap.ReportFolderPath()); } string reportFilePath = FilePathMap.RBACExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(jobConfiguration) == false) { return(true); } try { loggerConsole.Info("Prepare Dashboards Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Dashboards Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Dashboards Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DASHBOARDS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DASHBOARDS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DASHBOARDS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DASHBOARDS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_WIDGETS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WIDGETS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_WIDGETS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WIDGETS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 4, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DATA_SERIES_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Type"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DATA_SERIES_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.Cells[3, 1].Value = "See Location"; sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DATA_SERIES_LOCATION_PIVOT); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DATA_SERIES_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DATA_SERIES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_DATA_SERIES_LOCATION_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_DATA_SERIES_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); #endregion loggerConsole.Info("Fill Dashboards Report File"); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications - All loggerConsole.Info("List of Applications - All"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Dashboards loggerConsole.Info("List of Dashboards"); sheet = excelReport.Workbook.Worksheets[SHEET_DASHBOARDS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.DashboardsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Widgets loggerConsole.Info("List of Widgets"); sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.DashboardWidgetsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Widget Data Series loggerConsole.Info("List of Widget Data Series"); sheet = excelReport.Workbook.Worksheets[SHEET_DATA_SERIES_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.DashboardMetricSeriesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Dashboards Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Applications - All // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["Description"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; } #endregion #region Dashboards // Make table sheet = excelReport.Workbook.Worksheets[SHEET_DASHBOARDS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_DASHBOARDS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumWidgets"].Position + 1, sheet.Dimension.Rows, table.Columns["NumWidgets"].Position + 1); var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars); sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["DashboardName"].Position + 1).Width = 20; sheet.Column(table.Columns["StartTime"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["StartTimeUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_DASHBOARDS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_DASHBOARDS_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "CreatedBy", eSortType.Ascending); addFilterFieldToPivot(pivot, "UpdatedBy", eSortType.Ascending); addFilterFieldToPivot(pivot, "IsShared"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "CanvasType"); addRowFieldToPivot(pivot, "DashboardName"); addDataFieldToPivot(pivot, "DashboardID", DataFieldFunctions.Count, "Dashboards"); addDataFieldToPivot(pivot, "NumWidgets", DataFieldFunctions.Sum, "Widgets"); addDataFieldToPivot(pivot, "NumAnalyticsWidgets", DataFieldFunctions.Sum, "Analytics"); addDataFieldToPivot(pivot, "NumEventListWidgets", DataFieldFunctions.Sum, "Events"); addDataFieldToPivot(pivot, "NumGaugeWidgets", DataFieldFunctions.Sum, "Gauges"); addDataFieldToPivot(pivot, "NumGraphWidgets", DataFieldFunctions.Sum, "Graphs"); addDataFieldToPivot(pivot, "NumIFrameWidgets", DataFieldFunctions.Sum, "IFrames"); addDataFieldToPivot(pivot, "NumImageWidgets", DataFieldFunctions.Sum, "Images"); addDataFieldToPivot(pivot, "NumMetricLabelWidgets", DataFieldFunctions.Sum, "Labels"); addDataFieldToPivot(pivot, "NumPieWidgets", DataFieldFunctions.Sum, "Pies"); addDataFieldToPivot(pivot, "NumTextWidgets", DataFieldFunctions.Sum, "Texts"); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_DASHBOARDS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; } #endregion #region Widgets // Make table sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_WIDGETS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["DashboardName"].Position + 1).Width = 20; sheet.Column(table.Columns["WidgetType"].Position + 1).Width = 15; sheet.Column(table.Columns["Title"].Position + 1).Width = 20; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_WIDGETS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_WIDGETS_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "EntityType", eSortType.Ascending); addFilterFieldToPivot(pivot, "EntitySelectionType"); addFilterFieldToPivot(pivot, "NumSelectedEntities", eSortType.Ascending); addFilterFieldToPivot(pivot, "NumDataSeries", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "CanvasType"); addRowFieldToPivot(pivot, "DashboardName"); addColumnFieldToPivot(pivot, "WidgetType"); addDataFieldToPivot(pivot, "DashboardName", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_WIDGETS_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; } #endregion #region Data Series // Make table sheet = excelReport.Workbook.Worksheets[SHEET_DATA_SERIES_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_DATA_SERIES); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["DashboardName"].Position + 1).Width = 20; sheet.Column(table.Columns["WidgetType"].Position + 1).Width = 15; sheet.Column(table.Columns["SeriesName"].Position + 1).Width = 20; sheet.Column(table.Columns["MetricType"].Position + 1).Width = 15; sheet.Column(table.Columns["MetricPath"].Position + 1).Width = 25; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_DATA_SERIES_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_DATA_SERIES_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "WidgetType"); addFilterFieldToPivot(pivot, "ApplicationName", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "CanvasType"); addRowFieldToPivot(pivot, "DashboardName"); addRowFieldToPivot(pivot, "MetricType"); addRowFieldToPivot(pivot, "MetricPath"); addColumnFieldToPivot(pivot, "SeriesType"); addDataFieldToPivot(pivot, "DashboardName", DataFieldFunctions.Count); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_DATA_SERIES_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet = excelReport.Workbook.Worksheets[SHEET_DATA_SERIES_LOCATION_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_DATA_SERIES_LOCATION); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "ApplicationName", eSortType.Ascending); addFilterFieldToPivot(pivot, "EntityType", eSortType.Ascending); addRowFieldToPivot(pivot, "WidgetType"); addRowFieldToPivot(pivot, "MetricType"); addRowFieldToPivot(pivot, "MetricPath"); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "CanvasType"); addRowFieldToPivot(pivot, "DashboardName"); addColumnFieldToPivot(pivot, "SeriesType"); addDataFieldToPivot(pivot, "DashboardName", DataFieldFunctions.Count); chart = sheet.Drawings.AddChart(GRAPH_DATA_SERIES_LOCATION, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet.Column(6).Width = 20; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file if (Directory.Exists(FilePathMap.ReportFolderPath()) == false) { Directory.CreateDirectory(FilePathMap.ReportFolderPath()); } string reportFilePath = FilePathMap.DashboardsExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(programOptions, jobConfiguration) == false) { return(true); } if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_APM) == 0) { logger.Warn("No {0} targets to process", APPLICATION_TYPE_APM); loggerConsole.Warn("No {0} targets to process", APPLICATION_TYPE_APM); return(true); } try { loggerConsole.Info("Prepare Detected APM Metrics Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics Detected APM Metrics Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics Detected APM Metrics Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_METRICS_SUMMARY_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_SUMMARY_TYPE_PIVOT); sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_METRICS_SUMMARY_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_SUMMARY_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); #endregion loggerConsole.Info("Fill Detected APM Metrics Report File"); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Applications - All loggerConsole.Info("List of Applications - All"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, typeof(ControllerApplication), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Metric Summary loggerConsole.Info("Metrics Summary"); sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_SUMMARY_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MetricPrefixSummaryReportFilePath(), 0, typeof(MetricSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Detected APM Metrics Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Applications - All // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["Description"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15; sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20; sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20; sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20; } #endregion #region Metrics Summary // Make table sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_SUMMARY_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_METRICS_SUMMARY_LIST); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["MetricPrefix"].Position + 1).Width = 30; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_SUMMARY_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_METRICS_SUMMARY_TYPE); setDefaultPivotTableSettings(pivot); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "MetricPrefix"); addDataFieldToPivot(pivot, "NumAll", DataFieldFunctions.Sum, "All"); addDataFieldToPivot(pivot, "NumActivity", DataFieldFunctions.Sum, "Activity"); addDataFieldToPivot(pivot, "NumNoActivity", DataFieldFunctions.Sum, "NoActivity"); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 30; ExcelChart chart = sheet.Drawings.AddChart(GRAPH_METRICS_SUMMARY_TYPE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath()); string reportFilePath = FilePathMap.MetricsListExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion #region Prepare individual application reports ParallelOptions parallelOptions = new ParallelOptions(); if (programOptions.ProcessSequentially == true) { parallelOptions.MaxDegreeOfParallelism = 1; } int j = 0; Parallel.ForEach( jobConfiguration.Target, parallelOptions, () => 0, (jobTarget, loop, subtotal) => { if (jobTarget.Type == APPLICATION_TYPE_APM) { createMetricListApplicationReport(programOptions, jobConfiguration, jobTarget); } return(1); }, (finalResult) => { Interlocked.Add(ref j, finalResult); if (j % 10 == 0) { Console.Write("[{0}].", j); } } ); #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
private bool createMetricListApplicationReport(ProgramOptions programOptions, JobConfiguration jobConfiguration, JobTarget jobTarget) { loggerConsole.Info("Prepare Detected APM Metrics Report File for {0}({1})", jobTarget.Application, jobTarget.ApplicationID); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics Detected APM Metrics Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics Detected APM Metrics Report"); int l = sheet.Dimension.Rows + 2; sheet.Cells[l, 1].Value = "Type"; sheet.Cells[l, 2].Value = "Application"; l++; sheet.Cells[l, 1].Value = "Name"; sheet.Cells[l, 2].Value = jobTarget.Application; #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_METRICS_SUMMARY_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_METRICS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Pivot"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_TYPE_PIVOT); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.Cells[3, 1].Value = "See BTs"; sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_BUSINESS_TRANSACTIONS_PIVOT); sheet.Cells[3, 2].StyleName = "HyperLinkStyle"; sheet.Cells[3, 3].Value = "See Tiers"; sheet.Cells[3, 4].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_TIER_PIVOT); sheet.Cells[3, 4].StyleName = "HyperLinkStyle"; sheet.Cells[3, 5].Value = "See Nodes"; sheet.Cells[3, 6].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_NODE_PIVOT); sheet.Cells[3, 6].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_METRICS_TYPE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + 9, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_METRICS_BUSINESS_TRANSACTIONS_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_METRICS_TIER_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_METRICS_NODE_PIVOT); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.Cells[2, 1].Value = "See Table"; sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METRICS_LIST); sheet.Cells[2, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1); #endregion loggerConsole.Info("Fill Detected APM Metrics Report File for {0}({1})", jobTarget.Application, jobTarget.ApplicationID); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Metrics Summary loggerConsole.Info("Metric Summary"); sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_SUMMARY_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MetricPrefixSummaryIndexFilePath(jobTarget), 0, typeof(MetricSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region List of Metrics loggerConsole.Info("List of Metrics"); sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.MetricsListApplicationReportFilePath(jobTarget), 0, typeof(Metric), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion loggerConsole.Info("Finalize Detected APM Metrics Report File for {0}({1})", jobTarget.Application, jobTarget.ApplicationID); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Metric Summary // Make table sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_SUMMARY_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_METRICS_SUMMARY_LIST); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["MetricPrefix"].Position + 1).Width = 30; } #endregion #region List of Metrics // Make table sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_METRICS_LIST); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 15; sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20; sheet.Column(table.Columns["MetricName"].Position + 1).Width = 20; for (int i = 1; i <= 22; i++) { sheet.Column(table.Columns[String.Format("Segment{0}", i)].Position + 1).Width = 20; } sheet.Column(table.Columns["MetricPath"].Position + 1).Width = 30; // Make pivot sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_TYPE_PIVOT]; ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + 7, 1], range, PIVOT_METRICS_LIST_TYPE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "NumSegments", eSortType.Ascending, true); addFilterFieldToPivot(pivot, "EntityName", eSortType.Ascending); addFilterFieldToPivot(pivot, "EntityType", eSortType.Ascending); addFilterFieldToPivot(pivot, "TierName", eSortType.Ascending); addFilterFieldToPivot(pivot, "TierAgentType", eSortType.Ascending); addFilterFieldToPivot(pivot, "BTName", eSortType.Ascending); addFilterFieldToPivot(pivot, "BTType", eSortType.Ascending); addFilterFieldToPivot(pivot, "BackendName", eSortType.Ascending); addFilterFieldToPivot(pivot, "BackendType", eSortType.Ascending); addRowFieldToPivot(pivot, "Controller"); addRowFieldToPivot(pivot, "ApplicationName"); addRowFieldToPivot(pivot, "Segment1"); addRowFieldToPivot(pivot, "Segment2"); addRowFieldToPivot(pivot, "Segment3"); addRowFieldToPivot(pivot, "Segment4"); addRowFieldToPivot(pivot, "Segment5"); addRowFieldToPivot(pivot, "Segment6"); addRowFieldToPivot(pivot, "Segment7"); addRowFieldToPivot(pivot, "Segment8"); addRowFieldToPivot(pivot, "Segment9"); addRowFieldToPivot(pivot, "Segment10"); addColumnFieldToPivot(pivot, "HasActivity"); addDataFieldToPivot(pivot, "MetricID", DataFieldFunctions.Count, "Count"); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet.Column(3).Width = 20; sheet.Column(4).Width = 20; sheet.Column(5).Width = 20; sheet.Column(6).Width = 20; sheet.Column(7).Width = 10; sheet.Column(8).Width = 10; sheet.Column(9).Width = 10; sheet.Column(10).Width = 10; sheet.Column(11).Width = 10; sheet.Column(12).Width = 10; sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_BUSINESS_TRANSACTIONS_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_METRICS_BUSINESS_TRANSACTIONS); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "NumSegments", eSortType.Ascending, true); addRowFieldToPivot(pivot, "BTType"); addRowFieldToPivot(pivot, "BTName"); addColumnFieldToPivot(pivot, "HasActivity"); addDataFieldToPivot(pivot, "MetricID", DataFieldFunctions.Count, "Count"); ExcelChart chart = sheet.Drawings.AddChart(GRAPH_METRICS_BUSINESS_TRANSACTIONS, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_TIER_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_METRICS_TIER); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "NumSegments", eSortType.Ascending, true); addRowFieldToPivot(pivot, "TierAgentType"); addRowFieldToPivot(pivot, "TierName"); addColumnFieldToPivot(pivot, "HasActivity"); addDataFieldToPivot(pivot, "MetricID", DataFieldFunctions.Count, "Count"); chart = sheet.Drawings.AddChart(GRAPH_METRICS_TIER, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; sheet = excelReport.Workbook.Worksheets[SHEET_METRICS_NODE_PIVOT]; pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_METRICS_NODE); setDefaultPivotTableSettings(pivot); addFilterFieldToPivot(pivot, "NumSegments", eSortType.Ascending, true); addRowFieldToPivot(pivot, "NodeAgentType"); addRowFieldToPivot(pivot, "NodeName"); addColumnFieldToPivot(pivot, "HasActivity"); addDataFieldToPivot(pivot, "MetricID", DataFieldFunctions.Count, "Count"); chart = sheet.Drawings.AddChart(GRAPH_METRICS_NODE, eChartType.ColumnClustered, pivot); chart.SetPosition(2, 0, 0, 0); chart.SetSize(800, 300); sheet.Column(1).Width = 20; sheet.Column(2).Width = 20; } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file string reportFilePath = FilePathMap.MetricsListApplicationExcelReportFilePath(jobTarget, true); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(programOptions, jobConfiguration) == false) { return(true); } if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_APM) == 0) { logger.Warn("No {0} targets to process", APPLICATION_TYPE_APM); loggerConsole.Warn("No {0} targets to process", APPLICATION_TYPE_APM); return(true); } try { loggerConsole.Info("Prepare Entity Flowmaps Report File"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Entity Flowmaps Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Entity Flowmaps Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivots // Entity sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT - 13 + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ACTIVITYFLOW); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT - 13 + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_TIERS_ACTIVITYFLOW); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT - 13 + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_NODES_ACTIVITYFLOW); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT - 13 + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_BACKENDS_ACTIVITYFLOW); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT - 13 + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_BUSINESS_TRANSACTIONS_ACTIVITYFLOW); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT - 13 + 1, 1); #endregion loggerConsole.Info("Fill Entity Flowmaps Report File"); #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion #region Controllers loggerConsole.Info("List of Controllers"); sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT - 13, 1); #endregion #region Applications loggerConsole.Info("Applications Flowmap"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ACTIVITYFLOW]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationsFlowmapReportFilePath(), 0, typeof(ActivityFlow), sheet, LIST_SHEET_START_TABLE_AT - 13, 1); #endregion #region Tiers loggerConsole.Info("Tiers Flowmap"); sheet = excelReport.Workbook.Worksheets[SHEET_TIERS_ACTIVITYFLOW]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.TiersFlowmapReportFilePath(), 0, typeof(ActivityFlow), sheet, LIST_SHEET_START_TABLE_AT - 13, 1); #endregion #region Nodes loggerConsole.Info("Nodes Flowmap"); sheet = excelReport.Workbook.Worksheets[SHEET_NODES_ACTIVITYFLOW]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.NodesFlowmapReportFilePath(), 0, typeof(ActivityFlow), sheet, LIST_SHEET_START_TABLE_AT - 13, 1); #endregion #region Backends loggerConsole.Info("Backends Flowmap"); sheet = excelReport.Workbook.Worksheets[SHEET_BACKENDS_ACTIVITYFLOW]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BackendsFlowmapReportFilePath(), 0, typeof(ActivityFlow), sheet, LIST_SHEET_START_TABLE_AT - 13, 1); #endregion #region Business Transactions loggerConsole.Info("Business Transactions Flowmap"); sheet = excelReport.Workbook.Worksheets[SHEET_BUSINESS_TRANSACTIONS_ACTIVITYFLOW]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BusinessTransactionsFlowmapReportFilePath(), 0, typeof(ActivityFlow), sheet, LIST_SHEET_START_TABLE_AT - 13, 1); #endregion loggerConsole.Info("Finalize Entity Flowmaps Report File"); #region Controllers sheet // Make table sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT - 13) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT - 13, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_CONTROLLERS); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; sheet.Column(table.Columns["Controller"].Position + 1).Width = 25; sheet.Column(table.Columns["Version"].Position + 1).Width = 15; } #endregion #region Applications // Make table sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ACTIVITYFLOW]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT - 13) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT - 13, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ACTIVITYFLOW); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; adjustColumnsOfActivityFlowRowTableInMetricReport(APMApplication.ENTITY_TYPE, sheet, table); } #endregion #region Tiers // Make table sheet = excelReport.Workbook.Worksheets[SHEET_TIERS_ACTIVITYFLOW]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT - 13) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT - 13, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_TIERS_ACTIVITYFLOW); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; adjustColumnsOfActivityFlowRowTableInMetricReport(APMTier.ENTITY_TYPE, sheet, table); } #endregion #region Nodes // Make table sheet = excelReport.Workbook.Worksheets[SHEET_NODES_ACTIVITYFLOW]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT - 13) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT - 13, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_NODES_ACTIVITYFLOW); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; adjustColumnsOfActivityFlowRowTableInMetricReport(APMNode.ENTITY_TYPE, sheet, table); } #endregion #region Backends // Make table sheet = excelReport.Workbook.Worksheets[SHEET_BACKENDS_ACTIVITYFLOW]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT - 13) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT - 13, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_BACKENDS_ACTIVITYFLOW); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; adjustColumnsOfActivityFlowRowTableInMetricReport(APMBackend.ENTITY_TYPE, sheet, table); } #endregion #region Business Transactions // Make table sheet = excelReport.Workbook.Worksheets[SHEET_BUSINESS_TRANSACTIONS_ACTIVITYFLOW]; logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows); if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT - 13) { range = sheet.Cells[LIST_SHEET_START_TABLE_AT - 13, 1, sheet.Dimension.Rows, sheet.Dimension.Columns]; table = sheet.Tables.Add(range, TABLE_BUSINESS_TRANSACTIONS_ACTIVITYFLOW); table.ShowHeader = true; table.TableStyle = TableStyles.Medium2; table.ShowFilter = true; table.ShowTotal = false; adjustColumnsOfActivityFlowRowTableInMetricReport(APMBusinessTransaction.ENTITY_TYPE, sheet, table); } #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath()); string reportFilePath = FilePathMap.FlowmapsExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration) { Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StepTiming stepTimingFunction = new StepTiming(); stepTimingFunction.JobFileName = programOptions.OutputJobFilePath; stepTimingFunction.StepName = jobConfiguration.Status.ToString(); stepTimingFunction.StepID = (int)jobConfiguration.Status; stepTimingFunction.StartTime = DateTime.Now; stepTimingFunction.NumEntities = jobConfiguration.Target.Count; this.DisplayJobStepStartingStatus(jobConfiguration); FilePathMap = new FilePathMap(programOptions, jobConfiguration); if (this.ShouldExecute(programOptions, jobConfiguration) == false) { return(true); } try { loggerConsole.Info("Prepare BSG Report File"); loggerConsole.Info("Executing BSG"); #region Prepare the report package // Prepare package ExcelPackage excelReport = new ExcelPackage(); excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version); excelReport.Workbook.Properties.Title = "AppDynamics DEXTER APM BSG Report"; excelReport.Workbook.Properties.Subject = programOptions.JobName; excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To); #endregion #region Parameters sheet // Parameters sheet ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS); var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle"); hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single; hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks); var timelineStyle = sheet.Workbook.Styles.CreateNamedStyle("TimelineStyle"); timelineStyle.Style.Font.Name = "Consolas"; timelineStyle.Style.Font.Size = 8; fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER APM BSG Report"); #endregion #region TOC sheet // Navigation sheet with link to other sheets sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC); #endregion #region Entity sheets and their associated pivot sheet = excelReport.Workbook.Worksheets.Add(SHEET_AGENTS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_BT_Info_List); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_BACKENDS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_BACKEND_CUSTOMIZATION_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_SERVICE_ENDPOINT_DETECTION_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_HEALTH_RULES_DETECTION_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_OVERHEAD_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_DATACOLLECTOR_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_DASHBOARD_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_SYNTHETICS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_BRUM_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_DATABASE_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_BIQ_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_SIM_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_MRUM_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_CONTAINERS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATION_ERRORS_LIST); sheet.Cells[1, 1].Value = "Table of Contents"; sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC); sheet.Cells[1, 2].StyleName = "HyperLinkStyle"; sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1); #endregion #region Report file variables ExcelRangeBase range = null; ExcelTable table = null; #endregion loggerConsole.Info("Fill BSG Report File"); #region Agents loggerConsole.Info("List of Agents"); sheet = excelReport.Workbook.Worksheets[SHEET_AGENTS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGAgentResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region BT loggerConsole.Info("Business Transactions Info"); sheet = excelReport.Workbook.Worksheets[SHEET_BT_Info_List]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGBusinessTransactionExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Backends loggerConsole.Info("List of Backends"); sheet = excelReport.Workbook.Worksheets[SHEET_BACKENDS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGBackendResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Backend Customization loggerConsole.Info("List of Backend Customizations"); sheet = excelReport.Workbook.Worksheets[SHEET_BACKEND_CUSTOMIZATION_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGBackendCustomizationResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region SEPs loggerConsole.Info("List of SEP Discovery and Customization"); sheet = excelReport.Workbook.Worksheets[SHEET_SERVICE_ENDPOINT_DETECTION_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGSepResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Health Rules loggerConsole.Info("List of Health Rule Customizations"); sheet = excelReport.Workbook.Worksheets[SHEET_HEALTH_RULES_DETECTION_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGHealthRuleResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Overhead loggerConsole.Info("List of Overhead configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_OVERHEAD_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGOverheadResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region DataCollectors loggerConsole.Info("List of DataCollectors configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_DATACOLLECTOR_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGDataCollectorResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Dashboards loggerConsole.Info("List of Dashboards configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_DASHBOARD_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGDashboardResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Synthetics loggerConsole.Info("List of Synthetics configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_SYNTHETICS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGSyntheticsResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region BRUM loggerConsole.Info("List of BRUM configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_BRUM_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGBrumResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region Database loggerConsole.Info("List of Database configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_DATABASE_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGDatabaseResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region BIQ loggerConsole.Info("List of Database configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_BIQ_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGBIQResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region SIM loggerConsole.Info("List of SIM configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_SIM_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGSimResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region MRUM loggerConsole.Info("List of MRUM configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_MRUM_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGMrumResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region CONTAINERS loggerConsole.Info("List of Container configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_CONTAINERS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGContainersResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region ERRORS loggerConsole.Info("List of Error configurations"); sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATION_ERRORS_LIST]; EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.BSGErrorsResultsExcelReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1); #endregion #region TOC sheet // TOC sheet again sheet = excelReport.Workbook.Worksheets[SHEET_TOC]; fillTableOfContentsSheet(sheet, excelReport); #endregion #region Save file FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath()); string reportFilePath = FilePathMap.BSGResultsExcelReportFilePath(jobConfiguration.Input.TimeRange); logger.Info("Saving Excel report {0}", reportFilePath); loggerConsole.Info("Saving Excel report {0}", reportFilePath); try { // Save full report Excel files excelReport.SaveAs(new FileInfo(reportFilePath)); } catch (InvalidOperationException ex) { logger.Warn("Unable to save Excel file {0}", reportFilePath); logger.Warn(ex); loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath); } #endregion return(true); } catch (Exception ex) { logger.Error(ex); loggerConsole.Error(ex); return(false); } finally { stopWatch.Stop(); this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch); stepTimingFunction.EndTime = DateTime.Now; stepTimingFunction.Duration = stopWatch.Elapsed; stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds; List <StepTiming> stepTimings = new List <StepTiming>(1); stepTimings.Add(stepTimingFunction); FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true); } }