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