Extended Events – Events

After thrilling dives into the XEvent Core Concepts of packages, channels and objects, today we have the core concept of “Event.”

If you recall, an event is some point of interest that may or may not occur within an application. Obviously, if the event is triggered, then that point of interest has occurred.

We have also seen that events are categorized/classified via what are called channels and keywords. This classification can be a big help when trying to find groups of events that may be related.

Events

An event carries with it the payload pertinent to the occurrence of that point of interest – when the event fires. This payload would be called “data” in a more friendly manner.

Before getting into the data too much, let’s take a look at the simplest of approaches to look at the available events within SQL Server.

SELECT xo.name AS EventName, xo.description
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'event'
ORDER BY xo.name;
/* and the public events */SELECT xo.name AS EventName, xo.description
FROM sys.dm_xe_objects xo
WHERE xo.object_type = 'event'
AND (xo.capabilities_desc <> 'private'
OR xo.capabilities_desc IS NULL)
ORDER BY xo.name;

Very basic query(ies). Knowing these events, what if I now wanted to tie this back to something we have already seen? For example, what if I wanted to see which channel and keyword mapped to the event? I might try something like the following:

DECLARE @keyword VARCHAR(64) = 'errors' --'errors' --NULL for all keywords
, @channel VARCHAR(32) = NULL; --'admin' --NULL for all channels
SELECT DISTINCT 
ch.EventName
,ch.description AS EventDesciption
, ke.KeyWord AS Keyword
, xp.name AS Package
, ch.Channel AS Channel
FROM sys.dm_xe_packages xp
INNER JOIN (
SELECT oc.object_package_guid, v.map_value AS Channel
,xo.name AS EventName
,xo.description
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_map_values v
ON oc.type_name = v.name
AND oc.column_value = CONVERT(NVARCHAR,v.map_key)
INNER JOIN sys.dm_xe_objects xo
ON oc.object_package_guid = xo.package_guid
AND oc.object_name = xo.name
WHERE oc.name = 'channel'
AND xo.object_type = 'event'
AND (xo.capabilities_desc <> 'private'
OR xo.capabilities_desc IS NULL)
) ch
ON ch.object_package_guid = xp.guid
LEFT OUTER JOIN (
SELECT xoc.object_package_guid, mv.map_value AS KeyWord
,xo.name AS EventName
FROM sys.dm_xe_map_values mv
INNER JOIN sys.dm_xe_object_columns xoc
ON xoc.type_name = mv.name
AND xoc.column_value = CONVERT(NVARCHAR,mv.map_key)
AND xoc.type_package_guid = mv.object_package_guid
INNER JOIN sys.dm_xe_objects xo
ON xo.package_guid = xoc.object_package_guid
AND xo.name = xoc.object_name
WHERE mv.name = 'keyword_map'
AND xo.object_type = 'event'
) ke
ON ke.object_package_guid = xp.guid
AND ch.EventName = ke.EventName
WHERE ch.Channel = ISNULL(@channel,ch.Channel)
AND ISNULL(ke.KeyWord,'') = COALESCE(@keyword,ke.KeyWord,'')
ORDER BY  ke.KeyWord,xp.name;

Granted, in the previous article I had included the event name in the query. I just simply did not return the event name with the results. What I have added this time around though is the event description to the query. Then I am returning both the event name and description with the results.

Looking closer at the query, you will see that I have set it up in a fashion to allow targeted searching for keywords or channels or both. Or one can provide a NULL value to return all Events regardless of keyword or channel.

This gets us to a spot where we can find the events – basically. But that is one piece of the puzzle. When dealing with extended events, one also needs to understand the components of the payload within the event. In other words, there is a bit of dissecting of the event to figure out the differences in data available within an event.

Stay tuned for the next episode when we perform the event dissection and look into the anatomy of an event (e.g. the data/payload of the event).