private static async Task <int> SendBlobToOMS(CloudPageBlob blob, int eventNumber, OMSIngestionApi oms) { string fileName = Path.Combine(Environment.GetEnvironmentVariable("WEBROOT_PATH"), Path.GetRandomFileName() + ".xel"); try { await blob.DownloadToFileAsync(fileName, FileMode.OpenOrCreate); using (var events = new QueryableXEventData(fileName)) { List <SQLAuditLog> list = ParseXEL(events, eventNumber, blob.Name); if (list.Count > 0) { var jsonList = JsonConvert.SerializeObject(list); await oms.SendOMSApiIngestionFile(jsonList); eventNumber += list.Count; totalLogs += list.Count; } } } finally { File.Delete(fileName); } s_consoleTracer.TraceEvent(TraceEventType.Information, 0, "Done processing: {0}", blob.Uri); return(eventNumber); }
/// <summary> /// https://stackoverflow.com/questions/661561/how-to-update-the-gui-from-another-thread-in-c /// </summary> /// <param name="progress"></param> /// <param name="SessionName"></param> /// <param name="sqlCSB"></param> public void ReadEventStream(IProgress <string> progress, string SessionName, SqlConnectionStringBuilder sqlCSB) { cancelProcessing = false; using (QueryableXEventData xEvents = new QueryableXEventData( sqlCSB.ConnectionString, SessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache)) { foreach (PublishedEvent xEvent in xEvents) { if (cancelProcessing) { //progress.Report("xEvents.Dispose();" + Environment.NewLine); xEvents.Dispose(); break; } else { if (eventsHandled.Contains(xEvent.Name)) { progress.Report(xEvent.Name); responseOpts.Respond(xEvent); } } } } }
XeTypeGenerator(string xeFileName) { _code = new Dictionary <string, string>(); var enumerable = new QueryableXEventData(xeFileName); _uniqueTypes = new SortedDictionary <string, SortedDictionary <string, PublishedEvent> >(); foreach (var e in enumerable) { SortedDictionary <string, PublishedEvent> typesInPackage; if (!_uniqueTypes.TryGetValue(e.Package.Name, out typesInPackage)) { typesInPackage = new SortedDictionary <string, PublishedEvent>(); _uniqueTypes.Add(e.Package.Name, typesInPackage); } if (typesInPackage.ContainsKey(e.Name)) { continue; } typesInPackage.Add(e.Name, e); } GenerateAll(); }
public SQLWorker() { SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(); scsb.ApplicationName = "PluralsightDemo"; scsb.DataSource = "PS-SQL2K12"; scsb.InitialCatalog = "master"; scsb.IntegratedSecurity = true; SqlConnection conn = new SqlConnection(scsb.ConnectionString); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = @"IF NOT EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'PS_StreamDemo') BEGIN -- Session does not exist, so create it. CREATE EVENT SESSION PS_StreamDemo ON SERVER ADD EVENT sqlserver.rpc_completed( ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.nt_username, sqlserver.server_principal_name, sqlserver.session_id)), ADD EVENT sqlserver.sp_statement_completed( ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.nt_username, sqlserver.server_principal_name, sqlserver.session_id)), ADD EVENT sqlserver.sql_batch_completed( ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.nt_username, sqlserver.server_principal_name, sqlserver.session_id)), ADD EVENT sqlserver.sql_statement_completed( ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.nt_username, sqlserver.server_principal_name, sqlserver.session_id)) -- Now start the session ALTER EVENT SESSION PS_StreamDemo ON SERVER STATE=START; END ELSE BEGIN -- Session exists, is it running? IF NOT EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = 'PS_StreamDemo') BEGIN -- Session is not running so start it. ALTER EVENT SESSION PS_StreamDemo ON SERVER STATE=START; END END"; cmd.ExecuteNonQuery(); stream = new QueryableXEventData( scsb.ConnectionString, "PS_StreamDemo", EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache); t = new Thread(ReadEventStream); t.Start(); }
public SQLWorker(string[] filelist) { filedata = new QueryableXEventData(filelist); t = new Thread(ReadEventData); t.Start(); }
public void ProcessEvent(QueryableXEventData data) { foreach (PublishedEvent x_event in data) { string name = x_event.Name; reads++; if (server == Replica.Primary) { foreach (EventLatency el in eventLatencies) { foreach (EventWithMode em in el.primaryEvents) { if (em.e.ToString() == name) { if (em.mode == -1 || em.mode == EventMetaData.GetEventMode(x_event)) { el.eventDB.Push(x_event); } } } } } else if (server == Replica.Secondary) { foreach (EventLatency el in eventLatencies) { foreach (EventWithMode em in el.secondaryEvents) { if (em.e.ToString() == name) { if (em.mode == -1 || em.mode == EventMetaData.GetEventMode(x_event)) { el.eventDB.Push(x_event); } } } } } if (reads % 4000 == 0) { //UInt64 i = GetAllCount(); var percent = (int)(reads * 100 / eventCount); fn_UpdateMsg("File:" + fileNum + "/" + totalFile + ", Processing " + reads.ToString() + "/" + eventCount.ToString() + " (" + percent.ToString() + "%)"); int cnt = GetAllQueueLength(); if (cnt > 5000) //need to wait for a while { Thread.Sleep((cnt / 5000) * 100); } } // if (k > 1000) break; } }
static void ProcessXELwithXEM() { //increment file count in the tracker myTrackStatus.m_Number_of_Files++; EventHolder x_event_holder; x_event_holder = new EventHolder(myInputParameters.m_XEL_File_To_Process); //check if the file exists if (File.Exists(myInputParameters.m_XEL_File_To_Process)) { Console.ForegroundColor = ConsoleColor.Cyan; Console.WriteLine("Thread {0} : Start processing file : {1}", Thread.CurrentThread.ManagedThreadId, x_event_holder.m_event_File_Info.Name); Console.ResetColor(); // save information about this file into the tracking table // get the identity value back to use as default value in the data table x_event_holder.m_fileID = x_event_holder.InsertFileInfoIntoTrackingTable(); DateTime start_Time = DateTime.Now; //open the XEL file along with the XEM file String[] xelFiles = new String[1]; xelFiles[0] = myInputParameters.m_XEL_File_To_Process; String[] xemFiles = new String[1]; xemFiles[0] = myInputParameters.m_XEM_File_To_Process; QueryableXEventData x_event_file = new QueryableXEventData(xelFiles, xemFiles); // process the metadata from the XEL file and add information to the data tables // need to do this only one time, so need to sync with other file processors myTrackStatus.acquire_metadata_Lock(true); { if (false == myEventMetadata.m_dt_Initialized) { myEventMetadata.ExtractMetadataFromFile(x_event_file); myEventMetadata.m_dt_Initialized = true; XELoader.FileProcessor.myTrackStatus.IsTableCreated = true; } } myTrackStatus.release_metadata_Lock(true); // now loop through each event in the file, extract information and store it in the data table foreach (PublishedEvent x_event in x_event_file) { x_event_holder.ProcessEvent(x_event); } //foreach loop // Now we can commit left over in the data tables to the database x_event_holder.SaveAllDataTablesToSQLDatabase(); x_event_holder.ClearAllDataTables(); DateTime end_Time = DateTime.Now; x_event_holder.m_Total_Processing_Duration = end_Time - start_Time; // update status to console Console.ForegroundColor = ConsoleColor.Cyan; Console.WriteLine("Thread {0} : Finished processing file {1} , duration : {2} , bulk copy duration : {3} ", Thread.CurrentThread.ManagedThreadId, x_event_holder.m_event_File_Info.Name, x_event_holder.m_Total_Processing_Duration.ToString(), x_event_holder.m_Bulk_Copy_Duration.ToString()); Console.ResetColor(); // save runtime for each file in the backend table x_event_holder.UpdateFileInfoInTrackingTable(); } }
static void ProcessEvents() { QueryableXEventData eventstream = new QueryableXEventData( @"Data Source = " + XECaptureServer + "; Initial Catalog = master; Integrated Security = SSPI", "XERMLCapture", EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache); foreach (PublishedEvent evt in eventstream) { ReplaySingleEvent(evt); } }
public UInt64 GetCount(QueryableXEventData data) { foreach (PublishedEvent x_event in data) { if (eventCount % 8000 == 0) { fn_UpdateMsg("File:" + fileNum2 + "/" + totalFile + ", Caculating " + eventCount); } eventCount++; } return(eventCount); }
public static IObservable <PublishedEvent> FromFiles(params string[] xelFiles) { if (xelFiles == null) { throw new ArgumentNullException("xelFiles"); } // Looks like XEvent has bug handling relative paths string[] fullPaths = (from f in xelFiles select Path.GetFullPath(f)).ToArray(); var enumerable = new QueryableXEventData(xelFiles); return(enumerable.ToObservable(ThreadPoolScheduler.Instance)); }
private void InitializeXESession() { string ConnectionString = @"Integrated Security=SSPI;Initial Catalog=master;Data Source=SQL2019"; QueryableXEventData eventstream = new QueryableXEventData( ConnectionString, "XERMLCapture", EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache); foreach (PublishedEvent evt in eventstream) { this.Invoke((MethodInvoker) delegate() { AddRow(evt); }); } }
/// <summary> /// Read a XEL file, consume all callstacks, hash them and return the equivalent XML /// </summary> /// <param name="xelFileName">Full path to the XEL file to read</param> /// <returns>XML equivalent of the histogram corresponding to these events</returns> internal string GetXMLEquivalent(string xelFileName) { if (!File.Exists(xelFileName)) { return(null); } var callstackSlots = new Dictionary <string, long>(); var xmlEquivalent = new StringBuilder(); using (var xelEvents = new QueryableXEventData(xelFileName)) { Parallel.ForEach(xelEvents, evt => { foreach (PublishedAction act in evt.Actions) { if (act.Value is CallStack) { CallStack castStack = (act.Value as CallStack); var callStackString = castStack.ToString(); lock (callstackSlots) { if (!callstackSlots.ContainsKey(callStackString)) { callstackSlots.Add(callStackString, 1); } else { callstackSlots[callStackString]++; } } } } }); } xmlEquivalent.AppendLine("<HistogramTarget truncated=\"0\" buckets=\"256\">"); foreach (KeyValuePair <string, long> item in callstackSlots.OrderByDescending(key => key.Value)) { xmlEquivalent.AppendFormat("<Slot count=\"{0}\"><value>{1}</value>", callstackSlots[item.Key], item.Key); xmlEquivalent.AppendLine(); } xmlEquivalent.AppendLine("</HistogramTarget>"); return(xmlEquivalent.ToString()); }
private static async Task <int> SendBlobToOMS(CloudBlob blob, int eventNumber, OMSIngestionApi oms) { RetryPolicy retryPolicy = new RetryPolicy(RetryPolicy.DefaultFixed.ErrorDetectionStrategy, DefaultRetryCount); s_consoleTracer.TraceEvent(TraceEventType.Information, 0, "Processing: {0}", blob.Uri); string fileName = Path.Combine(GetLocalStorageFolder(), Path.GetRandomFileName() + ".xel"); try { OperationContext operationContext = new OperationContext(); operationContext.RequestCompleted += (sender, e) => PrintHeaders(e); await retryPolicy.ExecuteAsync((() => blob.DownloadToFileAsync(fileName, FileMode.OpenOrCreate, null, null, operationContext))); List <SQLAuditLog> list; using (var events = new QueryableXEventData(fileName)) { list = ParseXEL(events, eventNumber, blob.Name); } IEnumerable <List <SQLAuditLog> > chunkedList = list.Chunk(10000); foreach (List <SQLAuditLog> chunk in chunkedList) { var jsonList = JsonConvert.SerializeObject(chunk); await oms.SendOMSApiIngestionFile(jsonList); eventNumber += chunk.Count; totalLogs += chunk.Count; } } catch (Exception e) { s_consoleTracer.TraceEvent(TraceEventType.Error, 0, "Failed processing: {0}. Reason: {1}", blob.Uri, e); throw; } finally { try { File.Delete(fileName); } catch (Exception e) { s_consoleTracer.TraceEvent(TraceEventType.Information, 0, "Was not able to delete file: {0}. Reason: {1}", fileName, e.Message); } } s_consoleTracer.TraceEvent(TraceEventType.Information, 0, "Done processing: {0}", blob.Uri); return(eventNumber); }
static void CreateOutput() { QueryableXEventData events = new QueryableXEventData(inputFileSpec); using (FileStream f = File.Create(outputFileSpec)) { foreach (PublishedEvent evt in events) { if (evt.Name == "rpc_starting" || evt.Name == "sql_batch_starting" || evt.Name == "login" || evt.Name == "logout") { StringBuilder sb = new StringBuilder(); sb.Append($"-- {evt.Name}\r\n"); sb.Append($"-- Session id: {evt.Actions["session_id"].Value}\t\t\t"); sb.Append($"-- Sequence #: {evt.Actions["event_sequence"].Value}\r\n"); sb.Append($"-- Client Hostname {evt.Actions["client_hostname"].Value}\t"); sb.Append($"-- Username {evt.Actions["nt_username"].Value}\r\n\r\n"); if (evt.Name == "rpc_starting") { sb.Append(evt.Fields["statement"].Value + "\r\n"); sb.Append("GO \r\n"); } if (evt.Name == "sql_batch_starting") { sb.Append(evt.Fields["batch_text"].Value + "\r\n"); sb.Append("GO \r\n\r\n"); } if (evt.Name == "login") { sb.Append("-- Connection Options for this login\r\n"); sb.Append(evt.Fields["options_text"].Value + "\r\n"); } sb.Append("\r\n"); Byte[] info = new UTF8Encoding(false).GetBytes(sb.ToString()); f.Write(info, 0, info.Length); } } } }
//Create import instances from metadata public void CreateTablesFromMetadata(QueryableXEventData data) { foreach (IMetadataGeneration mgen in data.EventProvider.MetadataGenerations) { // iterate through each package to extract events foreach (IPackage xe_package in mgen.Packages) { //iterate through each event foreach (IEventMetadata xe_event in xe_package.Events) { //justfiy which database to create. if (server == Replica.Primary) { foreach (EventLatency el in eventLatencies) { foreach (EventWithMode em in el.primaryEvents) { if (em.e.ToString() == xe_event.Name) { el.eventDB.AddTable(xe_event); } } } } else if (server == Replica.Secondary) { foreach (EventLatency el in eventLatencies) { foreach (EventWithMode em in el.secondaryEvents) { if (em.e.ToString() == xe_event.Name) { el.eventDB.AddTable(xe_event); } } } } } } } }//CreateTablesFromMetadata
static void ReadXeEvents() { using (var events = new QueryableXEventData(connectionString, xeSession, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache)) { foreach (var x in events) { if (x.Name == "object_altered" || x.Name == "object_created" | x.Name == "object_deleted") { PublishedEventField ddl_phase; if (x.Fields.TryGetValue(nameof(ddl_phase), out ddl_phase)) { var fs = x.Fields; if (ddl_phase.Value.ToString() == "Commit") { Console.WriteLine($"\nEvent {x.Name}.\nDDL Phase: {fs["ddl_phase"].Value}.\nObject: id-{fs["object_id"].Value}; name-{fs["object_name"].Value}.\nDatabase: id-{fs["database_id"].Value}; name-{x.Actions["database_name"].Value}.\nSql text: {x.Actions["sql_text"].Value}"); } } } } } }
/// <summary> /// This function will iterate through every event in a set of files from a directory /// and return the event name followed by the list of fields and action and their /// values for the event. /// </summary> /// <param name="args"></param> static void Main(string[] args) { // Create an instance of the enumerable object using a set of XEL files as the source. QueryableXEventData events = new QueryableXEventData(@"C:\Temp\*.xel"); // Iterate through each event in the file. foreach (PublishedEvent evt in events) { // Optionally you can check for specific events rather than respond to each event. // Obviously this is going to run for everything. if (evt.Name == evt.Name) { // List the event name Console.WriteLine(evt.Name); // Iterate through each field in the current event and return its name and value. foreach (PublishedEventField fld in evt.Fields) { Console.WriteLine("\tField: {0} = {1}", fld.Name, fld.Value); // Optionally take an action if you find a specific field. if (fld.Value.ToString() == "foo") { // Add really important code here. } } // Iterate through each action in thecurrent event and return its name and value. foreach (PublishedAction act in evt.Actions) { Console.WriteLine("\tAction: {0} = {1}", act.Name, act.Value); } } } Console.Read(); }
XeTypeGenerator(string xeFileName) { _code = new Dictionary<string, string>(); var enumerable = new QueryableXEventData(xeFileName); _uniqueTypes = new SortedDictionary<string, SortedDictionary<string, PublishedEvent>>(); foreach (var e in enumerable) { SortedDictionary<string, PublishedEvent> typesInPackage; if (!_uniqueTypes.TryGetValue(e.Package.Name, out typesInPackage)) { typesInPackage = new SortedDictionary<string, PublishedEvent>(); _uniqueTypes.Add(e.Package.Name, typesInPackage); } if (typesInPackage.ContainsKey(e.Name)) continue; typesInPackage.Add(e.Name, e); } GenerateAll(); }
private static List <SQLAuditLog> ParseXEL(QueryableXEventData events, int eventNumber, string blobName) { int count = 0; List <SQLAuditLog> list = new List <SQLAuditLog>(); foreach (var currentEvent in events) { if (count >= eventNumber) { try { SQLAuditLog currentLog = new SQLAuditLog(currentEvent); list.Add(currentLog); } catch (Exception ex) { s_consoleTracer.TraceEvent(TraceEventType.Error, 0, "Error: Could not send event number: {0}, from blob: {1}", count, blobName, ex); } } count++; } return(list); }
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); }
public void Convert() { Dictionary <String, CsvColumn> columns = new Dictionary <string, CsvColumn>(); logger.Trace(String.Format("Parsing starting {0}", DateTime.Now)); using (QueryableXEventData eventStream = new QueryableXEventData(InputFile)) { // Read the file once to get all the columns foreach (PublishedEvent xevent in eventStream) { foreach (PublishedAction action in xevent.Actions) { if (!columns.ContainsKey(action.Name)) { columns.Add(action.Name, new CsvColumn() { Name = action.Name, Type = 'a', Position = columns.Count }); } } foreach (PublishedEventField field in xevent.Fields) { if (!columns.ContainsKey(field.Name)) { columns.Add(field.Name, new CsvColumn() { Name = field.Name, Type = 'f', Position = columns.Count }); } } } } logger.Trace(String.Format("Parsing finished {0}", DateTime.Now)); List <CsvColumn> orderedColumns = new List <CsvColumn>(columns.Count); foreach (CsvColumn col in columns.Values) { orderedColumns.Insert(col.Position, col); } logger.Trace(String.Format("Starting output {0}", DateTime.Now)); using (QueryableXEventData eventStream = new QueryableXEventData(InputFile)) { using (BufferedStream f = new BufferedStream(new FileStream(OutputFile, FileMode.Append, FileAccess.Write), 4096000)) { using (TextWriter textWriter = new StreamWriter(f)) { using (var csv = new CsvWriter(textWriter, CultureInfo.CurrentCulture)) { // Write Headers csv.WriteField("name"); csv.WriteField("timestamp"); csv.WriteField("timestamp(UTC)"); foreach (CsvColumn col in orderedColumns) { csv.WriteField(col.Name); } csv.NextRecordAsync(); // Write Data foreach (PublishedEvent xevent in eventStream) { csv.WriteField(xevent.Name); csv.WriteField(xevent.Timestamp); csv.WriteField(xevent.Timestamp.ToUniversalTime()); foreach (CsvColumn col in orderedColumns) { if (col.Type == 'f') { PublishedEventField theValue = null; if (xevent.Fields.TryGetValue(col.Name, out theValue)) { csv.WriteField(theValue.Value); } else { csv.WriteField(""); } } else { PublishedAction theValue = null; if (xevent.Actions.TryGetValue(col.Name, out theValue)) { csv.WriteField(theValue.Value); } else { csv.WriteField(""); } } } csv.NextRecordAsync(); } } } } } logger.Trace(String.Format("Output finished {0}", DateTime.Now)); }
internal void Process() { string connectionString = "Data Source=" + ServerName + ";"; if (String.IsNullOrEmpty(DatabaseName)) { connectionString += "Initial Catalog = master; "; } else { connectionString += "Initial Catalog = " + DatabaseName + "; "; } if (String.IsNullOrEmpty(UserName)) { connectionString += "Integrated Security = SSPI; "; } else { connectionString += "User Id = " + UserName + "; "; connectionString += "Password = "******"; "; } logger.Info(String.Format("Connecting to XE session '{0}' on server '{1}'", SessionName, ServerName)); QueryableXEventData eventStream = null; try { eventStream = new QueryableXEventData( connectionString, SessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache); } catch (Exception e) { var ioe = new InvalidOperationException(String.Format("Unable to connect to the Extended Events session {0} on server {1}", SessionName, ServerName), e); logger.Error(ioe); throw ioe; } logger.Info($"Connected to {ServerName}."); try { foreach (PublishedEvent xevent in eventStream) { if (stopped) { break; } // Pass events to the responses foreach (Response r in Responses) { // filter out unwanted events // if no events are specified, will process all if (r.Events.Count > 0) { if (!r.Events.Contains(xevent.Name, StringComparer.CurrentCultureIgnoreCase)) { continue; } } try { r.Process(xevent); } catch (Exception e) { if (FailOnProcessingError) { throw; } else { logger.Error(e, ServerName); } } } } } catch (Exception e) { logger.Error(e, ServerName); throw; } }
static void Main(string[] args) { Log.Logger = new LoggerConfiguration() .ReadFrom.AppSettings() .CreateLogger(); string xSession = ConfigurationManager.AppSettings["xSessionName"]; string ssasConnectionString = ConfigurationManager.AppSettings["ssasConnectionString"]; string sqlConnectionString = ConfigurationManager.AppSettings["sqlConnectionString"]; string sqlInsert = "INSERT INTO [dbo].[SSAS_Monitoring] ([TimeStampUtc],[EventType],[ServerName],[DatabaseName],[UserName],[StartTime],[Duration],[CPUTime],[QueryText],[ObjectName]) VALUES (@TimeStampUtc,@EventType,@ServerName,@DatabaseName,@UserName,@StartTime,@Duration,@CPUTime,@QueryText,@ObjectName)"; try { Log.Information("SSAS xEvent reader started."); Log.Information("Establishing connection..."); using (var connection = new AdomdConnection(ssasConnectionString)) { connection.Open(); Log.Information("Connected."); Log.Information("Subscribing to xEvent stream session..."); using (var command = connection.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "<Subscribe xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "<Object xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "<TraceID>" + xSession + "</TraceID>" + "</Object>" + "</Subscribe>"; Log.Information("Waiting for events to capture..."); using (var reader = XmlReader.Create(command.ExecuteXmlReader(), new XmlReaderSettings() { Async = true })) using (var sqlConnection = new SqlConnection(sqlConnectionString)) using (var data = new QueryableXEventData(reader, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.CacheToDisk)) { foreach (PublishedEvent @event in data) { Log.Information($"Event captured: {@event.Name}"); CapturedEvent capturedEvent; try { capturedEvent = ConvertEvent(@event); } catch (ConvertEventException ex) { Log.Error(ex, "Conversion error occured between publishedEvent - capturedEvent."); continue; } using (var sqlCommand = new SqlCommand()) { sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = sqlInsert; sqlCommand.Parameters.AddWithValue("@TimeStampUtc", capturedEvent.TimeStampUtc); sqlCommand.Parameters.AddWithValue("@EventType", capturedEvent.EventType); sqlCommand.Parameters.AddWithValue("@ServerName", capturedEvent.ServerName); sqlCommand.Parameters.AddWithValue("@DatabaseName", capturedEvent.DatabaseName); sqlCommand.Parameters.AddWithValue("@UserName", capturedEvent.UserName); sqlCommand.Parameters.AddWithValue("@StartTime", capturedEvent.StartTime); sqlCommand.Parameters.AddWithValue("@Duration", capturedEvent.Duration); sqlCommand.Parameters.AddWithValue("@CPUTime", capturedEvent.CPUTime); sqlCommand.Parameters.AddWithValue("@QueryText", capturedEvent.QueryText); sqlCommand.Parameters.AddWithValue("@ObjectName", string.IsNullOrEmpty(capturedEvent.ObjectName) ? DBNull.Value : (object)capturedEvent.ObjectName); if (sqlConnection.State != ConnectionState.Open) { sqlConnection.Open(); } sqlCommand.ExecuteNonQuery(); if (sqlConnection.State == ConnectionState.Open) { sqlConnection.Close(); } Log.Information($"Event ({@event.Name}) saved to database."); } //eof using SqlCommand } //eof foreach PublishedEvents }//eof using xEventData } //eof using command } //eof using ssasConnection } //eof try catch (AdomdConnectionException ex) { Log.Error(ex, "There was an error when connecting to SSAS."); } catch (AdomdErrorResponseException ex) { Log.Error(ex, "There was an unknown Adomd exception when connecting to SSAS."); } catch (AdomdException ex) { Log.Error(ex, "There was an unknown Adomd exception when connecting to SSAS."); } catch (XmlException ex) { Log.Error(ex, "There was an error with the XmlReader used in QueryableXEventData."); } catch (XEventException ex) { Log.Error(ex, "There was an xEvent exception."); } catch (ConvertEventException ex) { Log.Error(ex, "Conversion error occured between publishedEvent - capturedEvent."); } catch (SqlException ex) { Log.Error(ex, "There was an error when executing an SQL operation."); } catch (Exception ex) { Log.Error(ex, "An unhandled exception occured!"); } }
public override void ReadEvents() { EventCount = 0; SqlTransformer transformer = new SqlTransformer(); using (QueryableXEventData eventstream = new QueryableXEventData( ConnectionString, SessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache)) { var eventsEnumerator = eventstream.GetEnumerator(); while (!stopped && eventsEnumerator.MoveNext()) { PublishedEvent evt = eventsEnumerator.Current; ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent(); string commandText = String.Empty; if (evt.Name == "rpc_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "statement"); evnt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (evt.Name == "sql_batch_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text"); evnt.Type = WorkloadEvent.EventType.BatchCompleted; } else if (evt.Name == "attention") { object value = TryGetValue(evt, FieldType.Action, "sql_text"); try { if (value is string) { commandText = (string)value; } else if (value is byte[]) { commandText = Encoding.Unicode.GetString((byte[])value); } else { throw new ArgumentException("Argument is of the wrong type"); } } catch (Exception e) { logger.Error(e, $"Unable to extract sql_text from attention event. Value is of type ${value.GetType().FullName}"); } evnt.Type = WorkloadEvent.EventType.Timeout; } else if (evt.Name == "user_event") { int num = (int)TryGetValue(evt, FieldType.Field, "event_id"); if (num == 83) { commandText = (string)TryGetValue(evt, FieldType.Field, "user_data"); evnt.Type = WorkloadEvent.EventType.Error; } } else { evnt.Type = WorkloadEvent.EventType.Unknown; continue; } try { evnt.ApplicationName = (string)TryGetValue(evt, FieldType.Action, "client_app_name"); evnt.DatabaseName = (string)TryGetValue(evt, FieldType.Action, "database_name"); evnt.HostName = (string)TryGetValue(evt, FieldType.Action, "client_hostname"); evnt.LoginName = (string)TryGetValue(evt, FieldType.Action, "server_principal_name"); object oSession = TryGetValue(evt, FieldType.Action, "session_id"); if (oSession != null) { evnt.SPID = Convert.ToInt32(oSession); } if (commandText != null) { evnt.Text = commandText; } evnt.StartTime = evt.Timestamp.LocalDateTime; if (evnt.Type == WorkloadEvent.EventType.Error) { // do nothing } else if (evnt.Type == WorkloadEvent.EventType.Timeout) { evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); evnt.CPU = Convert.ToInt64(evnt.Duration); } else { evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value); evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value); evnt.CPU = Convert.ToInt64(evt.Fields["cpu_time"].Value); evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); } } catch (Exception e) { logger.Error(e, "Error converting XE data from the stream."); throw; } if (evnt.Type <= WorkloadEvent.EventType.BatchCompleted) { if (transformer.Skip(evnt.Text)) { continue; } evnt.Text = transformer.Transform(evnt.Text); } Events.Enqueue(evnt); EventCount++; } } }
private void ReadEventsFromStream() { try { QueryableXEventData eventstream = new QueryableXEventData( ConnectionInfo.ConnectionString, "sqlworkload", EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache); int rowsRead = 0; SqlTransformer transformer = new SqlTransformer(); foreach (PublishedEvent evt in eventstream) { ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent(); string commandText = String.Empty; if (evt.Name == "rpc_completed") { commandText = evt.Fields["statement"].Value.ToString(); evnt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (evt.Name == "sql_batch_completed") { commandText = evt.Fields["batch_text"].Value.ToString(); evnt.Type = WorkloadEvent.EventType.BatchCompleted; } else if (evt.Name == "attention") { commandText = evt.Actions["sql_text"].Value.ToString(); evnt.Type = WorkloadEvent.EventType.Timeout; } else { evnt.Type = WorkloadEvent.EventType.Unknown; continue; } if (evt.Actions["client_app_name"].Value != null) { evnt.ApplicationName = (string)evt.Actions["client_app_name"].Value; } if (evt.Actions["database_name"].Value != null) { evnt.DatabaseName = (string)evt.Actions["database_name"].Value; } if (evt.Actions["client_hostname"].Value != null) { evnt.HostName = (string)evt.Actions["client_hostname"].Value; } if (evt.Actions["server_principal_name"].Value != null) { evnt.LoginName = (string)evt.Actions["server_principal_name"].Value; } if (evt.Actions["session_id"].Value != null) { evnt.SPID = Convert.ToInt32(evt.Actions["session_id"].Value); } if (commandText != null) { evnt.Text = commandText; } evnt.StartTime = evt.Timestamp.LocalDateTime; if (evnt.Type == WorkloadEvent.EventType.Timeout) { evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); evnt.CPU = Convert.ToInt32(evnt.Duration / 1000); } else { evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value); evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value); evnt.CPU = Convert.ToInt32(evt.Fields["cpu_time"].Value); evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); } if (transformer.Skip(evnt.Text)) { continue; } evnt.Text = transformer.Transform(evnt.Text); Events.Enqueue(evnt); rowsRead++; } } catch (Exception ex) { logger.Error(ex.Message); logger.Error(ex.StackTrace); if (ex.InnerException != null) { logger.Error(ex.InnerException.Message); } Dispose(); } }
public override void ReadEvents() { EventCount = 0; SqlTransformer transformer = new SqlTransformer(); using (QueryableXEventData eventstream = new QueryableXEventData( ConnectionString, SessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.CacheToDisk)) { var eventsEnumerator = eventstream.GetEnumerator(); while (!stopped && eventsEnumerator.MoveNext()) { PublishedEvent evt = eventsEnumerator.Current; ExecutionWorkloadEvent workloadEvent = new ExecutionWorkloadEvent(); try { workloadEvent.EventSequence = Convert.ToInt64(TryGetValue(evt, FieldType.Action, "event_sequence")); string commandText = String.Empty; if (evt.Name == "rpc_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "statement"); workloadEvent.Type = WorkloadEvent.EventType.RPCCompleted; } else if (evt.Name == "sql_batch_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text"); workloadEvent.Type = WorkloadEvent.EventType.BatchCompleted; } else if (evt.Name == "attention") { workloadEvent = new ErrorWorkloadEvent(); object value = TryGetValue(evt, FieldType.Action, "sql_text"); if (value == null) { continue; } try { if (value is string) { commandText = (string)value; } else if (value is byte[]) { commandText = Encoding.Unicode.GetString((byte[])value); } else { throw new ArgumentException("Argument is of the wrong type"); } } catch (Exception e) { logger.Error(e, $"Unable to extract sql_text from attention event. Value is of type ${value.GetType().FullName}"); } workloadEvent.Text = commandText; workloadEvent.Type = WorkloadEvent.EventType.Timeout; } else if (evt.Name == "user_event") { workloadEvent = new ErrorWorkloadEvent(); int num = (int)TryGetValue(evt, FieldType.Field, "event_id"); if (num == 83 || num == 82) { if (TryGetString(evt, FieldType.Field, "user_info").StartsWith("WorkloadTools.")) { commandText = TryGetString(evt, FieldType.Field, "user_data"); workloadEvent.Text = commandText; if (num == 83) { workloadEvent.Type = WorkloadEvent.EventType.Error; } else { workloadEvent.Type = WorkloadEvent.EventType.Timeout; } } else { workloadEvent.Type = WorkloadEvent.EventType.Unknown; continue; } } } else { workloadEvent.Type = WorkloadEvent.EventType.Unknown; continue; } try { workloadEvent.ApplicationName = TryGetString(evt, FieldType.Action, "client_app_name"); workloadEvent.DatabaseName = TryGetString(evt, FieldType.Action, "database_name"); workloadEvent.HostName = TryGetString(evt, FieldType.Action, "client_hostname"); workloadEvent.LoginName = TryGetString(evt, FieldType.Action, "server_principal_name"); workloadEvent.SPID = TryGetInt32(evt, FieldType.Action, "session_id"); if (commandText != null) { workloadEvent.Text = commandText; } workloadEvent.StartTime = evt.Timestamp.LocalDateTime; if (workloadEvent.Type == WorkloadEvent.EventType.Error) { workloadEvent.Duration = 0; workloadEvent.CPU = 0; } else if (workloadEvent.Type == WorkloadEvent.EventType.Timeout) { workloadEvent.Duration = TryGetInt64(evt, FieldType.Field, "duration"); workloadEvent.CPU = Convert.ToInt64(workloadEvent.Duration); } else { workloadEvent.Reads = TryGetInt64(evt, FieldType.Field, "logical_reads"); workloadEvent.Writes = TryGetInt64(evt, FieldType.Field, "writes"); workloadEvent.CPU = TryGetInt64(evt, FieldType.Field, "cpu_time"); workloadEvent.Duration = TryGetInt64(evt, FieldType.Field, "duration"); } } catch (Exception e) { logger.Error(e, "Error converting XE data from the stream."); throw; } if (workloadEvent.Type <= WorkloadEvent.EventType.BatchCompleted) { if (transformer.Skip(workloadEvent.Text)) { continue; } workloadEvent.Text = transformer.Transform(workloadEvent.Text); } Events.Enqueue(workloadEvent); EventCount++; } catch (Exception ex) { logger.Error($"Error converting XE data from the stream: {ex.Message}"); try { logger.Error($" event type : {workloadEvent.Type}"); logger.Error($" client_app_name : {TryGetString(evt, FieldType.Action, "client_app_name")}"); logger.Error($" database_name : {TryGetString(evt, FieldType.Action, "database_name")}"); logger.Error($" client_hostname : {TryGetString(evt, FieldType.Action, "client_hostname")}"); logger.Error($" server_principal_name : {TryGetString(evt, FieldType.Action, "server_principal_name")}"); logger.Error($" session_id : {TryGetString(evt, FieldType.Action, "session_id")}"); logger.Error($" duration : {TryGetString(evt, FieldType.Field, "duration")}"); logger.Error($" logical_reads : {TryGetString(evt, FieldType.Field, "logical_reads")}"); logger.Error($" writes : {TryGetString(evt, FieldType.Field, "writes")}"); logger.Error($" cpu_time : {TryGetString(evt, FieldType.Field, "cpu_time")}"); } catch (Exception) { //ignore, it is only logging } throw; } } } }
public void ExtractMetadataFromFile(QueryableXEventData in_event_file) { Console.WriteLine("Thread {0} : Evaluating the metadata of the extended event file and building data tables", Thread.CurrentThread.ManagedThreadId); // initialize the max lengths of string, xml and binary columns GetMaxPossibleColumn(); // read the metadata from the QueryableXEventData foreach (IMetadataGeneration mgen in in_event_file.EventProvider.MetadataGenerations) { // collection to store all actions from each package DataTable dt_Actions = new DataTable("Actions"); DataColumnCollection dc_col_actions = dt_Actions.Columns; //iterate through each package to extract actions foreach (IPackage xe_package in mgen.Packages) { //iterate through the actions foreach (IActionMetadata xe_action in xe_package.Actions) { switch (xe_action.Name) { case "debug_break": case "create_dump_single_thread": case "create_dump_all_threads": { // some actions do not have any valid information in the extended event data file to be processed, so ignore them break; } case "query_hash": case "query_plan_hash": { // actions that capture hash values for query execution have some data type mismatch issues, so we create another parallel column with binary data dc_col_actions.Add("a_" + xe_action.Name, GetDataTableColumnType(xe_action.Type.ToString(), xe_action.Name)); dc_col_actions.Add("a_" + xe_action.Name + "_bin", System.Type.GetType("System.Byte[]")); break; } case "callstack": { // actions that capture hash values for query execution have some data type mismatch issues, so we create another parallel column with binary data dc_col_actions.Add("a_" + xe_action.Name, GetDataTableColumnType(xe_action.Type.ToString(), xe_action.Name)); dc_col_actions.Add("a_" + xe_action.Name + "_debugcmd", System.Type.GetType("System.String")); break; } default: { // each action present in this package needs to be added as a column, All actions will have a_ prefix for the column names dc_col_actions.Add("a_" + xe_action.Name, GetDataTableColumnType(xe_action.Type.ToString(), xe_action.Name)); break; } } } } // iterate through each package to extract events foreach (IPackage xe_package in mgen.Packages) { //iterate through each event foreach (IEventMetadata xe_event in xe_package.Events) { //create one datatable per event DataTable dt_event = new DataTable(xe_event.Name); DataColumnCollection dc_col_fields = dt_event.Columns; // Add a mandatory column to indicate which file this event is coming from DataColumn file_id_column; file_id_column = new DataColumn("e_Imported_File_Id", System.Type.GetType("System.Int64")); dt_event.Columns.Add(file_id_column); // Add a mandatory column to capture the time at which event occured: Time_Of_Event DataColumn time_column; time_column = new DataColumn("e_Time_Of_Event", System.Type.GetType("System.DateTime")); dt_event.Columns.Add(time_column); // Add a mandatory column to capture the time at which event occured: Time_Of_Event_utc DataColumn time_column_utc; time_column_utc = new DataColumn("e_Time_Of_Event_utc", System.Type.GetType("System.DateTime")); dt_event.Columns.Add(time_column_utc); // Add a mandatory column to capture the time at which event occured: Time_Of_Event_local DataColumn time_column_local; time_column_local = new DataColumn("e_Time_Of_Event_local", System.Type.GetType("System.DateTime")); dt_event.Columns.Add(time_column_local); //iterate through the fields foreach (IEventFieldMetadata xe_field in xe_event.Fields) { //each field for this event needs to be added as a column, All data columns in a event will have c_ prefix added dc_col_fields.Add("c_" + xe_field.Name, GetDataTableColumnType(xe_field.Type.ToString(), xe_field.Name)); } //now add all the actions to the event datatable foreach (DataColumn dc_Action in dc_col_actions) { dt_event.Columns.Add(dc_Action.ColumnName, dc_Action.DataType); } //now add this data table to the global collection m_event_tables.Add(dt_event); //increment table count in the status tracker XELoader.FileProcessor.myTrackStatus.m_Number_of_Tables++; // create the table in the target SQL database for this event // we will do this only if the mode is not append only if (false == XELoader.FileProcessor.myTrackStatus.IsTableCreated) { CreateTableInSQLdatabase(dt_event); } } } } Console.WriteLine("Thread {0} : Finished metadata work, now ready to start processing and load data", Thread.CurrentThread.ManagedThreadId); }
public void StartEventStreamCollection() { if (Monitor.TryEnter(_streamLocker)) { if (CreateXEventStreamOnTarget()) { try { if (streamEvent != null) { while (streamEvent != null && !_StopRequested) { try { using (_xEventDataStream = new QueryableXEventData(_xEventConnectionString, _sessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache)) { foreach (PublishedEvent _xEvent in _xEventDataStream) { string server_name = ""; string event_timestamp = ""; string event_name = ""; Int64 session_id = 0; Int64 transaction_id = 0; string database_name = ""; string nt_username = ""; string object_name = ""; Int64 duration = 0; StringBuilder statement = new StringBuilder(); StringBuilder xml_report = new StringBuilder(); string client_hostname = ""; string application_name = ""; Int64 cpu_time = 0; Int64 physical_reads = 0; Int64 logical_reads = 0; Int64 writes = 0; Int64 row_count = 0; string causality_guid = ""; int causality_seq = 0; int nest_level = 0; string wait_type = ""; string wait_resource = ""; string resource_owner_type = ""; string lock_mode = ""; //server_name = ""; //event_timestamp = ""; //event_name = ""; //session_id = 0; //transaction_id = 0; //database_name = ""; //nt_username = ""; //object_name = ""; //duration = 0; //client_hostname = ""; //application_name = ""; //cpu_time = 0; //physical_reads = 0; //logical_reads = 0; //writes = 0; //row_count = 0; //causality_guid = ""; //causality_seq = 0; //nest_level = 0; //wait_type = ""; //wait_resource = ""; //resource_owner_type = ""; //lock_mode = ""; PublishedEventField ef; event_name = _xEvent.Name; if (_xEvent.Fields.TryGetValue("object_name", out ef)) { object_name = ef.Value.ToString(); } if (_xEvent.Fields.TryGetValue("duration", out ef)) { duration = Convert.ToInt64(ef.Value.ToString()); } if (_xEvent.Fields.TryGetValue("statement", out ef)) { lock (_statementTextLocker) { statement.Append(ef.Value.ToString()); } //statement = ef.Value.ToString(); } if (_xEvent.Fields.TryGetValue("cpu_time", out ef)) { cpu_time = Convert.ToInt64(ef.Value.ToString()); } if (_xEvent.Fields.TryGetValue("physical_reads", out ef)) { physical_reads = Convert.ToInt64(ef.Value.ToString()); } if (_xEvent.Fields.TryGetValue("logical_reads", out ef)) { logical_reads = Convert.ToInt64(ef.Value.ToString()); } if (_xEvent.Fields.TryGetValue("writes", out ef)) { writes = Convert.ToInt64(ef.Value.ToString()); } if (_xEvent.Fields.TryGetValue("row_count", out ef)) { row_count = Convert.ToInt64(ef.Value.ToString()); } if (_xEvent.Fields.TryGetValue("xml_report", out ef)) { lock (_xmlTextLocker) { xml_report.Append(ef.Value.ToString()); } } if (_xEvent.Fields.TryGetValue("nest_level", out ef)) { nest_level = Convert.ToInt32(ef.Value.ToString()); } if (_xEvent.Fields.TryGetValue("wait_type", out ef)) { wait_type = ef.Value.ToString(); } if (_xEvent.Fields.TryGetValue("wait_resource", out ef)) { wait_resource = ef.Value.ToString(); } if (_xEvent.Fields.TryGetValue("blocked_process", out ef)) { lock (_xmlTextLocker) { xml_report.Append(ef.Value.ToString()); } } if (_xEvent.Fields.TryGetValue("resource_owner_type", out ef)) { resource_owner_type = ef.Value.ToString(); } if (_xEvent.Fields.TryGetValue("lock_mode", out ef)) { lock_mode = ef.Value.ToString(); } PublishedAction ss; if (_xEvent.Actions.TryGetValue("collect_system_time", out ss)) { event_timestamp = ((DateTimeOffset)ss.Value).ToString("yyyy-MM-dd HH:mm:ss.fffff"); } if (_xEvent.Actions.TryGetValue("session_id", out ss)) { session_id = Convert.ToInt64(ss.Value.ToString()); } if (_xEvent.Actions.TryGetValue("transaction_id", out ss)) { transaction_id = Convert.ToInt64(ss.Value.ToString()); } if (_xEvent.Actions.TryGetValue("database_name", out ss)) { database_name = ss.Value.ToString(); } if (_xEvent.Actions.TryGetValue("nt_username", out ss)) { nt_username = ss.Value.ToString(); } if (_xEvent.Actions.TryGetValue("server_instance_name", out ss)) { server_name = ss.Value.ToString(); } if (_xEvent.Actions.TryGetValue("client_hostname", out ss)) { client_hostname = ss.Value.ToString(); } if (_xEvent.Actions.TryGetValue("client_app_name", out ss)) { application_name = ss.Value.ToString(); } if (_xEvent.Actions.TryGetValue("sql_text", out ss)) { lock (_statementTextLocker) { statement.Append(_xEvent.Actions["sql_text"].Value.ToString()); } //statement = _xEvent.Actions["sql_text"].Value.ToString(); } if (_xEvent.Actions.TryGetValue("attach_activity_id", out ss)) { causality_guid = _xEvent.Actions["attach_activity_id"].Value.ToString(); causality_seq = Convert.ToInt32(_xEvent.Actions["attach_activity_id"].Value.ToString().Split('[')[1].ToString().Replace("]", "")); } if (_xEvent.Actions.TryGetValue("attach_activity_id_seq", out ss)) { causality_seq = Convert.ToInt32(_xEvent.Actions["attach_activity_id_seq"].Value.ToString()); } if (_xEvent.Actions.TryGetValue("wait_resource", out ss)) { wait_resource = ss.Value.ToString(); } _CapturesInTheLastSecond++; /* increase the capture count for this session */ /* TODO: exclusions are to be generated to go in here */ if (/*object_name != "InsertXETraceEvent" && object_name != "sp_reset_connection" * &&*/( (_CapturesInTheLastSecond < 10000 && event_name.Contains("wait")) || (_CapturesInTheLastSecond > 10000 && !event_name.Contains("wait")) || _CapturesInTheLastSecond < 10000 ) ) { _ValidCapturesInTheLastSecond++; /* XEvent Collected */ streamEvent(new XEventItem() { server_name = _serverName , event_name = event_name , event_timestamp = event_timestamp , session_id = session_id , transaction_id = transaction_id , database_name = database_name , nt_username = nt_username , object_name = object_name , duration = duration , statement = statement , xml_report = xml_report , client_hostname = client_hostname , application_name = application_name , cpu_time = cpu_time , physical_reads = physical_reads , logical_reads = logical_reads , writes = writes , row_count = row_count , causality_guid = causality_guid , causality_seq = causality_seq , nest_level = nest_level , wait_type = wait_type , wait_resource = wait_resource , resource_owner_type = resource_owner_type , lock_mode = lock_mode }, null); } } } } catch (Exception xEx) { if (!_StopRequested) { Console.WriteLine("Inner xerror:: " + xEx.ToString()); if (xEx.ToString().Contains("The server was not found or was not accessible")) { streamEvent = null; /* force exit */ Console.WriteLine("Exit Forced!"); } } else { Console.WriteLine("Stop requested :: server: " + _serverName); } } } } } finally { Monitor.Exit(_streamLocker); } } } }
static void ProcessOneFile(String in_file_to_process) { FileInfo current_File = new FileInfo(in_file_to_process); String current_File_Pattern = GetLeadingFilePattern(current_File.Name); // did the user request a specific pattern if ("*.xel" != myInputParameters.m_XEL_File_Pattern) { String pattern = "*.xel"; int position = myInputParameters.m_XEL_File_Pattern.LastIndexOf(pattern); String requested_leading_pattern = myInputParameters.m_XEL_File_Pattern.Substring(0, position); String current_leading_pattern = current_File_Pattern.Substring(0, requested_leading_pattern.Length); if (current_leading_pattern != requested_leading_pattern) { Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine("Thread {0} : Skipping file since pattern [{1}] did not match : {2} [Overide with -p for alternate pattern]", Thread.CurrentThread.ManagedThreadId, requested_leading_pattern, current_File.Name); Console.ResetColor(); return; } } // check if this file belongs to the same XE session or something else else if (current_File_Pattern != myTrackStatus.m_filePatternInUse) { Console.ForegroundColor = ConsoleColor.Yellow; Console.WriteLine("Thread {0} : Skipping file since pattern [{1}] did not match : {2} [Overide with -p for alternate pattern]", Thread.CurrentThread.ManagedThreadId, myTrackStatus.m_filePatternInUse, current_File.Name); Console.ResetColor(); return; } // report the pattern match status Console.ForegroundColor = ConsoleColor.Cyan; Console.WriteLine("Thread {0} : Requested pattern [{1}] matches file name : {2}", Thread.CurrentThread.ManagedThreadId, myTrackStatus.m_filePatternInUse, current_File.Name); Console.ResetColor(); EventHolder x_event_holder = null; try { //increment file count in the tracker myTrackStatus.m_Number_of_Files++; // call read ahead on this file to speed up the XE Linq reader if ("y" == myInputParameters.m_ReadAhead) { ReadAhead x_ReadAhead = new ReadAhead(in_file_to_process); Thread x_Thread = new Thread(new ThreadStart(x_ReadAhead.DoReadAhead)); x_Thread.Start(); } //check if the file exists if (File.Exists(in_file_to_process)) { Console.ForegroundColor = ConsoleColor.Cyan; Console.WriteLine("Thread {0} : Start processing file : {1}", Thread.CurrentThread.ManagedThreadId, current_File.Name); Console.ResetColor(); QueryableXEventData x_event_file; //open the XEL file try { x_event_file = new QueryableXEventData(in_file_to_process); } catch (Exception e) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("Thread {0} : *** Exception encountered calling QueryableXEventData in ProcessOneFile *** ", Thread.CurrentThread.ManagedThreadId); Console.WriteLine("Thread {0} : Exception message : {1} ", Thread.CurrentThread.ManagedThreadId, e.Message); Console.WriteLine("Thread {0} : Exception code : {1} ", Thread.CurrentThread.ManagedThreadId, System.Runtime.InteropServices.Marshal.GetHRForException(e)); //Using e.HResult gives CS0122 in new versions of .NET Console.WriteLine("Thread {0} : Exception stack : {1} ", Thread.CurrentThread.ManagedThreadId, e.StackTrace); Console.ResetColor(); return; } // process the metadata from the XEL file and add information to the data tables // need to do this only one time, so need to sync with other file processors myTrackStatus.acquire_metadata_Lock(true); { if (false == myEventMetadata.m_dt_Initialized) { myEventMetadata.ExtractMetadataFromFile(x_event_file); myEventMetadata.m_dt_Initialized = true; myTrackStatus.IsTableCreated = true; } } myTrackStatus.release_metadata_Lock(true); x_event_holder = new EventHolder(in_file_to_process); // save information about this file into the tracking table // get the identity value back to use as default value in the data table x_event_holder.m_fileID = x_event_holder.InsertFileInfoIntoTrackingTable(); DateTime start_Time = DateTime.Now; // now loop through each event in the file, extract information and store it in the data table Console.WriteLine("Thread {0} : Extracting events from the extended event file : {1}", Thread.CurrentThread.ManagedThreadId, current_File.Name); foreach (PublishedEvent x_event in x_event_file) { x_event_holder.ProcessEvent(x_event); } //foreach loop // Now we can commit left over in the data tables to the database x_event_holder.SaveAllDataTablesToSQLDatabase(); x_event_holder.ClearAllDataTables(); DateTime end_Time = DateTime.Now; x_event_holder.m_Total_Processing_Duration = end_Time - start_Time; // update status to console Console.ForegroundColor = ConsoleColor.Cyan; Console.WriteLine("Thread {0} : Finished processing file {1} , duration : {2} , bulk copy duration : {3} ", Thread.CurrentThread.ManagedThreadId, x_event_holder.m_event_File_Info.Name, x_event_holder.m_Total_Processing_Duration.ToString(), x_event_holder.m_Bulk_Copy_Duration.ToString()); Console.ResetColor(); // save runtime for each file in the backend table x_event_holder.UpdateFileInfoInTrackingTable(); // increment global error and truncation counts from this file if (myTrackStatus.m_Number_of_Errors > 0 || myTrackStatus.m_Number_of_StringTruncations > 0 || myTrackStatus.m_Number_of_XMLTruncations > 0 || myTrackStatus.m_Number_of_BinaryTruncations > 0) { myTrackStatus.acquire_error_truncation_Lock(true); { myTrackStatus.m_Number_of_Errors += x_event_holder.m_Number_of_Errors; myTrackStatus.m_Number_of_StringTruncations += x_event_holder.m_Number_of_String_Truncations; myTrackStatus.m_Number_of_XMLTruncations += x_event_holder.m_Number_of_XML_Truncations; myTrackStatus.m_Number_of_BinaryTruncations += x_event_holder.m_Number_of_Binary_Truncations; } myTrackStatus.release_error_truncation_Lock(true); } } } catch (Exception e) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("Thread {0} : *** Exception encountered *** ", Thread.CurrentThread.ManagedThreadId); Console.WriteLine("Thread {0} : Stop processing file : {1} ", Thread.CurrentThread.ManagedThreadId, current_File.Name); Console.WriteLine("Thread {0} : Exception message : {1} ", Thread.CurrentThread.ManagedThreadId, e.Message); Console.WriteLine("Thread {0} : Exception code : {1} ", Thread.CurrentThread.ManagedThreadId, System.Runtime.InteropServices.Marshal.GetHRForException(e)); //just using e.HResult gives CS0122 in new versions of .NET Console.WriteLine("Thread {0} : Exception stack : {1} ", Thread.CurrentThread.ManagedThreadId, e.StackTrace); Console.ResetColor(); // increment global error and truncation counts from this file myTrackStatus.acquire_error_truncation_Lock(true); { myTrackStatus.m_Number_of_Errors += x_event_holder.m_Number_of_Errors; myTrackStatus.m_Number_of_StringTruncations += x_event_holder.m_Number_of_String_Truncations; myTrackStatus.m_Number_of_XMLTruncations += x_event_holder.m_Number_of_XML_Truncations; myTrackStatus.m_Number_of_BinaryTruncations += x_event_holder.m_Number_of_Binary_Truncations; } myTrackStatus.release_error_truncation_Lock(true); } }
public static async Task Spy(Action <ShowplanEvent> eventFunc, SpyOptions options, CancellationToken token) { await Task.Run(async() => { var sessionName = $"{XePlanNamePrefix}-{Environment.MachineName}-{Guid.NewGuid()}"; var connectionString = ConnectionStringBuilder.Build( "master", options.Server, options.UserName, options.Password); var store = new XEStore(new SqlStoreConnection(new SqlConnection(connectionString))); if (options.CleanOnStart) { CleanUpExistingSessions(store); } var existingCount = store.Sessions.Count(s => s.Name.StartsWith(XePlanNamePrefix)); if (existingCount > 0) { Log.Logger.Warning("Found {count} existing plans that might need cleanup. Run with -c option to remove them.", existingCount); } var filterExpression = FilterBuilder.Build(options.Database, options.AppName); var session = store.CreateSession(sessionName); session.MaxDispatchLatency = 1; session.AutoStart = false; var showPlanEvent = session.AddEvent("sqlserver.query_post_execution_showplan"); showPlanEvent.PredicateExpression = filterExpression; showPlanEvent.AddAction("sqlserver.sql_text"); showPlanEvent.AddAction("sqlserver.query_hash"); showPlanEvent.AddAction("sqlserver.query_plan_hash"); showPlanEvent.AddAction("sqlserver.plan_handle"); try { Log.Logger.Verbose("Creating new session {session}", session.Name); session.Create(); Log.Logger.Verbose("Starting new session {session}", session.Name); session.Start(); } catch (Exception e) { Log.Logger.Fatal("Unable to create monitoring session", e); throw; } Log.Logger.Verbose("Session {session} started", session.Name); // if the task gets canceled then we need to break out of the loop and clean up the session // we're checking in the loop for the cancellation but that will only hit if an event is triggered // so this lets us be a bit more aggressive about quitting and cleaning up after ourselves token.Register(() => SessionCleanup(session)); try { using (var eventStream = new QueryableXEventData(connectionString, sessionName, EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache)) { Log.Logger.Verbose("Watching new session"); foreach (var evt in eventStream) { if (token.IsCancellationRequested) { Log.Logger.Verbose("Cancelling sql spy task"); break; } try { var sqlEvent = new ShowplanEvent { ShowplanXml = (XMLData)evt.Fields["showplan_xml"].Value, Duration = (ulong)evt.Fields["duration"].Value, EstimatedCost = (int)evt.Fields["estimated_cost"].Value, EstimatedRows = (int)evt.Fields["estimated_rows"].Value, QueryHash = (ulong)evt.Actions["query_hash"].Value, QueryPlanHash = (ulong)evt.Actions["query_plan_hash"].Value, SqlStatement = (string)evt.Actions["sql_text"].Value, PlanHandle = (byte[])evt.Actions["plan_handle"].Value }; eventFunc(sqlEvent); } catch (Exception e) { Log.Logger.Error("Error creating event", e); } } } } catch (Exception e) { if (!token.IsCancellationRequested) { Log.Logger.Error("Unknown error while monitoring events", e); throw; } } await Task.Delay(0, token); }, token); }
public void ReadEvents() { EventCount = 0; SqlTransformer transformer = new SqlTransformer(); using (QueryableXEventData eventstream = new QueryableXEventData(Path)) { var eventsEnumerator = eventstream.GetEnumerator(); while (!stopped && eventsEnumerator.MoveNext()) { PublishedEvent evt = eventsEnumerator.Current; ExecutionWorkloadEvent evnt = new ExecutionWorkloadEvent(); string commandText = String.Empty; if (evt.Name == "rpc_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "statement"); evnt.Type = WorkloadEvent.EventType.RPCCompleted; } else if (evt.Name == "sql_batch_completed") { commandText = (string)TryGetValue(evt, FieldType.Field, "batch_text"); evnt.Type = WorkloadEvent.EventType.BatchCompleted; } else if (evt.Name == "attention") { commandText = (string)TryGetValue(evt, FieldType.Action, "sql_text"); evnt.Type = WorkloadEvent.EventType.Timeout; } else { evnt.Type = WorkloadEvent.EventType.Unknown; continue; } try { evnt.ApplicationName = (string)TryGetValue(evt, FieldType.Action, "client_app_name"); evnt.DatabaseName = (string)TryGetValue(evt, FieldType.Action, "database_name"); evnt.HostName = (string)TryGetValue(evt, FieldType.Action, "client_hostname"); evnt.LoginName = (string)TryGetValue(evt, FieldType.Action, "server_principal_name"); object oSession = TryGetValue(evt, FieldType.Action, "session_id"); if (oSession != null) { evnt.SPID = Convert.ToInt32(oSession); } if (commandText != null) { evnt.Text = commandText; } evnt.StartTime = evt.Timestamp.LocalDateTime; if (evnt.Type == WorkloadEvent.EventType.Timeout) { evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); evnt.CPU = Convert.ToInt64(evnt.Duration); } else { evnt.Reads = Convert.ToInt64(evt.Fields["logical_reads"].Value); evnt.Writes = Convert.ToInt64(evt.Fields["writes"].Value); evnt.CPU = Convert.ToInt64(evt.Fields["cpu_time"].Value); evnt.Duration = Convert.ToInt64(evt.Fields["duration"].Value); } } catch (Exception e) { logger.Error(e, "Error converting XE data from the stream."); throw; } if (transformer.Skip(evnt.Text)) { continue; } evnt.Text = transformer.Transform(evnt.Text); Events.Enqueue(evnt); EventCount++; } finished = true; } }