Applies to:

All versions of Centrify Direct Audit

Question

How to run a query that can run against the active Audit Store database to find all sessions created by the scp/rsync/sftp traffic?

Answer:

Here's a query that can be run against the active Audit Store database to find all sessions created by the scp/rsync/sftp traffic. Use SQL Management Studio to run this query:


SELECT DISTINCT(Session.SessionId), MachineName, UserName, Tty FROM RawData INNER JOIN Session ON RawData.SessionId = Session.SessionId WHERE (CONTAINS(Tag, 'scp') OR CONTAINS(Tag, 'rsync') OR CONTAINS(Tag, 'sftp')) AND TYPE = 0


The above query returns Ids of the sessions; In case want to find just the machine names, modify the query slightly as given below:


SELECT DISTINCT(MachineName) FROM RawData INNER JOIN Session ON RawData.SessionId = Session.SessionId WHERE (CONTAINS(Tag, 'scp') OR CONTAINS(Tag, 'rsync') OR CONTAINS(Tag, 'sftp')) AND TYPE = 0

How to delete the session:
 
The same query condition can be used to delete the sessions. However,  it is recommended to use the following SQL statements to perform delete. The SQL statements below are more intelligent as they divide the entire “delete” job into small batches which prevents uncontrolled growth of the transaction logs.
 

DECLARE @batchSize INT
DECLARE @sessionId UNIQUEIDENTIFIER
DECLARE @flag INT
DECLARE @continue INT
 
SET @batchSize = 5 /* PROCESS ONLY 5 SESSIONS AT A TIME */
SET @continue = (SELECT COUNT(SessionId) FROM RawData WHERE (CONTAINS(Tag, 'scp') OR CONTAINS(Tag, 'rsync') OR CONTAINS(Tag, 'sftp')) AND TYPE = 0)
WHILE (@continue > 0)
BEGIN
 
       DECLARE session_cursor CURSOR
              FOR SELECT TOP (@batchSize) SessionId FROM RawData WHERE (CONTAINS(Tag, 'scp') OR CONTAINS(Tag, 'rsync') OR CONTAINS(Tag, 'sftp')) AND TYPE = 0 ORDER BY SessionId;
 
       OPEN session_cursor
       FETCH NEXT FROM session_cursor INTO @sessionId
       WHILE @@FETCH_STATUS = 0
       BEGIN
              PRINT N'Deleting session ' + CAST(@sessionId AS nvarchar(50))
              SET @flag = (SELECT COUNT(*) FROM RawData WHERE SessionId=@sessionId)
              PRINT N'Total rows to delete for session ' + CAST(@sessionId AS nvarchar(50)) + N' are ' + CAST(@flag AS nvarchar(max))
              WHILE (@flag > 0)
              BEGIN
                     DELETE TOP(5000) FROM RawData WHERE SessionId=@sessionId
                     SET @flag = (SELECT COUNT(*) FROM RawData WHERE SessionId=@sessionId)
              END
              PRINT N'Clearing remaining tables for session ' + CAST(@sessionId AS nvarchar(50))
              DELETE FROM Session WHERE SessionId=@sessionId
              FETCH NEXT FROM session_cursor INTO @sessionId
       END
 
       CLOSE session_cursor;
       DEALLOCATE session_cursor;
 
SET @continue = (SELECT COUNT(SessionId) FROM RawData WHERE (CONTAINS(Tag, 'scp') OR CONTAINS(Tag, 'rsync') OR CONTAINS(Tag, 'sftp')) AND TYPE = 0)
END