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