Finding the Right Path

I have a fairly large backlog of articles that are in progress or that are planned for my series about Extended Events. Despite the backlog and planned articles, every now and then something else comes up that bumps things around. This article is an example of bumping the schedule around. You can see some of the backlog and the full list of articles in the series by visiting the table of contents – here.

I bring that up for a couple of reasons. First and foremost being that the topic was recently raised as a “need” by some colleagues. The second being that I see the need and how it was lacking in coverage by anything I had already written.

What is this gaping hole in the coverage of Extended Events? To be honest, it is not a very complicated topic or very difficult gap to fill. It’s just something that has been overlooked. The gap boils down to this: how does one consistently find the correct path to the Extended Event Log file (XEL file)?

Filling the Gap

The gap I will be working to fill in this article deals with consistently finding the file path for Extended Event (XE) sessions. This gap rises due a few different things such as the ability to define a target in different manners, being able to move the logs directory, or even the fact that a target may not be added to the session (let alone a file target). These causes can all contribute to a bit of frustration and may pose as different symptoms when trying to get the file path.

One additional complication is tied to the running state of a session. For that complication, I talked briefly about it in my article about better practices. The state of the session could have an impact and could cause frustration when trying to retrieve the file path. All of these things are considerations that must be made when trying to retrieve the file path.

To find the file path, let’s start with some basics. The default path for XEL files is in the log directory of the instance. In addition to this default behavior, each session stores metadata about running sessions as well as deployed sessions that is accessible from DMVs and system catalogs.

Log Path

The first basic to tackle is the log path for the instance. I can query for the log path of the error log for the instance and rely on that as the path of my xel files. Should I choose this method, then I could execute the following query.

DECLARE @ErrorPath VARCHAR(256) = CONVERT(VARCHAR(256),SERVERPROPERTY('ErrorLogFileName'));
SELECT LEFT(@ErrorPath,
LEN(@ErrorPath) - CHARINDEX('', REVERSE(@ErrorPath)) + 1) AS ErrorPath;

Executing that query on my SQL 2014 instance produces the following results:

Now the obvious problems with this method come from the fact that relying on this data is relying upon an assumption that you have set all of your XE Sessions to use the default log path. If you have declared your sessions to use a file target and did not specify a path, then the assumption is safe. However, if you are detail oriented, you probably have been somewhat explicit in how you define your event file target. This brings us to the next topic – defining the path.

Defining The Path

While a bit of bird-walk, it is necessary to cover this topic at this juncture. This brief discussion will help to understand some of the other issues with retrieving the path consistently.

When defining the path of the event path, there is a bit of flexibility in how one can define the file to be used. You can either declare the file as just the file name, or you can define the file as the folder path along with the file name. Both methods are completely legitimate. Unfortunately, this flexibility is what causes some of the pain with retrieving the file path consistently.

Let’s take a look at two quick, and acceptable, ways to add an event file to an XE Session. I will be re-using a session from a previous article for these examples.

CREATE EVENT SESSION [AuditSelects] ON SERVER
ADD EVENT sqlserver.degree_of_parallelism (
ACTION ( sqlserver.database_id, sqlserver.database_name,
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname,
sqlserver.context_info, sqlserver.client_connection_id )
WHERE  statement_type = ( 1 ) --type 1 is a select statement
AND [sqlserver].[database_name] = N'AdventureWorks2014' 
AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
AND [sqlserver].[client_app_name] NOT LIKE 'Red Gate Software Ltd SQL Prompt%'
)
ADD TARGET package0.event_file (  SET filename = N'C:DatabaseXEAuditSelects.xel'
, max_rollover_files = ( 25 ) )
;
GO

This is a pretty standard format I use for creating my sessions. The section I want to highlight though is the set of the filename near the end of the script. I will generally define the entire path for my xel files when I create a session – just as I have done in this example. Defining this path helps me to know exactly where I am putting the session for starters. Equally as important is that this path is easier to retrieve from metadata because I have explicitly defined the path.

Take this next example of the same session but with one minor difference.

CREATE EVENT SESSION [AuditSelects] ON SERVER
ADD EVENT sqlserver.degree_of_parallelism (
ACTION ( sqlserver.database_id, sqlserver.database_name,
sqlserver.nt_username, sqlserver.sql_text, sqlserver.username,sqlserver.session_nt_username,
sqlserver.client_app_name, sqlserver.session_id, sqlserver.client_hostname,
sqlserver.context_info, sqlserver.client_connection_id )
WHERE  statement_type = ( 1 ) --type 1 is a select statement
AND [sqlserver].[database_name] = N'AdventureWorks2014' 
AND [sqlserver].[client_app_name] <> 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'
AND [sqlserver].[client_app_name] NOT LIKE 'Red Gate Software Ltd SQL Prompt%'
)
ADD TARGET package0.event_file (  SET filename = N'AuditSelects.xel'
, max_rollover_files = ( 25 ) )
;
GO

