Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        /// <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);
                        }
                    }
                }
            }
        }
Ejemplo n.º 3
0
        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();
        }
Ejemplo n.º 4
0
            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();
            }
Ejemplo n.º 5
0
            public SQLWorker(string[] filelist)
            {
                filedata = new QueryableXEventData(filelist);

                t = new Thread(ReadEventData);
                t.Start();
            }
Ejemplo n.º 6
0
        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;
            }
        }
Ejemplo n.º 7
0
        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();
            }
        }
Ejemplo n.º 8
0
        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);
            }
        }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 10
0
        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));
        }
Ejemplo n.º 11
0
        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); });
            }
        }
Ejemplo n.º 12
0
        /// <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());
        }
Ejemplo n.º 13
0
        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);
        }
Ejemplo n.º 14
0
        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);
                    }
                }
            }
        }
Ejemplo n.º 15
0
 //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
Ejemplo n.º 16
0
 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}");
                     }
                 }
             }
         }
     }
 }
Ejemplo n.º 17
0
        /// <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();
        }
Ejemplo n.º 18
0
        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();
        }
Ejemplo n.º 19
0
        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);
        }
Ejemplo n.º 20
0
        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);
        }
Ejemplo n.º 21
0
        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));
        }
Ejemplo n.º 22
0
            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;
                }
            }
Ejemplo n.º 23
0
        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!");
            }
        }
Ejemplo n.º 24
0
        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;
                    }
                }
            }
        }
Ejemplo n.º 27
0
        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);
        }
Ejemplo n.º 28
0
        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);
                    }
                }
            }
        }
Ejemplo n.º 29
0
        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);
            }
        }
Ejemplo n.º 30
0
        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;
            }
        }
Ejemplo n.º 32
0
    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}");
                        }
                    }
                }

            }
        }
    }