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