Applies to: All versions of DirectAudit, Microsoft SQL Server
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:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TRIGGER [dbo].[INSERT_TRIGGER_DirectAudit_Command]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- 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
@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)
The above commands, should produce events as follows and sends an email alert to the recipients as follows:
From: SunDB Administrator
Sent: Thursday, April 01, 2010 7:20 PM
To: Sumana Annam
Subject: A command was executed on audited system: rhel5.universe.com
A command wasexecuted on audited system
The user: email@example.com
Executed the command: [sumana@rhel5 ~]$ adinfo -m
On Host: rhel5.universe.com
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 "Server=IND-DC01.universe.com\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:
IF @@USER = 'youruser@yourdomain