How to capture SQL login details with Extended Events

Question : I have to capture logon information details for a specific logon on a SQL Server.   Specifically - the client_hostname, nt_username & username. What i'm looking for is a log recording a successful connection made to the server.     The event should be triggered a) when a connection is made & b)   from a connection pool. 

Answer:   SQL Server  Extended Events are your friend . This example  creates a Extended Event for a logon called "mylogon".  When a connection based on the situations you requested a) new connection or b) connection from a connection pool  

CREATE EVENT SESSION [Track_Logon] ON SERVER 
ADD EVENT sqlserver.login(
    ACTION(sqlserver.client_hostname,sqlserver.nt_username,sqlserver.username)
    WHERE ([sqlserver].[session_server_principal_name]=N'mylogon'))
ADD TARGET package0.event_file(SET filename=N'C:\temp\track_logon.xel')
GO
ALTER EVENT SESSION [Track_Logon]  
ON SERVER  
STATE = start;  

Once you've collected all this data , you will want to organise and present the data in a useful way. Keep in mind - this data may be required by someone  who does not have SQL skills. This query uses the sys.fn_xe_file_target_read_file function . 

SELECT
    [XML Data],
    [XML Data].value('(/event/action[@name=''client_hostname'']/value)[1]','varchar(max)') AS [client_hostname],
	[XML Data].value('(/event/action[@name=''username'']/value)[1]','varchar(max)') AS [username],
	[XML Data].value('(/event/action[@name=''nt_username'']/value)[1]','varchar(max)') AS [nt_username]

FROM
    (SELECT
        OBJECT_NAME              AS [Event], 
        CONVERT(XML, event_data) AS [XML Data]
    FROM
        sys.fn_xe_file_target_read_file
    ('C:\temp\track_logon_0_*.xel',NULL,NULL,NULL)) as FailedQueries

If you'd like extra information on parsing xel files read How to parse sys.fn_xe_file_target_read_file faster