Tips for finding Knowledge Articles

  • - Enter just a few key words related to your question or problem
  • - Add Key words to refine your search as necessary
  • - Do not use punctuation
  • - Search is not case sensitive
  • - Avoid non-descriptive filler words like "how", "the", "what", etc.
  • - If you do not find what you are looking for the first time,reduce the number of key words you enter and try searching again.
  • - Minimum supported Internet Explorer version is IE9
Home  >

KB-1887: SQL trigger to raise an event & send an email for every command executed on audited machines

Authentication Service ,  

11 April,19 at 07:50 PM

How can an event be raised into the Application Event log for every command executed on audited machines ?

SQL triggers can be used to raise such events in the Application event log. A trigger can be setup to get "fired" whenever
an INSERT, UPDATE or DELETE SQL command is executed against a specific table.

An INSERT SQL trigger against the table "command" needs to be setup in order to raise an event for every command executed on the audited machine.

Follow the below steps to create the INSERT SQL trigger:

1. Start by opening SQL Server Management Studio Express. Once it is launched, expand the Databases -> DirectAudit database -> Tables -> command -> Triggers node.

2. Right-click the Triggers node and choose "New Trigger ..."

3. SQL Server initially fills the form with a template for creating a new trigger; you can replace its content with the below:

   ON  [dbo].[command]
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    -- Insert statements for trigger here

    DECLARE @@COMMAND nvarchar(128)
    DECLARE @@SESSIONID varchar(255)
    DECLARE @@MESSAGE varchar(255)
    DECLARE @@HOST varchar(255)
    DECLARE @@USER varchar(255)
    DECLARE @@EMAILSUB varchar(255)
    SET @@COMMAND = (SELECT command FROM Inserted)
    SET @@SESSIONID = (SELECT session_id FROM Inserted)
    SET @@HOST = (SELECT machinename FROM dbo.session WHERE id = @@SESSIONID)
    SET @@USER = (SELECT username FROM dbo.session WHERE id = @@SESSIONID)
    SELECT @@MESSAGE = CHAR(13) + CHAR(13) + 'A command was executed on audited system' + CHAR(13) + CHAR(13) + 'The user: ' + @@USER + CHAR(13) + 'Executed the command: ' + @@COMMAND + CHAR(13) + 'On Host: ' + @@HOST + CHAR(13) + 'In Session: ' + @@SESSIONID
    EXEC master..xp_logevent 50001, @@MESSAGE, 'INFORMATIONAL'

SELECT @@EMAILSUB = 'A command was executed on audited system:' + @@COMMAND

    EXEC  msdb.dbo.sp_send_dbmail
    @profile_name = '<Database Mail Profile Name>',
    @recipients='<Email addresses for Recipients>',
    @body = @@MESSAGE,
    @body_format = 'TEXT',
    @subject = 'A command was executed on audited system' + @@HOST

INSERT_TRIGGER_DirectAudit_Command is the name of the trigger and it can be replaced with the name which fits the conventional naming for your environment.

In order to use the "msdb.dbo.sp_send_dbmail" in the trigger, you need to enable DB Mail and also create DB Mail profile.

4. Execute the trigger by clicking on the "execute" button or from the menu query -> execute or by hitting F5

5. To test, login into the audited machine and run few commands as shown:

[sumana@rhel5 ~]$ adinfo -m
[sumana@rhel5 ~]$ who
root     pts/0        2010-04-01 17:16 (:0.0)
sumana   pts/1        2010-04-01 17:54 (rhel5)
[sumana@rhel5 ~]$

The above commands, should produce events as follows and sends an email alert to the recipients as follows:

-----Original Message-----
From: SunDB Administrator
Sent: Thursday, April 01, 2010 7:20 PM
To: Sumana Annam
Subject: A command was executed on audited system:

A command wasexecuted on audited system

The user:
Executed the command: [sumana@rhel5 ~]$ adinfo -m
On Host:
In Session: 2E2D6946-E27C-C245-918D-C2F9124F38D2

Note: Any software which can slurp from the Event Log on Windows can be setup to send alerts based on these events. If you need to replay, then the session id from the event can be used to call the sessionplayer with the sql connect and session id.

C:\Program Files\Centrify\DirectAudit>sessionplayer.exe "Server=SQLServerName\InstanceName;Database=DatabaseName;Trusted_Connection=True;" sessionID

C:\Program Files\Centrify\DirectAudit>sessionplayer.exe "\SunDB;Database=DirectAudit_1.1.2;Trusted_Connection=True;" 2E2D6946-E27C-C245-918D-C2F9124F38D2

Will it also work, if we add a specified User ID?

you will need to enclose the EXEC statement in the trigger with condition clause. Example:

EXEC master.xp_logevent......
IF @@USER = 'youruser@yourdomain
EXEC master.xp_logevent....