Email alert

  • 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

  • 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/

  • 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

  • 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/

  • 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

     

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

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