SQL Server Logon Auditing and Reporting the data from a DB Table
Auditing of logons is set with options available through the SSMS studio or via T-SQL. You can log success or failure or both.  The recommended destination is the Windows event log which can be queried with PowerShell or exported.

This fills up your event log fast as many service accounts are connecting many times a minute.

An audit policy can be created which can be more selective. From here


Connect the SQL server instance via SQL Server Management Studio.
Navigate to Security → Right-click “Audits” and select “New audit” →
Type in an name for the audit and select the location where the SQL
Server audit logs will be stored → Click “OK” → 
Right-click the newly created audit and select “Enable audit”. 
Right-click “Server Audit Specification” and select “New Server Audit Specification” → 
Type in an appropriate name → Select the new audit from the audit drop-down
menu → 
In the “Audit action type” column, select “Failed Login Group”
and “Successful Login Group” → Click “OK” → 
Right-click on the newly
created server audit specification and select “Enable server audit
specification”. 
To view the SQL Server audit login trail, navigate to
Security | Audits → Right-click the newly created audit and select
“View Audit Logs”


You can also make a system trigger but why bother when an audit policy will do the job?