Skip to content

Instantly share code, notes, and snippets.

@codingoutloud
Created March 12, 2019 17:27
Show Gist options
  • Save codingoutloud/0f4a20848048f14eb49bb645a6e8d821 to your computer and use it in GitHub Desktop.
Save codingoutloud/0f4a20848048f14eb49bb645a6e8d821 to your computer and use it in GitHub Desktop.
-- Turn on Audit Logging to Blob for your Azure SQL Database. Then you can query who has logged in.
-- The example below assumes DB Server-level audit logging. Details will vary slightly for Database-level audit logging.
-- The example below shows who logged in so far today.
-- Change "-0" to "-1" to look at yesterday (from a UTC perspective, not your local timezone).
-- Change "-0" to "-100" to look at 100 days ago.
SELECT FORMATMESSAGE('%s (%s)', CAST(DATEADD(day, -0, CONVERT(date, SYSUTCDATETIME())) as varchar),
DATENAME(WEEKDAY, DATEADD(day, -0, SYSUTCDATETIME()))),
server_principal_name,
COUNT(server_principal_name) as 'Logins'
FROM sys.fn_get_audit_file(FORMATMESSAGE('https://<MYBLOB>.blob.core.windows.net/sqldbauditlogs/<MYDBSERVER>/<MYDB>/SqlDbAuditing_ServerAudit/%s/', 
CAST(DATEADD(day, -0, CONVERT(date, SYSUTCDATETIME())) as varchar)),default, default)
WHERE (event_time >= CAST(CONVERT(date, SYSUTCDATETIME()) as datetime2))AND (action_id = 'DBAS')
GROUP BY server_principal_name
HAVING COUNT(server_principal_name) > 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment