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