C# how to get list of XEvent Sessions in a given database

Question

I'm trying to write some simple Extended Events management code in C#, but am fairly new to it. I am able to setup XEvent sessions in SSMS and was able to get the Linq stream from that created session in C# using this example

What I would like to do now, is to be able to query a given database for what sessions exist. I could manually query the sys.dm_xe* tables and create the mapped classes for those, but it looks like the classes already exist in the Microsoft.SqlServer.Management.XEvent namespace - so I'd hate to do a poor re-implementation if something already exists.

The specific table holding what sessions exist is sys.dm_xe_sessions.

Any example code or help is appreciated. Thanks!

`sys.dm_xe_sessions` should return active XEvent sessions. `sys.server_event_sessions` returns all XEvent sessions, including ones that are not active. Is that what you're looking for? (I'm not sure if you are looking for C# or TSQL code.) — Dave Mason, Dec 15 '16 at 21:51

score 2 · Accepted Answer · answered Dec 21 '16 at 14:59

The class to look for is XEStore in Microsoft.SqlServer.Managment.XEvent. With this you can see what extended event sessions exist as well as create new ones.

using (SqlConnection conn = new SqlConnection(connString)) {
    XEStore store = new XEStore(new SqlStoreConnection(conn));

    if (store.Sessions[sessionName] != null) {
        Console.WriteLine("dropping existing session");
        store.Sessions[sessionName].Drop();
    }

    Session s = store.CreateSession(sessionName);
    s.MaxMemory = 4096;
    s.MaxDispatchLatency = 30;
    s.EventRetentionMode = Session.EventRetentionModeEnum.AllowMultipleEventLoss;

    Event rpc = s.AddEvent("rpc_completed");
    rpc.AddAction("username");
    rpc.AddAction("database_name");
    rpc.AddAction("sql_text");
    rpc.PredicateExpression = @"sqlserver.username NOT LIKE '%testuser'";

    s.Create();
    s.Start();

    //s.Stop();
    //s.Drop();
}

C# how to get list of XEvent Sessions in a given database

1 Answers1