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?
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