Email alert

Question

Email alert

mtz676

SSCrazy Eights

Points: 9212
More actions
November 6, 2019 at 2:29 pm

#3695438

Can extended events send out an email alert ?
ex: I have a windows or sql account created on a server . I can track that the account has been created but can I configure and have an email alert sent to me when the account creation happens
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Yes, in the alert, response, there is a "notify operators" check box. You will have to create an operator, and select the appropriate options for the alert.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

Thank you John.

Would you please elaborate on how to configure an email alert once an event happens which we needs to be emailed?

Also...

I am able to capture NTLogins activity.But the live windows seems to be caturing a lot more than the required data.

Ex: For a create table statement by a windows account ex: NTAccount1 will show up close to 20 rows.Can I have only 1 row which says that b.create table under sql and NTAccount1 under nt_user column ?

My requirement:

I would like to capture every query executed by every Windows account or every sqlaccount but only minimal data needs to be captured.I only need this row to show up in the watch live window to show up.

name

timestamp

statement

duration

nt_username

sql_statement_completed

45:02.1

CREATE DATABASE [bbbbbb] CONTAINMENT = NONE ON PRIMARY ( NAME = N'bbbbbb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\bbbbbb.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'bbbbbb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\bbbbbb_log.ldf' , SIZE = 35840KB , FILEGROWTH = 10%)

838374

NTAccount1

I will only need the one row as above without a trail of other related rows.

My Extended events code:

CREATE EVENT SESSION [AuditLogins] ON SERVER

ADD EVENT sqlserver.sql_statement_completed(

ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)

WHERE ([sqlserver].[sql_text]

like '%SELECT%' OR [sqlserver].[sql_text] like '%ALTER%' OR

[sqlserver].[sql_text] like '%DELETE%' OR [sqlserver].[sql_text] like '%UPDATE%' OR

[sqlserver].[sql_text] like '%INSERT%' OR [sqlserver].[sql_text] like '%CREATE%' OR

[sqlserver].[sql_text] like '%DROP%' OR [sqlserver].[sql_text] like '%RENAME%' OR

[sqlserver].[sql_text] like '%TRUNCATE%' OR [sqlserver].[sql_text] like '%COMMENT%' OR

[sqlserver].[sql_text] like '%MERGE%' OR [sqlserver].[sql_text] like '%CALL%' OR

[sqlserver].[sql_text] like '%EXPLAIN PLAN%' OR [sqlserver].[sql_text] like '%LOCK TABLE%' OR

[sqlserver].[sql_text] like '%GRANT%' OR [sqlserver].[sql_text] like '%REVOKE%' OR

[sqlserver].[sql_text] like '%INDEXES%' OR [sqlserver].[sql_text] like '%TABLE%' OR [sqlserver].[sql_text] like '%INDEX%'))

ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvents\AuditLogins.xel',max_file_size=(2),max_rollover_files=(1000))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,

MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)

GO

Thanks

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

mtz676 wrote:

Thank you John.
Would you please elaborate on how to configure an email alert once an event happens which we needs to be emailed?

Did you actually right-click on an alert, and look at the 'Response" section? If you did, you will see how to trigger an email as a response.

Also...
I am able to capture NTLogins activity.But the live windows seems to be caturing a lot more than the required data.
Ex: For a create table statement by a windows account ex: NTAccount1 will show up close to 20 rows.Can I have only 1 row which says that b.create table under sql and NTAccount1 under nt_user column ?

I am not sure what you are capturing, and how you are capturing this.

My requirement:
I would like to capture every query executed by every Windows account or every sqlaccount but only minimal data needs to be captured.I only need this row to show up in the watch live window to show up.
name
timestamp
statement
duration
nt_username

You already have this. It's called Query Store. There are numerous articles on how to configure this, and use it.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

I think there may be some confusion between Extended Events, and Event Notification. Extended Events are what the script you provided would create, and can write to targets such as a ring buffer, or to a text file. They don't really have the ability to notify you, you have to actively go out and read the target to see what events were captured.

Event Notification is a slightly different beast, it uses Service Broker and a queue of captured events. You can then have an activation stored procedure that determines what to do with those events as they are placed on the queue, such as send an e-mail when conditions are met:

https://docs.microsoft.com/en-us/sql/relational-databases/service-broker/event-notifications?view=sql-server-ver15