//Check Server Connection public static String checkServerConnection(string d, string p, ManagementScope scope) { //Number of tries if connection fails int retry = 5; tryconnection: string[] connList = new string[3]; string text = ""; String[,] data = new String[10, 10]; //Connection Object ConnectionOptions connection = new ConnectionOptions(); try { //Query Object to get whatever data is required ObjectQuery query = new ObjectQuery("SELECT * FROM Win32_PerfFormattedData_PerfDisk_PhysicalDisk"); //Query the server ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query); //For each in Query Object foreach (ManagementObject queryObj in searcher.Get()) { String value = String.Empty; //Property of Query Object value = queryObj["AvgDiskQueueLength"].ToString(); Double threshold = Convert.ToDouble(queryObj["AvgDiskQueueLength"].ToString()); } } catch (Exception e) { //Try till retry ! = 0(Try 5 times) if (retry != 0) { Thread.Sleep(5000); retry = retry - 1; goto tryconnection; } else { try { //Send mail String subject = "UNABLE TO REACH SERVER"; String failreport = "Criticality : High <br >" + "Server IP Address: " + @d + "<br >" + e.Message; ReportMailer mailer = new ReportMailer(0, 0, failreport, subject); mailer.SendMail(); } catch (Exception er) { Console.WriteLine(er.Message); } } } return(text); }
//Average Disk Sec per Write public static String checkAvgDiskSecWrite(string d, string p, ManagementScope scope) { string[] connList = new string[3]; string text = ""; String[,] data = new String[10, 10]; text = Environment.NewLine; ObjectQuery query = new ObjectQuery ("SELECT * FROM Win32_PerfFormattedData_PerfDisk_PhysicalDisk where Name = '_Total'"); ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query); foreach (ManagementObject queryObj in searcher.Get()) { String value = String.Empty; value = queryObj["AvgDisksecPerWrite"].ToString(); Double diskwrite = Convert.ToDouble(value); text = text + DateTime.Now.ToString() + ";"; text = text + value + ";"; //Check Threshold and send mail if (diskwrite > 20) { try { String subject = "HIGH AVERAGE DISK WRITE"; String emailtext = "Criticality : High <br>" + "Server IP Address : " + @d + "<br >" + "Value: " + diskwrite + "<br>"; ReportMailer mailer = new ReportMailer(0, 0, emailtext, subject); mailer.SendMail(); } catch (Exception e) { Console.WriteLine(e.Message); } } } //Write To File String newContent = String.Empty; String content = String.Empty; String file = @p + "wmiAverageDiskSecWrite.txt"; if (File.Exists(file)) { content = File.ReadAllText(file); newContent = content + text; File.WriteAllText(file, newContent); } return(text); }
static void Main(string[] args) { //The name of the tool. Console.WriteLine("The Appmon Tool"); int count = 0; //counting SV key value pairs of 'SV' from configuration file foreach (var item in ConfigurationManager.AppSettings.AllKeys) { if (item.StartsWith("SV")) { count = count + 1; } } //array to store connection list string[] connList = new string[count]; int arrcount = 0; //loop to get value of keys 'SV' from configuration file and store them in connList foreach (var item in ConfigurationManager.AppSettings.AllKeys) { if (item.StartsWith("SV")) { string query = ConfigurationManager.AppSettings[item]; Console.WriteLine("Adding server list"); Console.WriteLine(query); connList[arrcount] = query; arrcount = arrcount + 1; } } foreach (var connect in connList) { Console.WriteLine(connect + Environment.NewLine); //split the connect(value part of key-value pair) string string[] sv = connect.Split(','); //sv[1] = IP address, sv[2]= database on the IP address string connectionstring = "Data Source=" + sv[1] + ";Initial Catalog=" + sv[2] + ";Integrated Security=sspi;"; Console.WriteLine(connectionstring); //sv[3] = path of the log file String path = sv[3]; Console.WriteLine(path); int retry = 10; trytotalmemory: SqlConnection connsql = new SqlConnection(@connectionstring); try { Console.WriteLine("Trying to open"); connsql.Open(); } catch (Exception error) { Console.WriteLine(error.Message); if (retry != 0) { //Console.WriteLine("Trying " + retry); retry = retry - 1; Thread.Sleep(10); goto trytotalmemory; } else { try { ////Console.WriteLine("Sending E-Mail at this time : {0}", DateTime.Now); String subject = "UNABLE TO CONNECT TO SERVER"; String emailtext = "CRITICALITY : HIGH:" + "<br />" + (@connectionstring.Split(';')[0]).Split('=')[1] + "<br />" + "Error: " + error.Message; ReportMailer mailer = new ReportMailer(0, 0, emailtext, subject); mailer.SendMail(); } catch (Exception error2) { Console.WriteLine(error2.Message); } } } //calling each method checkParam.checkTargetMemory(@connectionstring, @path, @connect, connsql); checkParam.checkTotalMemory(@connectionstring, @path, @connect, connsql); checkParam.checkFullScans(@connectionstring, @path, @connect, connsql); checkParam.checkAveragelatchWaitTime(@connectionstring, @path, @connect, connsql); checkParam.checkAverageLockWaitTime(@connectionstring, @path, @connect, connsql); checkParam.checkActiveTransactionsPerSecond(@connectionstring, @path, @connect, connsql); checkParam.checkNumberOfActiveTransactions(@connectionstring, @path, @connect, connsql); checkParam.checkNumberOfActiveConnections(@connectionstring, @path, @connect, connsql); checkParam.checklongRunningQueries(@connectionstring, @path, @connect, connsql); checkParam.checkPhysicalMemory(@connectionstring, @path, @connect, connsql); checkParam.checkBlockingQueries(@connectionstring, @path, @connect, connsql); // checkParam.checkAverageDiskRead(@connectionstring, @path, @connect, connsql); // checkParam.checkAverageDiskWrite(@connectionstring, @path, @connect, connsql); checkParam.logPhysicalMemory(@connectionstring, @path, @connect, connsql); checkParam.checkSeverity(@connectionstring, @path, @connect, connsql); // checkParam.checkerror825(@connectionstring, @path, @connect, connsql); checkParam.checkDeadLocks(@connectionstring, @path, @connect, connsql); checkParam.checkDiskLatencyCritical(@connectionstring, @path, @connect, connsql); checkParam.checkUnusualJobDuration(@connectionstring, @path, @connect, connsql); checkParam.buffermanager(@connectionstring, @path, @connect, connsql); checkParam.bufferpoolUsedPerDatabase(@connectionstring, @path, @connect, connsql); checkParam.bufferpoolUsedPerObject(@connectionstring, @path, @connect, connsql); checkParam.checkPendingMemoryGrants(@connectionstring, @path, @connect, connsql); checkParam.checkBatchRequestsPerSecond(@connectionstring, @path, @connect, connsql); Console.WriteLine("Done with " + connectionstring + Environment.NewLine); connsql.Dispose(); } Console.WriteLine("END"); }
public static String checkSqlserverMemory(string d, string p, ManagementScope scope) { string[] connList = new string[3]; string text = ""; String[,] data = new String[10, 10]; //Query ObjectQuery query2 = new ObjectQuery ("SELECT * FROM Win32_ComputerSystem"); ManagementObjectSearcher searcher2 = new ManagementObjectSearcher(scope, query2); String totalMemory = String.Empty; double tm = 0.0; foreach (ManagementObject queryObj in searcher2.Get()) { totalMemory = queryObj["TotalPhysicalMemory"].ToString(); tm = Convert.ToDouble(Convert.ToDouble(totalMemory) / (1024.00 * 1024.00)); } //Query2 ObjectQuery query = new ObjectQuery ("SELECT * FROM Win32_PerfRawData_PerfProc_Process where Name = 'sqlservr'"); ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query); foreach (ManagementObject queryObj in searcher.Get()) { String value = String.Empty; value = queryObj["WorkingSetPrivate"].ToString(); Double threshold = Convert.ToDouble((Convert.ToDouble(queryObj["WorkingSetPrivate"].ToString())) / (1024.00 * 1024.00)); text = Environment.NewLine + text + DateTime.Now.ToString() + ";"; text = text + value + ";"; //Write To File String newContent = String.Empty; String content = String.Empty; String file = @p + "wmiSqlserverMemory.txt"; if (File.Exists(file)) { content = File.ReadAllText(file); newContent = content + text; File.WriteAllText(file, newContent); } //Check Threshold and send mail if (((threshold / tm) * 100) >= 95) { try { String subject = "HIGH MEMORY UTILIZATION BY SQL SERVER"; String emailtext = "Criticality : High <br>" + "Server IP Address: " + @d + "<br >" + "Value: " + threshold + "<br>" + "Percentage : " + ((threshold / tm) * 100) + "<br>"; ReportMailer mailer = new ReportMailer(0, 0, emailtext, subject); mailer.SendMail(); } catch (Exception e) { Console.WriteLine(e.Message); } } } return(text); }
//CpuUtilizationBySQLServer public static String checkSqlserverCpuUtilization(string d, string p, ManagementScope scope) { int retry = 3; tryconnection: string[] connList = new string[3]; string text = ""; String[,] data = new String[10, 10]; //Query ObjectQuery query = new ObjectQuery ("SELECT * FROM Win32_PerfFormattedData_PerfProc_Process where Name = 'sqlservr'"); ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query); foreach (ManagementObject queryObj in searcher.Get()) { String value = String.Empty; value = queryObj["PercentProcessorTime"].ToString(); Double threshold = Convert.ToDouble(queryObj["PercentProcessorTime"].ToString()); text = Environment.NewLine + DateTime.Now.ToString() + ";"; text = text + value + ";"; //Check Threshold and send mail if (threshold > 95) { if (retry != 0) { Thread.Sleep(5000); retry = retry - 1; goto tryconnection; } else { try { String subject = "High Cpu Utilization by Sql Server"; String emailtext = "Criticality : Low <br>" + "Server IP Address: " + @d + "<br >" + "Value: " + threshold + "<br> "; ReportMailer mailer = new ReportMailer(0, 0, emailtext, subject); mailer.SendMail(); } catch (Exception e) { Console.WriteLine(e.Message); } } } } //Write To File String newContent = String.Empty; String content = String.Empty; String file = @p + "wmiSqlserverCpuUtilization.txt"; if (File.Exists(file)) { content = File.ReadAllText(file); newContent = content + text; File.WriteAllText(file, newContent); } return(text); }
public static String checkPhysicalMemory(string d, string p, ManagementScope scope) { string[] connList = new string[3]; string text = ""; String[,] data = new String[10, 10]; ObjectQuery query2 = new ObjectQuery ("SELECT * FROM Win32_ComputerSystem"); ManagementObjectSearcher searcher2 = new ManagementObjectSearcher(scope, query2); String totalMemory = String.Empty; double tm = 0.0; foreach (ManagementObject queryObj in searcher2.Get()) { totalMemory = queryObj["TotalPhysicalMemory"].ToString(); tm = Convert.ToDouble(Convert.ToDouble(totalMemory) / (1024.00 * 1024.00)); } double fm = 0.0; ObjectQuery query = new ObjectQuery ("SELECT * FROM Win32_PerfFormattedData_PerfOS_Memory"); ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query); foreach (ManagementObject queryObj in searcher.Get()) { String value = String.Empty; value = queryObj["AvailableMBytes"].ToString(); fm = Convert.ToDouble(value); text = text + DateTime.Now.ToString() + ";"; double usedMem = tm - fm; double freeMemperc = (fm / tm) * 100; text = text + fm + ";" + tm + ";" + usedMem + ";" + freeMemperc + ";"; String newContent = String.Empty; String content = String.Empty; String file = @p + "wmiLogPhysicalMemory.txt"; if (File.Exists(file)) { content = File.ReadAllText(file); newContent = content + text + Environment.NewLine; File.WriteAllText(file, newContent); } if ((fm / tm * 100) < 5) { try { String subject = "LOW PHYSICAL MEMORY"; String[] datatext = text.Split(';'); String emailtext = "Criticality : High <br>" + "IP Address: " + @d + "<br >" + "Time: " + datatext[0] + "<br>" + "FreeMemory in MB: " + datatext[1] + "<br>" + "Total Memory in MB: " + datatext[2] + "<br>" + "Used Memory in MB: " + datatext[3] + "<br>" + "Free Memory Percentage: " + datatext[4] + "<br>"; ReportMailer mailer = new ReportMailer(0, 0, emailtext, subject); mailer.SendMail(); } catch (Exception e) { Console.WriteLine(e.Message); } } break; } return(text); }