ArcSight SQL TextData Field Override

Document created by tdaubner@hanger.com on Apr 17, 2014Last modified by tdaubner@hanger.com on Apr 17, 2014
Version 4Show Document
  • View in full screen mode

ArcSight SQL Override

Override for ArcSight Microsoft SQL Server Multiple Instance Audit DB

The ArcSight Microsoft SQL Server Multiple Instance Audit DB Smart Connector has an issue with having event data needed for compliance purposes being truncated at 1023 bytes within the message field. This issue is caused by the SQL query that reads the trace file logs is doing a select statement on the TextData column with a varchar of 1000. Which makes the SmartConnector only see the first 1000 characters in the TextData column. The other issue is that the message field can only be 1023 bytes in size. 

 

The below query is how the out of the box ArcSight Microsoft SQL Server Multiple Instance Audit DB SmartConnector tries to read the trace file logs. Which you can see only selects the first 1000 characters in the TextData column (CAST(TextData as varchar(1000)) as TextData).

 

Default Smart Connector MS SQL Query: Note the varchar.

query=SELECT CAST(TextData as varchar(1000)) as TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName, SessionLoginName, SPID,StartTime, \
   ClientProcessID, Duration, EndTime, Reads, Writes, CPU, EventSubClass, Success, EventClass, Error as errorCode, \
   ObjectName, DatabaseName,DatabaseID, FileName, OwnerName, LoginSid, TargetLoginName,TargetLoginSid, RoleName, TargetUserName, Permissions \
   FROM ::fn_trace_gettable('$TRACE_FILE_FULL_PATH$', 1) \
   where StartTime >= ?

Please note that you cannot just update the query to not have a character limit sense the message field has a Max message field size of 1023 bytes. Which means you have to update the query in such a way that the TextData column is under the Max field size and still has all of the needed column/fields in order to track the logs and parse them into the correct fields in Logger/ESM.

I made the below SQL query that will allow just that. Attached: Hanger-Inc-Trace-File-Split-TextData.sql

Hanger-Inc-Trace-File-Split-TextData.sql:

-- HANGER, INC
-- Created by TJ Daubner 04/08/2014
-- Update @TraceMe with the location of the trace file
-- @Limit is the Size limit to split on
declare @Limit int  = 1000
Declare @TraceMe varchar(max) = '<Trace File>'
-- @TempTab used to create a temp DB with a unique ID in-order to re-join once the TextData column is split into field sizes of 1000 characters
Declare @tempTab TABLE(ID INT identity(1,1),TextData varchar(MAX),NTUserName varchar(MAX),NTDomainName varchar(MAX),HostName varchar(MAX),ServerName varchar(MAX),ApplicationName varchar(MAX),LoginName varchar(MAX),
SessionLoginName varchar(MAX),SPID varchar(MAX),StartTime DateTime,ClientProcessID varchar(MAX),Duration varchar(MAX),EndTime DateTime,Reads varchar(MAX),Writes varchar(MAX),CPU varchar(MAX),EventSubClass varchar(MAX),
Success varchar(MAX),EventClass varchar(MAX),Error varchar(MAX),ObjectName varchar(MAX),DatabaseName varchar(MAX),DatabaseID varchar(MAX),FileName varchar(MAX),OwnerName varchar(MAX),LoginSid image,TargetLoginName varchar(MAX),
TargetLoginSid image,RoleName varchar(MAX),TargetUserName varchar(MAX),Permissions varchar(MAX))
-- @PrintTempTab used to rebuild the data once the split has ran
Declare @PrintTempTab TABLE(ID INT identity(1,1),TextData varchar(MAX),NTUserName varchar(MAX),NTDomainName varchar(MAX),HostName varchar(MAX),ServerName varchar(MAX),ApplicationName varchar(MAX),LoginName varchar(MAX),
SessionLoginName varchar(MAX),SPID varchar(MAX),StartTime DateTime,ClientProcessID varchar(MAX),Duration varchar(MAX),EndTime DateTime,Reads varchar(MAX),Writes varchar(MAX),CPU varchar(MAX),EventSubClass varchar(MAX),
Success varchar(MAX),EventClass varchar(MAX),Error varchar(MAX),ObjectName varchar(MAX),DatabaseName varchar(MAX),DatabaseID varchar(MAX),FileName varchar(MAX),OwnerName varchar(MAX),LoginSid image,TargetLoginName varchar(MAX),
TargetLoginSid image,RoleName varchar(MAX),TargetUserName varchar(MAX),Permissions varchar(MAX))
-- @i used to increment in the while statement
DECLARE @i int, @numrows varchar(MAX)
-- @Txt used to split the TextData column into multiple rows 
Declare @Txt varchar(max)
insert into @tempTab (TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error,
ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions) select TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,
SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error,ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions
from ::fn_trace_gettable(@TraceMe, 1)
WHERE TextData IS NOT NULL
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @tempTab)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(id) FROM @tempTab))
BEGIN
    SET @Txt = (SELECT TextData FROM @tempTab WHERE ID = @i)
    ;with cte_DoIt
    as ( select substring(@Txt, 0, ((@Limit+1)-charindex(' ', reverse(left(@Txt + ' ',@Limit))))) [TextData], stuff(@Txt, 1, ((@Limit+1)-charindex(' ', reverse(left(@Txt + ' ',@Limit)))), '') [Swap] union all
    select substring(Swap, 0, (@Limit+1) - charindex(' ', reverse(left(Swap + ' ', @Limit)))), stuff(Swap, 1, ((@Limit+1)-charindex(' ',reverse(left(Swap + ' ',@Limit)))), '') from cte_DoIt where len(Swap) > 0
    ) 
    insert into @PrintTempTab (TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error,ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions) select cte_DoIt.TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error,ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions
    from cte_DoIt   
    JOIN @tempTab on ID = @i
    SET @i = @i + 1
