SCRIPT: How to create Audit for logins?

 -------------------------------------------------------
CREATE SERVER AUDIT TrackLogins
TO FILE
(
 FILEPATH = N'C:\SQL\', -- CREATE FOLDER OR CHANGE PATH
 MAXSIZE = 0 MB,
 MAX_ROLLOVER_FILES = 2147483647,
 RESERVE_DISK_SPACE = OFF
)
WITH
(
 QUEUE_DELAY = 1000,
 ON_FAILURE = CONTINUE
)
GO
 
-------------------------------------------------------
--Exclude login
--ALTER SERVER AUDIT TrackLogins WHERE server_principal_name <> N'login_name';
--GO
 
-------------------------------------------------------
CREATE SERVER AUDIT SPECIFICATION TrackAllLogins
FOR SERVER AUDIT TrackLogins
  ADD (FAILED_LOGIN_GROUP),
  ADD (SUCCESSFUL_LOGIN_GROUP),
  ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);
GO 
 
-------------------------------------------------------
ALTER SERVER AUDIT TrackLogins
WITH (STATE = ON);
GO
 
-------------------------------------------------------
/*
For SQL 2022 you can check Audit content with this script.
Maybe in the future you will be able to check in SSMS.
*/
 
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
GO
SELECT *
INTO #temp
FROM sys.fn_get_audit_file(
    'C:\SQL\TrackLogins_137942C3-DD3C-45CA-A25B-851C2405D4F1_0_133543800050110000.sqlaudit', -- CHANGE FILE NAME
    DEFAULT,
    DEFAULT
);
 
 

SELECT session_server_principal_name, COUNT(session_server_principal_name) AS [Count] FROM #temp
WHERE
succeeded = 1
AND session_server_principal_name <> ''
GROUP BY session_server_principal_name
ORDER BY [Count] desc

 
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
GO