Azure SQL Managed Instance - a simple Extended Event to track logins

 This is a barebones Xevent (Extended Event) . I'm not using blob storage, just the existing "eh, you have a couple hundred meg worth of ring buffer you can use". But I needed to see who was using one of our dev instances. I thought we'd moved everything over to the Azure SQL DBs, since there's a ton of reasons to do so.

Here's some quick off-the-cuff reasons why I prefer Azure SQL DB to Managed Instance:

1. Spins up/down FAST.  Seconds to minutes. Have a 20gb database? Takes 2 minutes. 30gb? 3.
2. More availability! Technically more servers!
3. Serverless options! Allows you to just pay for the price of storage, not compute. Doubly useful for QA/Dev boxes that aren't always used. Also allows us to auto scaleup/down as traffic changes.
4. Works with our super-limited networking.

Yes, there are downsides. CLR, Replication, Agent, Mail, others. But the wins of using Azure SQL DB are very tangible.  < / soapbox > 

Anyhow, script:

@@

/* 

CREATE EVENT SESSION [Logins] ON SERVER 

ADD EVENT sqlserver.login(

ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_name,sqlserver.username))

ADD TARGET package0.ring_buffer(SET max_memory = 4096)  --max_event_limit threw error!

go

ALTER EVENT SESSION [Logins] 

ON SERVER 

STATE = start ;

GO*/

if object_id('tempdb..#xevent') is not null

    DROP TABLE #xevent

CREATE TABLE #xevent (target_data XML)

INSERT INTO #xevent (    target_data)

SELECT target_data

 FROM sys.dm_xe_session_targets        AS st

    INNER JOIN sys.dm_xe_sessions       AS se

         ON CAST(se.address AS BINARY(8)) = CAST(st.event_session_address AS BINARY(8))

WHERE

se.name = 'Logins'

DECLARE @count VARCHAR(10) 

SELECT @count = target_data.value('/RingBufferTarget[1]/@eventCount','varchar(10)') 

FROM #xevent --null means it's not on or not "installed"

PRINT 'eventCount = ' + @count

;with events_cte as(

SELECT 

ed.c.value('(@timestamp)[1]', 'DATETIMEOFFSET') at TIME ZONE 'Central Standard Time' AS err_timestamp,

--xevents.event_data.value('(RingBufferTarget/event/@timestamp)[1]', 'datetime2')) AS [err_timestamp],

ed.c.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS client_app_name,

ed.c.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS client_hostname,

ed.c.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id,

ed.c.value('(action[@name="database_name (Action)"]/value)[1]', 'nvarchar(max)') AS nt_username,

ed.c.value('(action[@name="username"]/value)[1]', 'nvarchar(max)') AS username

, ed.c.query('.') AS event_data  --we leave this off since it makes this run SO MUCH LONGER.

FROM #xevent a

cross apply (select CAST(target_data as XML) as event_data) AS xevents

CROSS APPLY event_data.nodes('/RingBufferTarget/event') AS ed(c)

)

SELECT *

--, events_cte.event_data

from events_cte 

order by err_timestamp desc;

@@