END
SELECTTextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error,ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions FROM@PrintTempTab

Once the query was created we updated the SQL SmartConnector override file (2005_2008.sdktbdatabase.properties) with the below to parse the SQL query for reading the trace files which is located $ARCSIGHT_HOME\current\user\agent\fcp\sqlserver_audit_db.

2005_2008.sdktbdatabase.properties:

# HANGER, INC
# Created by TJ Daubner 04/08/2014
# Update @TraceMe with the location of the trace file
# @Limit is the Size limit to split on
query=declare @Limit int  = 1000 \
Declare @TraceMe varchar(max) = '$TRACE_FILE_FULL_PATH$' \
-- @TempTab used to create a temp DB with a unique ID in-order to re-join once the TextData column is split into field sizes of 1000 characters \
Declare @tempTab TABLE(ID INT identity(1,1),TextData varchar(MAX),NTUserName varchar(MAX),NTDomainName varchar(MAX),HostName varchar(MAX),ServerName varchar(MAX),ApplicationName varchar(MAX),LoginName varchar(MAX), \
SessionLoginName varchar(MAX),SPID varchar(MAX),StartTime DateTime,ClientProcessID varchar(MAX),Duration varchar(MAX),EndTime DateTime,Reads varchar(MAX),Writes varchar(MAX),CPU varchar(MAX),EventSubClass varchar(MAX), \
Success varchar(MAX),EventClass varchar(MAX),Error varchar(MAX),ObjectName varchar(MAX),DatabaseName varchar(MAX),DatabaseID varchar(MAX),FileName varchar(MAX),OwnerName varchar(MAX),LoginSid image,TargetLoginName varchar(MAX), \
TargetLoginSid image,RoleName varchar(MAX),TargetUserName varchar(MAX),Permissions varchar(MAX)) \
-- @PrintTempTab used to rebuild the data once the split has ran \
Declare @PrintTempTab TABLE(ID INT identity(1,1),TextData varchar(MAX),NTUserName varchar(MAX),NTDomainName varchar(MAX),HostName varchar(MAX),ServerName varchar(MAX),ApplicationName varchar(MAX),LoginName varchar(MAX), \
SessionLoginName varchar(MAX),SPID varchar(MAX),StartTime DateTime,ClientProcessID varchar(MAX),Duration varchar(MAX),EndTime DateTime,Reads varchar(MAX),Writes varchar(MAX),CPU varchar(MAX),EventSubClass varchar(MAX), \
Success varchar(MAX),EventClass varchar(MAX),Error varchar(MAX),ObjectName varchar(MAX),DatabaseName varchar(MAX),DatabaseID varchar(MAX),FileName varchar(MAX),OwnerName varchar(MAX),LoginSid image,TargetLoginName varchar(MAX), \
TargetLoginSid image,RoleName varchar(MAX),TargetUserName varchar(MAX),Permissions varchar(MAX)) \
-- @i used to increment in the while statement \
DECLARE @i int, @numrows varchar(MAX) \
-- @Txt used to split the TextData column into multiple rows \
Declare @Txt varchar(max) \
insert into @tempTab (TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error, \
ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions) select TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName, \
SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error,ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions \
from ::fn_trace_gettable(@TraceMe, 1) \
WHERE TextData IS NOT NULL AND StartTime >= ? \
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @tempTab) \
IF @numrows > 0 \
WHILE (@i <= (SELECT MAX(id) FROM @tempTab)) \
BEGIN \
    SET @Txt = (SELECT TextData FROM @tempTab WHERE ID = @i) \
    \;with cte_DoIt\
    as ( select substring(@Txt, 0, ((@Limit+1)-charindex(' ', reverse(left(@Txt + ' ',@Limit))))) [TextData], stuff(@Txt, 1, ((@Limit+1)-charindex(' ', reverse(left(@Txt + ' ',@Limit)))), '') [Swap] union all \
    select substring(Swap, 0, (@Limit+1) - charindex(' ', reverse(left(Swap + ' ', @Limit)))), stuff(Swap, 1, ((@Limit+1)-charindex(' ',reverse(left(Swap + ' ',@Limit)))), '') from cte_DoIt where len(Swap) > 0 \
    ) \
    insert into @PrintTempTab (TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error,ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions) select cte_DoIt.TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass,Error,ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions \
    from cte_DoIt \
    JOIN @tempTab on ID = @i \
    SET @i = @i + 1 \
END
SELECT TextData,NTUserName,NTDomainName,HostName,ServerName,ApplicationName,LoginName,SessionLoginName,SPID,StartTime,ClientProcessID,Duration,EndTime,Reads,Writes,CPU,EventSubClass,Success,EventClass, \
  Error,ObjectName,DatabaseName,DatabaseID,FileName,OwnerName,LoginSid,TargetLoginName,TargetLoginSid,RoleName,TargetUserName,Permissions FROM @PrintTempTab \

I've attached the 2005_2008.sdktbdatabase.properties to this post.

 

Over View:

There is a limitation in ArcSight Logger with the Max message field size of 1023 bytes. Rolling your logs to other fields will not make sure you get 100% of the TextData message field in the MS SQL trace logs. So, If you want to make sure you always get the full log read.  ArcSight support has a future product enhancement for their Microsoft SQL Server Multiple Instance Audit DB Smart Connector to be able to fully log MS SQL trace logs with out the override I provided to meet SOX compliance around SQL direct data change.


Outcomes