private void PerformWrite(ConcurrentQueue <DataTable> dataQueue, XEReaderCollectionItemConfig itm)
{
while (true)
{
DataTable collectedData = null;
while (dataQueue.Count > 0)
{
//
// Merge collected data in a single DataTable
//
DataTable dt = null;
dataQueue.TryDequeue(out dt);
if (collectedData == null)
{
collectedData = dt;
}
else
{
collectedData.Merge(dt);
}
collectedData.RemotingFormat = System.Data.SerializationFormat.Binary;
}
//
// Write to the cache file
//
if (collectedData != null && collectedData.Rows.Count > 0)
{
// Remove the columns not included in the output
List <String> columnsToRemove = new List <String>();
foreach (DataColumn dc in collectedData.Columns)
{
if (!itm.Columns.Contains(dc.ColumnName) && !dc.ColumnName.Equals("collection_time"))
{
columnsToRemove.Add(dc.ColumnName);
}
}
foreach (String colName in columnsToRemove)
{
collectedData.Columns.Remove(colName);
}
WriteCacheFile(collectedData, itm);
}
Thread.Sleep(itm.Frequency * 1000);
}
}
/*
* Controls that the session is running. If it's not running
*/
private void CheckSession(XEReaderCollectionItemConfig itm)
{
String sql_check_session = @"
SELECT name,
state =
CASE WHEN EXISTS (
SELECT 1
FROM sys.dm_xe_sessions AS x
WHERE name = sess.name
) THEN 'ON'
ELSE 'OFF'
END
FROM sys.server_event_sessions AS sess
WHERE name = '{0}'
";
sql_check_session = String.Format(sql_check_session, itm.SessionName);
DataTable sess = CollectorUtils.GetDataTable(SourceServerInstance, "master", sql_check_session);
if (sess.Rows.Count <= 0)
{
// create the session
CollectorUtils.InvokeSqlCmd(SourceServerInstance, "master", itm.SessionDefinition);
// repeat the check to see if
// the session has been started
sess = CollectorUtils.GetDataTable(SourceServerInstance, "master", sql_check_session);
}
if (sess.Rows[0]["state"].ToString().Equals("OFF"))
{
// TODO: start the session
String sql_startSession = @"
ALTER EVENT SESSION [{0}]
ON SERVER
STATE = start;
";
sql_startSession = String.Format(sql_startSession, itm.SessionName);
CollectorUtils.InvokeSqlCmd(SourceServerInstance, "master", sql_startSession);
}
}
private void initData(string text)
{
XEReaderCollectionItemConfig cfg = parse(text);
// XE Session
textBox1.Text = cfg.SessionName;
textBox2.Text = Regex.Replace(cfg.SessionDefinition, @"\r\n?|\n", "\r\n");
textBox3.Text = cfg.OutputTable;
textBox5.Text = cfg.Filter;
textBox4.Text = String.Join(",", cfg.Columns.ToArray());
// Alert
if (cfg.Alerts.Count > 0)
{
AlertConfig ac = cfg.Alerts[0];
textBox6.Text = ac.Sender;
textBox7.Text = ac.Recipient;
textBox8.Text = ac.Subject;
foreach (string itm in comboBox2.Items)
{
if (itm.Equals(ac.Importance.ToString(), StringComparison.CurrentCultureIgnoreCase))
{
comboBox2.SelectedItem = itm;
}
}
textBox10.Text = String.Join(",", ac.Columns.ToArray());
textBox11.Text = ac.Filter;
checkBox1.Checked = ac.Enabled;
checkBox2.Checked = ac.WriteToErrorLog;
checkBox3.Checked = ac.WriteToWindowsLog;
foreach (string itm in comboBox1.Items)
{
if (itm.Equals(ac.Mode.ToString(), StringComparison.CurrentCultureIgnoreCase))
{
comboBox1.SelectedItem = itm;
}
}
textBox12.Text = ac.Delay.ToString();
}
}
public void CollectData()
{
logger = new CollectorLogger(SourceServerInstance, CollectionSetUid, ItemId);
if (verbose)
{
logger.logMessage("--------------------------------");
}
if (verbose)
{
logger.logMessage(" ExtendedXEReaderCollector ");
}
if (verbose)
{
logger.logMessage("--------------------------------");
}
if (verbose)
{
logger.logMessage("Copyright© sqlconsulting.it 2014");
}
if (verbose)
{
logger.logMessage("-");
}
if (verbose)
{
logger.logMessage("Loading configuration");
}
//
// Load Configuration
//
cfg = new XEReaderCollectorConfig();
cfg.readFromDatabase(SourceServerInstance, CollectionSetUid, ItemId);
String connectionString = String.Format(@"Data Source = {0}; Initial Catalog = master; Integrated Security = SSPI", SourceServerInstance);
collectorThread = Thread.CurrentThread;
Task.Factory.StartNew(() => checkCollectionSetEnabled());
if (verbose)
{
logger.logMessage("Entering collection items loop");
}
foreach (CollectionItemConfig item in cfg.collectionItems)
{
XEReaderCollectionItemConfig itm = (XEReaderCollectionItemConfig)item;
if (verbose)
{
logger.logMessage("Processing item n. " + itm.Index);
}
if (verbose)
{
logger.logMessage("Processing session " + itm.SessionDefinition);
}
var dataQueue = new ConcurrentQueue <DataTable>();
DateTime lastEventFlush = new DateTime(1900, 1, 1);
CheckSession(itm);
Task.Factory.StartNew(() => PerformWrite(dataQueue, itm));
// Queries an existing session
Microsoft.SqlServer.XEvent.Linq.QueryableXEventData events = new QueryableXEventData(
connectionString,
itm.SessionName,
EventStreamSourceOptions.EventStream,
EventStreamCacheOptions.DoNotCache);
foreach (PublishedEvent evt in events)
{
try
{
DataTable dt = ReadEvent(evt);
//
// Apply filter
//
DataView dw = new DataView(dt);
dw.RowFilter = itm.Filter;
dt = dw.ToTable();
//
// Enqueue the collected data for the consumer thread
//
if (dt != null && dt.Rows.Count > 0)
{
dataQueue.Enqueue(dt);
}
//
// Process rows to fire alerts if needed
//
foreach (AlertConfig currentAlert in itm.Alerts)
{
foreach (DataRow currentRow in dt.Select(currentAlert.Filter))
{
//TODO: Process alerts
ProcessAlert(currentAlert, currentRow);
}
}
}
catch (Exception e)
{
// capture the session related exceptions
logger.logMessage(e.StackTrace);
// try restarting the session event stream
try
{
events = new QueryableXEventData(
connectionString,
itm.SessionName,
EventStreamSourceOptions.EventStream,
EventStreamCacheOptions.DoNotCache);
}
catch (Exception ex)
{
// Unable to restart the events stream
logger.logMessage(ex.StackTrace);
throw ex;
}
}
}
}
logger.cleanupLogFiles(cfg.DaysUntilExpiration);
}
private XEReaderCollectionItemConfig parse(string text)
{
string sql = @"
WITH XMLNAMESPACES('DataCollectorType' AS ns),
XESession AS (
SELECT
x.value('Name[1]','varchar(max)') AS xe_session_name,
x.value('OutputTable[1]', 'varchar(max)') AS outputTable,
x.value('Definition[1]', 'varchar(max)') AS xe_session_definition,
x.value('Filter[1]', 'varchar(max)') AS xe_session_filter,
x.value('ColumnsList[1]', 'varchar(max)') AS xe_session_columnslist
FROM @x.nodes('/ns:ExtendedXEReaderCollector/Session') Q(x)
),
XEAlert AS (
SELECT
x.value('Sender[1]','varchar(max)') AS alert_sender,
x.value('Recipient[1]','varchar(max)') AS alert_recipient,
x.value('Filter[1]', 'varchar(max)') AS alert_filter,
x.value('ColumnsList[1]', 'varchar(max)') AS alert_columnslist,
x.value('Mode[1]', 'varchar(max)') AS alert_mode,
x.value('Importance[1]', 'varchar(max)') AS alert_importance_level,
x.value('Delay[1]', 'varchar(max)') AS alert_delay,
x.value('Subject[1]', 'varchar(max)') AS alert_subject,
x.value('Body[1]', 'varchar(max)') AS alert_body,
x.value('AttachmentFileName[1]', 'varchar(max)') AS alert_attachment_filename,
x.value('@WriteToERRORLOG[1]', 'varchar(max)') AS alert_write_to_errorlog,
x.value('@WriteToWindowsLog[1]', 'varchar(max)') AS alert_write_to_windowslog,
x.value('@Enabled[1]', 'varchar(max)') AS alert_enabled
FROM @x.nodes('/ns:ExtendedXEReaderCollector/Alert') Q(x)
)
SELECT *
FROM XESession
CROSS JOIN XEAlert
";
int ConnectionTimeout = 15;
int QueryTimeout = 600;
String ConnectionString = String.Format("Server={0};Database={1};Integrated Security=True;Connect Timeout={2}", Manager.ServerName, "msdb", ConnectionTimeout);
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConnectionString;
XEReaderCollectionItemConfig cfg = new XEReaderCollectionItemConfig();
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = QueryTimeout;
cmd.Parameters.Add("x", SqlDbType.Xml, -1);
cmd.Parameters[0].Value = text;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
DataRow currentRow = dt.Rows[0];
cfg.SessionName = currentRow["xe_session_name"].ToString();
cfg.OutputTable = currentRow["outputTable"].ToString();
cfg.SessionDefinition = currentRow["xe_session_definition"].ToString();
cfg.Filter = currentRow["xe_session_filter"].ToString();
cfg.Columns = new List <String>(currentRow["xe_session_columnslist"].ToString().Split(','));
cfg.Enabled = true;
AlertConfig a = new AlertConfig();
a.Sender = currentRow["alert_sender"].ToString();
a.Recipient = currentRow["alert_recipient"].ToString();
a.Filter = currentRow["alert_filter"].ToString();
a.Columns = new List <String>(currentRow["alert_columnslist"].ToString().Split(','));
a.Mode = (Sqlconsulting.DataCollector.Utils.AlertMode)
Enum.Parse(typeof(Sqlconsulting.DataCollector.Utils.AlertMode), currentRow["alert_mode"].ToString());
a.Importance = (Sqlconsulting.DataCollector.Utils.ImportanceLevel)
Enum.Parse(typeof(Sqlconsulting.DataCollector.Utils.ImportanceLevel), currentRow["alert_importance_level"].ToString());
a.Delay = Int32.Parse(currentRow["alert_delay"].ToString());
a.Subject = currentRow["alert_subject"].ToString();
a.WriteToErrorLog = Boolean.Parse(currentRow["alert_write_to_errorlog"].ToString());
a.WriteToWindowsLog = Boolean.Parse(currentRow["alert_write_to_windowslog"].ToString());
a.Enabled = Boolean.Parse(currentRow["alert_enabled"].ToString());
cfg.Alerts.Add(a);
}
}
finally
{
conn.Close();
}
return(cfg);
}
}
