public override void RecordMessage(AlertRaised alertRaised)
{
string lastStep = "";
try
{
SQLDatabaseNotifierConfig currentConfig = (SQLDatabaseNotifierConfig)AgentConfig;
lastStep = "Getting or opening connection";
lock (AgentConfig)
{
if (cacheConn == null || cacheConn.State != ConnectionState.Open)
{
cacheConn = null;
cacheConn = new SqlConnection(currentConfig.GetConnectionString());
for (int i = 0; i < 3; i++)
{
try
{
cacheConn.Open();
break;
}
catch (System.Data.SqlClient.SqlException ex)
{
if (ex.Message.Contains("The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement"))
{
System.Threading.Thread.Sleep(3000);
//try again
}
else
{
throw;
}
}
}
}
}
//using (SqlConnection conn = new SqlConnection(currentConfig.GetConnectionString()))
{
// conn.Open();
lastStep = string.Format("Inserting test message into database {0}\\{1}", currentConfig.SqlServer, currentConfig.Database);
string alertParamName = currentConfig.AlertFieldName.Replace("'", "''").Replace("@", "");
string collectorTypeParamName = currentConfig.CollectorTypeFieldName.Replace("'", "''").Replace("@", "");
string categoryParamName = currentConfig.CategoryFieldName.Replace("'", "''").Replace("@", "");
string previousStateParamName = currentConfig.PreviousStateFieldName.Replace("'", "''").Replace("@", "");
string currentStateParamName = currentConfig.CurrentStateFieldName.Replace("'", "''").Replace("@", "");
string detailsParamName = currentConfig.DetailsFieldName.Replace("'", "''").Replace("@", "");
string sql = currentConfig.UseSP ? currentConfig.CmndValue :
string.Format("Insert {0} ({1}, {2}, {3}, {4}, {5}, {6}) values(@{1}, @{2}, @{3}, @{4}, @{5}, @{6})",
currentConfig.CmndValue,
currentConfig.AlertFieldName,
collectorTypeParamName,
categoryParamName,
previousStateParamName,
currentStateParamName,
detailsParamName);
string collectorName = "QuickMon Global Alert";
string collectorType = "None";
int oldState = 0;
int newState = 0;
string detailMessage = "N/A";
if (alertRaised.RaisedFor != null)
{
collectorName = alertRaised.RaisedFor.Name;
collectorType = alertRaised.RaisedFor.CollectorRegistrationName;
oldState = (byte)alertRaised.RaisedFor.LastMonitorState.State;
newState = (byte)alertRaised.RaisedFor.CurrentState.State;
detailMessage = FormatUtils.N(alertRaised.RaisedFor.CurrentState.RawDetails, "No details available");
}
using (SqlCommand cmnd = new SqlCommand(sql, cacheConn))
{
SqlParameter[] paramArr = new SqlParameter[]
{
new SqlParameter("@" + alertParamName, (byte)alertRaised.Level),
new SqlParameter("@" + collectorTypeParamName, collectorType),
new SqlParameter("@" + categoryParamName, collectorName),
new SqlParameter("@" + previousStateParamName, oldState),
new SqlParameter("@" + currentStateParamName, newState),
new SqlParameter("@" + detailsParamName, detailMessage)
};
cmnd.Parameters.AddRange(paramArr);
if (currentConfig.UseSP)
{
cmnd.CommandType = CommandType.StoredProcedure;
}
else
{
cmnd.CommandType = CommandType.Text;
}
cmnd.CommandTimeout = currentConfig.CmndTimeOut;
cmnd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new Exception("Error recording message in Database notifier\r\nLast step: " + lastStep, ex);
}
}
public override void RefreshDisplayData()
{
if (Notifier != null && Notifier.AgentConfig != null)
{
SQLDatabaseNotifierConfig dbConfig = (SQLDatabaseNotifierConfig)Notifier.AgentConfig;
int infos = 0;
int warns = 0;
int errs = 0;
int debugs = 0;
try
{
lvwMessages.BeginUpdate();
if (chkStayCurrent.Checked)
{
dateTimeChooserTo.SelectedDateTime = DateTime.Now.AddMinutes(1);
}
int topCount = int.MaxValue;
if (cboTopCount.SelectedItem.ToString() != "All")
{
topCount = int.Parse(cboTopCount.SelectedItem.ToString());
}
string viewerName = dbConfig.ViewerName.Replace("'", "''");
string alertParamName = dbConfig.AlertFieldName.Replace("'", "''").Replace("@", "");
string collectorTypeParamName = dbConfig.CollectorTypeFieldName.Replace("'", "''").Replace("@", "");
string categoryParamName = dbConfig.CategoryFieldName.Replace("'", "''").Replace("@", "");
string previousStateParamName = dbConfig.PreviousStateFieldName.Replace("'", "''").Replace("@", "");
string currentStateParamName = dbConfig.CurrentStateFieldName.Replace("'", "''").Replace("@", "");
string detailsParamName = dbConfig.DetailsFieldName.Replace("'", "''").Replace("@", "");
string datetimeParamName = dbConfig.DateTimeFieldName.Replace("'", "''").Replace("@", "");
object alertTypeValue = cboAlertLevel.SelectedIndex;
if (cboAlertLevel.SelectedIndex == 4)
{
alertTypeValue = DBNull.Value;
}
object currentStateValue = cboState.SelectedIndex;
if (cboState.SelectedIndex == 6)
{
currentStateValue = DBNull.Value;
}
object categoryValue;
if (txtCategory.Text.Length == 0)
{
categoryValue = DBNull.Value;
}
else
{
categoryValue = "%" + txtCategory.Text + "%";
}
object detailsValue;
if (txtSearchText.Text.Length == 0)
{
detailsValue = DBNull.Value;
}
else
{
detailsValue = "%" + txtSearchText.Text + "%";
}
object collectorTypeValue = DBNull.Value;
string sql = dbConfig.UseSPForViewer ? dbConfig.ViewerName : Properties.Resources.QueryTemplate
.Replace("@top", cboTopCount.SelectedItem.ToString())
.Replace("InsertDate", datetimeParamName)
.Replace("AlertLevel", alertParamName)
.Replace("CollectorType", collectorTypeParamName)
.Replace("Category", categoryParamName)
.Replace("PreviousState", previousStateParamName)
.Replace("CurrentState", currentStateParamName)
.Replace("Details", detailsParamName)
.Replace("AlertMessages", viewerName);
using (SqlConnection conn = new SqlConnection(dbConfig.GetConnectionString()))
{
try
{
conn.Open();
}
catch (System.Data.SqlClient.SqlException ex)
{
if (ex.Message.Contains("The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement"))
{
System.Threading.Thread.Sleep(3000);
toolStripStatusLabelDetails.Text = "Connection timed out! Retrying...";
Application.DoEvents();
//try again
conn.Open();
}
else
{
throw;
}
}
SqlParameter[] paramArr = new SqlParameter[]
{
new SqlParameter("@Top", topCount),
new SqlParameter("@FromDate", dateTimeChooserFrom.SelectedDateTime),
new SqlParameter("@ToDate", dateTimeChooserTo.SelectedDateTime),
new SqlParameter("@" + alertParamName, alertTypeValue)
{
DbType = DbType.Byte
},
new SqlParameter("@" + collectorTypeParamName, collectorTypeValue),
new SqlParameter("@" + categoryParamName, categoryValue),
new SqlParameter("@" + currentStateParamName, currentStateValue),
new SqlParameter("@" + detailsParamName, detailsValue)
};
using (SqlCommand cmnd = new SqlCommand(sql, conn))
{
cmnd.Prepare();
cmnd.Parameters.AddRange(paramArr);
if (dbConfig.UseSPForViewer)
{
cmnd.CommandType = CommandType.StoredProcedure;
}
else
{
cmnd.CommandType = CommandType.Text;
}
cmnd.CommandTimeout = dbConfig.CmndTimeOut;
using (SqlDataReader r = cmnd.ExecuteReader())
{
lvwMessages.Items.Clear();
List <ListViewItem> items = new List <ListViewItem>();
while (r.Read())
{
AlertMessage alertMessage = new AlertMessage();
alertMessage.InsertDate = (DateTime)r[datetimeParamName];
alertMessage.AlertLevel = AlertLevelConverter.GetAlertLevelFromText(r[alertParamName].ToString());
alertMessage.CollectorType = r[collectorTypeParamName].ToString();
alertMessage.PreviousState = CollectorStateConverter.GetCollectorStateFromText(r[previousStateParamName].ToString());
alertMessage.CurrentState = CollectorStateConverter.GetCollectorStateFromText(r[currentStateParamName].ToString());
alertMessage.Category = r[categoryParamName].ToString();
alertMessage.Details = r[detailsParamName].ToString();
ListViewItem lvi = new ListViewItem(alertMessage.InsertDate.ToString("yyyy-MM-dd"));
if (alertMessage.AlertLevel == AlertLevel.Info)// || alertMessage.AlertLevel == AlertLevel.Debug)
{
lvi.ImageIndex = 1;
infos++;
}
else if (alertMessage.AlertLevel == AlertLevel.Warning)
{
lvi.ImageIndex = 2;
warns++;
}
else if (alertMessage.AlertLevel == AlertLevel.Error || alertMessage.AlertLevel == AlertLevel.Crisis)
{
lvi.ImageIndex = 3;
errs++;
}
else
{
lvi.ImageIndex = 0;
debugs++;
}
lvi.SubItems.Add(alertMessage.InsertDate.ToString("HH:mm:ss"));
lvi.SubItems.Add(alertMessage.Category);
lvi.SubItems.Add(alertMessage.CurrentState.ToString());
string details = alertMessage.Details;
if (details.Length > 80)
{
details = details.Substring(0, 80);
}
lvi.SubItems.Add(details);
lvi.Tag = alertMessage;
items.Add(lvi);
}
lvwMessages.Items.AddRange(items.ToArray());
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
string counts = string.Format("{0} item(s), Info: {1}, Warn: {2}, Err {3}",
lvwMessages.Items.Count, infos, warns, errs);
if (debugs > 0)
{
counts += " Debug: " + debugs.ToString();
}
toolStripStatusLabelDetails.Text = counts;
lvwMessages.EndUpdate();
}
}
}
private void cmdTest_Click(object sender, EventArgs e)
{
string connStr = "";
string lastStep = "";
try
{
SQLDatabaseNotifierConfig tmpConfig = new SQLDatabaseNotifierConfig();
lastStep = "Setting up connection string";
tmpConfig.SqlServer = txtServer.Text;
tmpConfig.Database = txtDatabase.Text;
tmpConfig.IntegratedSec = chkIntegratedSec.Checked;
tmpConfig.UserName = txtUserName.Text;
tmpConfig.Password = txtPassword.Text;
connStr = tmpConfig.GetConnectionString();
lastStep = "Opening connection";
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
lastStep = "Inserting test message into database";
string cmndName = txtCmndValue.Text.Replace("'", "''");
string viewerName = txtViewerName.Text.Replace("'", "''");
string alertParamName = txtAlertFieldName.Text.Replace("'", "''").Replace("@", "");
string collectorTypeParamName = txtCollectorType.Text.Replace("'", "''").Replace("@", "");
string categoryParamName = txtCategoryFieldName.Text.Replace("'", "''").Replace("@", "");
string previousStateParamName = txtPreviousStateFieldName.Text.Replace("'", "''").Replace("@", "");
string currentStateParamName = txtCurrentStateFieldName.Text.Replace("'", "''").Replace("@", "");
string detailsParamName = txtDetailsFieldName.Text.Replace("'", "''").Replace("@", "");
string datetimeParamName = txtDateTimeFieldName.Text.Replace("'", "''").Replace("@", "");
int topCount = 1;
string sql = chkUseSP.Checked ? cmndName :
string.Format("Insert {0} ({1}, {2}, {3}, {4}, {5}, {6}) values(@{1}, @{2}, @{3}, @{4}, @{5}, @{6})",
cmndName,
alertParamName,
collectorTypeParamName,
categoryParamName,
previousStateParamName,
currentStateParamName,
detailsParamName);
byte alertTypeValue = 0;
byte previousState = 0;
byte currentState = 0;
using (SqlCommand cmnd = new SqlCommand(sql, conn))
{
SqlParameter[] paramArr = new SqlParameter[]
{
new SqlParameter("@" + alertParamName, alertTypeValue),
new SqlParameter("@" + collectorTypeParamName, "N/A"),
new SqlParameter("@" + categoryParamName, "Test"),
new SqlParameter("@" + previousStateParamName, previousState),
new SqlParameter("@" + currentStateParamName, currentState),
new SqlParameter("@" + detailsParamName, "Testing QuickMon database notifier insert")
};
cmnd.Parameters.AddRange(paramArr);
if (chkUseSP.Checked)
{
cmnd.CommandType = CommandType.StoredProcedure;
}
else
{
cmnd.CommandType = CommandType.Text;
}
cmnd.CommandTimeout = (int)numericUpDownCmndTimeOut.Value;
cmnd.ExecuteNonQuery();
}
lastStep = "Retrieve message from database";
sql = chkUseSP2.Checked ? viewerName : GetQuery(viewerName, alertParamName, collectorTypeParamName,
categoryParamName, previousStateParamName, currentStateParamName, detailsParamName, datetimeParamName);
using (SqlCommand cmnd = new SqlCommand(sql, conn))
{
SqlParameter[] paramArr = new SqlParameter[]
{
new SqlParameter("@Top", topCount),
new SqlParameter("@FromDate", DateTime.Now.AddDays(-1)),
new SqlParameter("@ToDate", DateTime.Now.AddMinutes(1)),
new SqlParameter("@" + alertParamName, alertTypeValue),
new SqlParameter("@" + categoryParamName, "Test"),
new SqlParameter("@" + currentStateParamName, currentState),
new SqlParameter("@" + detailsParamName, "Testing QuickMon database notifier insert")
};
cmnd.Parameters.AddRange(paramArr);
if (chkUseSP2.Checked)
{
cmnd.CommandType = CommandType.StoredProcedure;
}
else
{
cmnd.CommandType = CommandType.Text;
}
cmnd.CommandTimeout = (int)numericUpDownCmndTimeOut.Value;
using (SqlDataReader r = cmnd.ExecuteReader())
{
if (!r.Read())
{
throw new Exception("No data returned by server");
}
}
}
}
MessageBox.Show("Test was successful!", "Test connection", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Failed at step: {0}\r\nDetails: {1}", lastStep, ex.Message), "Test connection", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}