public OsDiagForm(OSDiagToolConf.ConfModel.strConfModel configurations, string dbms, DBConnector.SQLConnStringModel SQLConnectionString = null, DBConnector.OracleConnStringModel OracleConnectionString = null) { InitializeComponent(); this.cb_iisThreads.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_threadDumps][OSDiagToolConfReader._l3_iisW3wp]; // Iis thread dumps this.cb_osServicesThreads.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_threadDumps][OSDiagToolConfReader._l3_osServices]; // OS services thread dumps this.cb_iisMemDumps.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_memoryDumps][OSDiagToolConfReader._l3_iisW3wp]; this.cb_osMemDumps.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_memoryDumps][OSDiagToolConfReader._l3_osServices]; this.cb_EvtViewerLogs.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_serverLogs][OSDiagToolConfReader._l3_evtAndServer]; this.cb_iisAccessLogs.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_serverLogs][OSDiagToolConfReader._l3_iisLogs]; this.nud_iisLogsNrDays.Value = configurations.IISLogsNrDays; // Number of days of IIS logs this.cb_platformLogs.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_platform][OSDiagToolConfReader._l3_platformLogs]; this.nud_topLogs.Value = configurations.osLogTopRecords; this.cb_platformAndServerFiles.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_platform][OSDiagToolConfReader._l3_platformAndServerConfigFiles]; this.cb_dbPlatformMetamodel.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_databaseOperations][OSDiagToolConfReader._l3_platformMetamodel]; this.cb_dbTroubleshoot.Checked = configurations.osDiagToolConfigurations[OSDiagToolConfReader._l2_databaseOperations][OSDiagToolConfReader._l3_databaseTroubleshoot]; this.lb_metamodelTables.Items.AddRange(configurations.tableNames.ToArray()); // add Platform Metamodel tables to list box bt_TestSaConnection.Click += delegate(object sender, EventArgs e) { bt_TestSaConnection_Click(sender, e, dbms, SQLConnectionString, OracleConnectionString); }; bt_runOsDiagTool.Click += delegate(object sender, EventArgs e) { bt_runOsDiagTool_Click(sender, e, configurations); }; }
private void bt_runOsDiagTool_Click(object sender, EventArgs e, OSDiagToolConf.ConfModel.strConfModel configurations) { var formConfigurations = new OSDiagToolForm.OsDiagFormConfModel.strFormConfigurationsModel(); List <string> tableNameHelper = new List <string>(); if (tb_iptSaUsername.Text.Equals("") || tb_iptSaPwd.Text.Equals("")) // if no input is provided in user or pwd, then DBTroubleshoot doesn't run { cb_dbTroubleshoot.Checked = false; } formConfigurations.saUser = tb_iptSaUsername.Text.ToString(); formConfigurations.saPwd = tb_iptSaPwd.Text.ToString(); formConfigurations.iisLogsNrDays = Convert.ToInt32(nud_iisLogsNrDays.Value); formConfigurations.osLogTopRecords = Convert.ToInt32(nud_topLogs.Value); foreach (object item in lb_metamodelTables.Items) { tableNameHelper.Add(item.ToString()); } formConfigurations.metamodelTables = tableNameHelper; Dictionary <string, bool> dictHelper = new Dictionary <string, bool>() { { _tdIis, cb_iisThreads.Checked }, { _tdOsServices, cb_osServicesThreads.Checked }, { _mdIis, cb_iisMemDumps.Checked }, { _mdOsServices, cb_osMemDumps.Checked }, { _slEvt, cb_EvtViewerLogs.Checked }, { _slIisLogs, cb_iisAccessLogs.Checked }, { _diMetamodel, cb_dbPlatformMetamodel.Checked }, { _diDbTroubleshoot, cb_dbTroubleshoot.Checked }, { _plPlatformLogs, cb_platformLogs.Checked }, { _plPlatformAndServerFiles, cb_platformAndServerFiles.Checked }, }; formConfigurations.cbConfs = dictHelper; var configurationsHelper = new DataHelperClass.strConfigurations(); configurationsHelper.FormConfigurations = new OSDiagToolForm.OsDiagFormConfModel.strFormConfigurationsModel(); configurationsHelper.FormConfigurations = formConfigurations; int numberOfSteps = OSDiagToolHelper.CountSteps(configurationsHelper.FormConfigurations.cbConfs); puf_popUpForm popup = new puf_popUpForm(puf_popUpForm._feedbackWaitType, OsDiagForm._waitMessage, totalSteps: numberOfSteps + 2); // totalSteps + 2 for the zipping and pop up close popup.Show(); popup.Refresh(); configurationsHelper.popup = popup; configurationsHelper.ConfigFileConfigurations = new OSDiagToolConf.ConfModel.strConfModel(); configurationsHelper.ConfigFileConfigurations = configurations; backgroundWorker1.RunWorkerAsync(configurationsHelper); Cursor = Cursors.Arrow; }
public OSDiagToolConf.ConfModel.strConfModel GetOsDiagToolConfigurations() { XDocument xml = XDocument.Load(_osDiagToolConfigPath); var configuration = new OSDiagToolConf.ConfModel.strConfModel(); configuration.queryTimeout = GetQueryTimeout(xml); configuration.IISLogsNrDays = GetIISLogsNrDays(xml); configuration.tableNames = GetTableNames(xml); configuration.osLogTopRecords = GetOsLogTopRecords(xml); configuration.databaseQueryConfigurations = GetDatabaseQueryConfigurations(xml); configuration.osDiagToolConfigurations = GetOsDiagToolConfigurations(xml); return(configuration); }
public static void DatabaseTroubleshooting(string dbEngine, OSDiagToolConf.ConfModel.strConfModel configurations, string outputDestination, DBConnector.SQLConnStringModel SQLConnectionString = null, DBConnector.OracleConnStringModel OracleConnectionString = null) { // Needs user with sa permissions if (dbEngine.ToLower().Equals("sqlserver")) { List <string> blockingAndBlockedSpids = new List <string>(); int top_statCachedPlan = Convert.ToInt32(configurations.databaseQueryConfigurations[OSDiagToolConfReader._l3_sqlServer][OSDiagToolConfReader._l4_top_statCachedPlans]); int top_topCPU = Convert.ToInt32(configurations.databaseQueryConfigurations[OSDiagToolConfReader._l3_sqlServer][OSDiagToolConfReader._l4_top_topCPU]); var sqlDBQueries = new SQLServerQueries(); Dictionary <string, string> sqlQueries = new Dictionary <string, string> { // use same name as in the DatabaseQueries { "sessionsSp_Who2", sqlDBQueries.sessionsSp_Who2 }, { "sessionsSp_Who2_Blocked", sqlDBQueries.sessionsSp_Who2_Blocked }, { "statCachedPlan", string.Format(sqlDBQueries.statCachedPlans, top_statCachedPlan) }, { "costlyCPUQueries", string.Format(sqlDBQueries.costlyCPUQueries, top_topCPU) }, { "dbccInputBuffer", sqlDBQueries.dbccInputBuffer } }; var connector = new DBConnector.SLQDBConnector(); SqlConnection connection = connector.SQLOpenConnection(SQLConnectionString); using (connection) { foreach (KeyValuePair <string, string> entry in sqlQueries) { if (!(entry.Key.Equals("sessionsSp_Who2_Blocked") || entry.Key.Equals("dbccInputBuffer"))) // skip sp_who2_blocked and dbcc since it already exports the entire result set of sp_who2 { CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, entry.Value, connection, null); } else if (entry.Key.Equals("sessionsSp_Who2_Blocked")) { SqlCommand cmd = new SqlCommand(entry.Value, connection) { CommandTimeout = configurations.queryTimeout }; SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { blockingAndBlockedSpids.Add(dr.GetValue(0).ToString()); // add blockings spids to list blockingAndBlockedSpids.Add(dr.GetValue(1).ToString()); // add blocked spids to list } } } else if (entry.Key.Equals("dbccInputBuffer")) { if (!(blockingAndBlockedSpids.Count.Equals(0))) // get sql text of blocked and blockig spids { string allBlockedSpidsInline = string.Join(",", blockingAndBlockedSpids.ToArray()); string blockedSqlTextQuery = string.Format(entry.Value, allBlockedSpidsInline); CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, blockedSqlTextQuery, connection, null); } } } ; } } else if (dbEngine.ToLower().Equals("oracle")) { List <string> orclSids = new List <string>(); int orcl_TopCPU = Convert.ToInt32(configurations.databaseQueryConfigurations[OSDiagToolConfReader._l3_oracle][OSDiagToolConfReader._l4_top_topCPU]); var orclDBQueries = new OracleQueries(); Dictionary <string, string> orclQueries = new Dictionary <string, string> { // TODO: use reflection to get the property names { "orcl_lockedObjects", orclDBQueries.lockedObjects }, { "orcl_lockedObjects_2", orclDBQueries.lockedObjects_2 }, { "orcl_resourceLimit", orclDBQueries.resourceLimit }, { "orcl_sessionByIOType_Reads", string.Format(orclDBQueries.sessionByIOType, "DISK_READS") }, { "orcl_sessionByIOType_Writes", string.Format(orclDBQueries.sessionByIOType, "DIRECT_WRITES") }, { "orcl_tk_queriesRunningNow", orclDBQueries.tk_queriesRunningNow }, { "orcl_sqlTextBySID", orclDBQueries.sqlTextBySID }, { "orcl_sidInfo", orclDBQueries.sidInfo }, { "orcl_topCPUSqls", string.Format(orclDBQueries.topCPUSqls, orcl_TopCPU) } }; var connector = new DBConnector.OracleDBConnector(); OracleConnection connection = connector.OracleOpenConnection(OracleConnectionString); using (connection) { foreach (KeyValuePair <string, string> entry in orclQueries) { if (!(entry.Key.Equals("orcl_lockedObjects") || (entry.Key.Equals("orcl_sessionByIOType_Writes") || (entry.Key.Equals("orcl_sessionByIOType_Reads") || (entry.Key.Equals("orcl_lockedObjects_2") || (entry.Key.Equals("orcl_sqlTextBySID") || (entry.Key.Equals("orcl_sidInfo")))))))) // skip queries that we want to know more about the sessions { CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, entry.Value, null, connection); } else if (entry.Key.Equals("orcl_lockedObjects") || entry.Key.Equals("orcl_lockedObjects_2") || entry.Key.Equals("orcl_sessionByIOType_Reads") || entry.Key.Equals("orcl_sessionByIOType_Writes")) { OracleCommand cmd = new OracleCommand(entry.Value, connection) { CommandTimeout = configurations.queryTimeout }; OracleDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { orclSids.Add(dr.GetValue(0).ToString()); // add Sids to list } } CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, entry.Value, null, connection); } else if (entry.Key.Equals("orcl_sidInfo") || entry.Key.Equals("orcl_sqlTextBySID")) { if (!(orclSids.Count.Equals(0))) { string allSidsInline = string.Join(",", orclSids.ToArray()); string sidsSqlTextQuery = string.Format(entry.Value, allSidsInline); CSVExporter.SQLToCSVExport(dbEngine, entry.Key, outputDestination, configurations.queryTimeout, sidsSqlTextQuery, null, connection); } } } } } }