SQL Server - Auditing sessions over time - Code example

So today I had a requirement to provide a quick and dirty way of collecting data on the sessions connected to SQL Server.  Specifically, I had to write a job that collected basic data on the current sessions, and if these sessions had not been previously recorded, record them into a table.  If the session was identical in all ways other than the session ID, then a counter called 'session count' is incremented for that session.

The challenging part here was providing what is basically a mashup of an INSERT and UPDATE statement, sometimes called an UPSERT.  In SQL Server 2008 and later versions, this functionality is provided using the MERGE statement.  My requirement here is to update a table with values if they exist; otherwise, insert them.  

So here's an example of how to use this MERGE statement in the context of my requirement. The example code is below.  You can modify it to suit your purpose - for example, write out to a permanent table, put it in a job, query the table in a different way, monitor requests rather than sessions, etc. or re-write it entirely, or simply use it as an example of MERGE for reference.

There's also a self-join in there which provides the columns on which to match.  This isn't strictly essential but I needed to match on columns then only update the last login time and last request end time if matched.    The outer query simply aggregates the results in the target table to provide a good view of the data.

Note this won't collect data on sessions which are opened and closed between running the collection.  So if you modify this code by e.g. using a permanent table and putting this code into a job (can't use a temp table in this context), run the job frequently.  If you run it e.g. every 15 minutes and a session is created and destroyed between the job execution intervals, you won't capture the data.

 -- ---------------------------------------------------------------------------------------------  
 -- Script to audit sessions on the server, accumulate session counts per distinct group of   
 -- values from sys.dm_exec_sessions, will ignore duplicate sessions (by session_id and unique   
 -- set of values), will increment when a new session is started that has the key values   
 -- matching, will insert when a brand new session is opened. Uses MERGE.  
   
 -- Run inside a stored procedure, or on the query window, multiple times over the course of X   
 -- hours to get a full list of sessions.  
 -- Add filters if necessary on the final SELECT.   
 -- Change #Logins to a permanent table if required.  
   
 -- Works only in SQL Server 2008, R2, 2012 and 2014.  
   
 -- Author: Derek Colley, 13/12/2013  
 -- ---------------------------------------------------------------------------------------------  
   
 -- TRUNCATE TABLE #Logins -- for debug  
   
 IF NOT EXISTS ( SELECT name FROM tempdb.sys.tables WHERE name LIKE ('#Logins%') )  
     CREATE TABLE #Logins (   
         uqid INT IDENTITY(1,1),   
         session_id INT,   
         last_login_time DATETIME,   
         host_name NVARCHAR(128),   
         program_name NVARCHAR(128),   
         client_interface_name NVARCHAR(32),   
         login_name NVARCHAR(128),   
         last_request_end_time DATETIME )  
   
 DECLARE @CurrentSessions TABLE (   
     session_id INT,   
     login_time DATETIME,   
     host_name NVARCHAR(128),   
     program_name NVARCHAR(128),   
     client_interface_name NVARCHAR(32),   
     login_name NVARCHAR(128),   
     last_request_end_time DATETIME )   
   
 INSERT INTO @CurrentSessions  
     SELECT        session_id, login_time, host_name, program_name, client_interface_name,   
                 login_name, last_request_end_time  
     FROM        sys.dm_exec_sessions  
     WHERE        session_id <> @@SPID   
     AND            session_id > 50  
       
 MERGE INTO #Logins   
 USING (   
         SELECT        c.session_id, c.login_time, c.host_name, c.program_name, c.client_interface_name,   
                     c.login_name , c.last_request_end_time  
         FROM        @CurrentSessions c  
         LEFT JOIN    #Logins l   
         ON            c.session_id = l.session_id   
         AND            c.host_name = l.host_name   
         AND            c.program_name = l.program_name   
         AND            c.client_interface_name = l.client_interface_name   
         AND            c.login_name = l.login_name    ) AS CurrentSessions  
     ON        #Logins.session_id = CurrentSessions.session_id   
     AND        #Logins.host_name = CurrentSessions.host_name   
     AND        #Logins.program_name = CurrentSessions.program_name   
     AND        #Logins.client_interface_name = CurrentSessions.client_interface_name   
     AND        #Logins.login_name = CurrentSessions.login_name  
 WHEN MATCHED THEN   
     UPDATE          
     SET            last_login_time = CurrentSessions.login_time,  
                 last_request_end_time = CurrentSessions.last_request_end_time  
 WHEN NOT MATCHED THEN   
     INSERT        (    session_id, last_login_time, host_name, program_name, client_interface_name,   
                     login_name, last_request_end_time )      
     VALUES        (session_id, login_time, host_name, program_name, client_interface_name,   
                 login_name, last_request_end_time );  
   
 SELECT        COUNT(*) [session_count], MAX(last_login_time) [last_login_time], host_name, program_name,   
             client_interface_name, login_name, MAX(last_request_end_time) [last_request_end_time]  
 FROM        #Logins   
 GROUP BY    host_name, program_name, client_interface_name, login_name  
 ORDER BY    COUNT(*) DESC