The minor difference in this example is just in how the filename was defined. It is technically accurate and acceptable to only use the filename instead of the path and filename as I did in the previous example. The problem here comes from the retrieval of the path from metadata.

Getting the Path

In the previous two examples, I showed two methods of defining the filename within a session. In the former example, this means I can more easily find the path. However, if I use the script in the beginning of the article to find my xel file paths, then I have made an inaccurate assumption. This leads to an inconsistency in how the file path is fetched. In the latter example, the assumptions concerning the default log path would be valid and could be applied here.

Since the assumptions do not prove to be consistent or accurate across the board, we need to both evaluate how the data for each would look and we would need to see how to retrieve this path more consistently. First up is looking at how the data for each of these example sessions would be stored in metadata.

SELECT sesf.*
, ses.name AS SessionName
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name LIKE 'auditselect%'

In the preceding query, I am looking at the deployed session metadata for two sessions, both of which start with “AuditSelect”. In the sys.server_event_session_fields view, there is an attribute called name that contains the value “filename”. This value will only exist if the session has an event file target defined for the session. Running the query will yield the following results (on my system).

In this result set, I can see there is one of each of the two event file definition methods I described in the previous section. In the green highlight you will see that I have the entire filepath. In the red highlight, you will only see the filename. Nothing special has been done to this data in the return as you can verify from the posted script. These are the actual stored values. So this would indeed seem like we have a bit of a problem, right?

Well, this is where we get to be a little bit imaginative and employ a script such as the following:

/* Deployed Session Metadata */DECLARE @SessionName VARCHAR(128) = 'AuditSelects';
IF EXISTS ( SELECT 1
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = @SessionName )
BEGIN
IF ( SELECT CHARINDEX('', CONVERT(VARCHAR(256),sesf.value))
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = @SessionName ) > 0
BEGIN
SELECT ses.name AS SessionName
, LEFT(CONVERT(VARCHAR(256),sesf.value),
LEN(CONVERT(VARCHAR(256),sesf.value)) - CHARINDEX('',
REVERSE(CONVERT(VARCHAR(256),sesf.value)))+1) AS XEFilePath
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = @SessionName;
END;
ELSE
BEGIN
SELECT TOP 1 cte1.SessionName
, LEFT(t2.file_name,
LEN(t2.file_name) - CHARINDEX('',
REVERSE(t2.file_name))
+ 1) AS XEFilePath
FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128), sesf.value),
'.xel', '*.xel') AS targetvalue
, ses.name AS SessionName
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = @SessionName
) cte1
CROSS APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,
NULL, NULL, NULL) t2;
END;
END;

Wow, that is considerably longer than the first example in the article. That is true! This script accounts for a few of the conditions that cause inconsistencies (but not yet all of them) in retrieving the file path for a deployed event session. The key here is to understand this will fetch the path whether you enter a complete path or not while creating the session. I also throw a check in there to confirm that the session has a file target as well as a check to ensure the session is deployed to the server.

Looking at the output, I get results that are somewhat friendly and easy to understand without any of the guesswork.

What if the session has never been started and did not have the full path declared? Well, that is one of the inconsistencies I am working on still. (Update 2019/05/17 – An updated script is coming out soon that handles this little issue.)

There is an alternative to this method as well. For the time being, this method would also be recommended in the event the session being researched happens to be one of the system sessions that is “private”. This next code chunk will show two different methods to parse the file path from running session metadata in the DMVs.

/* use query() method for running session */SELECT cte1.SessionName
,CAST(cte1.target_data.query('data(/EventFileTarget/File/@name)') AS VARCHAR(256)) AS XEFilePath
INTO #presel
FROM ( SELECT target_data = CONVERT(XML, target_data), s.name AS SessionName
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'system_health'
) cte1;
SELECT p.SessionName
, LEFT(p.XEFilePath,
LEN(p.XEFilePath) - CHARINDEX('', REVERSE(p.XEFilePath)) + 1) AS XEFilePath
FROM #presel p;
DROP TABLE #presel;
GO
/* use value() method on Running Session Metadata */SELECT FileEvent.FileTarget.value('(@name)[1]',
'varchar(256)') AS [FilePath]
,cte1.SessionName
INTO #presel
FROM ( SELECT target_data = CONVERT(XML, target_data), s.name AS SessionName
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'system_health'
) cte1
CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File')FileEvent ( FileTarget );
SELECT p.SessionName
, LEFT(p.FilePath,
LEN(p.FilePath) - CHARINDEX('', REVERSE(p.FilePath)) + 1) AS XEFilePath
FROM #presel p;
DROP TABLE #presel;
GO

Conclusion

In the article today, I have shown some of the internals to retrieving file paths for Extended Event Sessions. I dove into metadata to pull out the path for the session and discussed some concerns for some of these methods. In the end, you have a few viable options to help retrieve the file path in a more consistent fashion.

To read more about Extended Events, I recommend this series of articles.