Audit login and audit logout

For sqlserver databases, when a database connection is established, the audit login event is triggered. When a database connection is closed, the audit logout event is triggered.

1. Audit logout duration value

Note: In the event probe, we can observe that the audit login and matched audit logout events useSame spidAnd the starttime recorded by the Audit logout event is the time when the audit login event was generated. See the figure below:

(Note the login and logout events with spid 61)

ThereforeDurationThe value of the field indicates how long the closed connection has been alive, rather than how long it takes to "Close the connection. However, you can use the audit logout to record how much CPU time is consumed to close the connection.CPUField Value.

2. query the current connection of sqlserver

There are two common methods:

1. sp_who: For example, to query the number of connections to log on using SA, you can call

Sp_who ' SA '

2. Query System table:

Select * From
[Master]. [DBO]. [sysprocesses] Where [dbid]
In
(
Select
[Dbid]
From
[Master]. [DBO]. [sysdatabases]
Where
Name = ' Yourdbname '
)

When using system table queries, I am most concerned with some of the columns:

Select spid, CPU, physical_io, memusage, login_time, last_batch, status from
[Master]. [DBO]. [sysprocesses] Where [dbid]
In
(
Select
[Dbid]
From
[Master]. [DBO]. [sysdatabases]
Where
Name = 'Yourdbname '
)