Source for extended-event category and channel information?

Question

The SSMS GUI for Extended Events lets you filter events by category ("errors", "execution", etc) and channel ("Analytic", "Operational", etc). How can I get the category and channel mapping for an event programmatically?

Answer 1

That information is available in the sys.dm_xe_* DMVs, but I had some trouble finding it.

Every event has a special, read-only column called "CHANNEL" in the sys.dm_xe_object_columns DMV. The column is of type "etw_channel", and its value is in the column_value column of the DMV. The column's value can be looked up in the sys.dm_xe_map_values DMV to get the channel name.

The category can be retrieved in a similar fashion, but with two wrinkles: the read-only column is called "KEYWORD", and 27 columns (in SQL Server 2014, at least) don't have that column.

The query below demonstrates how to get the information. In particular, it returns the same information you see in the GUI when first choosing events. The events are ordered by name, events from the "Debug" channel are filtered out, and 27 of them have a NULL category.

SELECT
        dxo.name,
        'Category' = camv.map_value,
        'Channel' = chmv.map_value
    FROM sys.dm_xe_objects AS dxo
    LEFT JOIN sys.dm_xe_object_columns AS cac ON 1=1
        AND cac.object_package_guid = dxo.package_guid
        AND cac.object_name = dxo.name
        AND cac.name = 'KEYWORD'
    LEFT JOIN sys.dm_xe_map_values AS camv ON 1=1
        AND camv.object_package_guid = cac.type_package_guid
        AND camv.name = cac.type_name
        AND camv.map_key = cac.column_value
    JOIN sys.dm_xe_object_columns AS chc ON 1=1
        AND chc.object_package_guid = dxo.package_guid
        AND chc.object_name = dxo.name
        AND chc.name = 'CHANNEL'
    JOIN sys.dm_xe_map_values AS chmv ON 1=1
        AND chmv.object_package_guid = chc.type_package_guid
        AND chmv.name = chc.type_name
        AND chmv.map_key = chc.column_value
    WHERE 1=1
        AND dxo.object_type = 'event'
        AND chmv.map_value <> 'Debug'
    ORDER BY
        dxo.name