Periodically retrieving data from MSSQL on Linux
Tags:
mssql odbc linux
#1
avhk
Hello,
I have what I think is a fairly straightforward situation. I'm running queries against an MS-SQL server to retrieve data every 5 minutes. The wrinkle is that I am using unixodbc from a ubuntu 16.04 machine with nxlog to do this. I'm able to retrieve all of the data once on a fresh install of nxlog. However, I'm not seeing new data every 5 minutes and I certain there should be more data as our SQL source is continually writing new entries...
My input section has a fairly complicated query...
<Input in_epo1>
Module im_odbc
ConnectionString DSN=Server;Database=ePO_PROD;UID=epo_user;PWD=epo_pwd;
PollInterval 300
SQL SELECT [EPOEvents].[AutoID], [EPOEvents].[ReceivedUTC] as [timestamp_epo], [EPOEvents].[ThreatName] as [signature], [EPOEvents].[ThreatType] as [threat_type], [EPOEvents].[ThreatEventID] as [signature_id], [EPOEvents].[ThreatCategory] as [category], [EPOEvents].[ThreatSeverity] as [severity_id], [EPOEventFilterDesc].[Name] as [event_description], [EPOEvents].[DetectedUTC] as [detected_timestamp], [EPOEvents].[TargetFileName] as [file_name], [EPOEvents].[AnalyzerDetectionMethod] as [detection_method], [EPOEvents].[ThreatActionTaken] as [vendor_action], CAST([EPOEvents].[ThreatHandled] as int) as [threat_handled], [EPOEvents].[TargetUserName] as [logon_user], [EPOComputerProperties].[UserName] as [user], [EPOComputerProperties].[DomainName] as [dest_nt_domain], [EPOEvents].[TargetHostName] as [dest_dns], [EPOEvents].[TargetHostName] as [dest_nt_host], [EPOComputerProperties].[IPHostName] as [fqdn], [dest_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),4,1))) ), [EPOComputerProperties].[SubnetMask] as [dest_netmask], [EPOComputerProperties].[NetAddress] as [dest_mac], [EPOComputerProperties].[OSType] as [os], [EPOComputerProperties].[OSServicePackVer] as [sp], [EPOComputerProperties].[OSVersion] as [os_version], [EPOComputerProperties].[OSBuildNum] as [os_build], [EPOComputerProperties].[TimeZone] as [timezone], [EPOEvents].[SourceHostName] as [src_dns], [src_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),4,1))) ), [EPOEvents].[SourceMAC] as [src_mac], [EPOEvents].[SourceProcessName] as [process], [EPOEvents].[SourceURL] as [url], [EPOEvents].[SourceUserName] as [source_logon_user], [EPOComputerProperties].[IsPortable] as [is_laptop], [EPOEvents].[AnalyzerName] as [product], [EPOEvents].[AnalyzerVersion] as [product_version], [EPOEvents].[AnalyzerEngineVersion] as [engine_version], [EPOEvents].[AnalyzerEngineVersion] as [dat_version], [EPOProdPropsView_VIRUSCAN].[datver] as [vse_dat_version], [EPOProdPropsView_VIRUSCAN].[enginever64] as [vse_engine64_version], [EPOProdPropsView_VIRUSCAN].[enginever] as [vse_engine_version], [EPOProdPropsView_VIRUSCAN].[hotfix] as [vse_hotfix], [EPOProdPropsView_VIRUSCAN].[productversion] as [vse_product_version], [EPOProdPropsView_VIRUSCAN].[servicepack] as [vse_sp] FROM [EPOEvents] left join [EPOLeafNode] on [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] left join [EPOEventFilterDesc] on [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId] and (EPOEventFilterDesc.Language='0409') WHERE (DATEDIFF(hour, CAST([EPOEvents].[ReceivedUTC] as date), getutcdate()) between 0 and 2) AND [EPOEvents].[AutoID] > ?
Exec delete($timestamp_epo);
</Input>
To me this looks like a fairly standard query - nxlog should save its position and know to query [EPOEvents].[AutoID] > lastseen_autoid. We added (DATEDIFF(hour, CAST([EPOEvents].[ReceivedUTC] as date), getutcdate()) between 0 and 2) into the mix to limit our results to the last two hours, however, we could equally remove this.
So I enabled ODBC tracing and it certainly seems like nxlog is querying the database successfully. Here's a short snippet:
[ODBC][10][1509980277.861057][SQLDriverConnect.c][726]
Entry:
Connection = 0x7f6fa8000eb0
Window Hdl = (nil)
Str In = [DSN=DATABASE2;Database=DATABASE2;UID=user2;PWD=*************;][length = 58 (SQL_NTS)]
Str Out = 0x7f6fb8eb9bd0
Str Out Max = 1024
Str Out Ptr = 0x7f6fb8eb9144
Completion = 0
[ODBC][10][1509980277.861922][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x7f6fa00008c0
[ODBC][10][1509980277.861965][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fa0000ef0
[ODBC][10][1509980277.862003][SQLDriverConnect.c][726]
Entry:
Connection = 0x7f6fa0000ef0
Window Hdl = (nil)
Str In = [DSN=DATABASE1;Database=DATABASE1;UID=user1;PWD=***;][length = 72 (SQL_NTS)]
Str Out = 0x7f6fb6eb5bd0
Str Out Max = 1024
Str Out Ptr = 0x7f6fb6eb5144
Completion = 0
[ODBC][10][1509980277.862040][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980277.862078][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x7f6fb00009b0
[ODBC][10][1509980277.862093][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fb0001270
[ODBC][10][1509980277.862115][SQLDriverConnect.c][726]
Entry:
Connection = 0x7f6fb0001270
Window Hdl = (nil)
Str In = [DSN=DATABASE3;Database=DATABASE3;UID=user2;PWD=*************;][length = 58 (SQL_NTS)]
Str Out = 0x7f6fb7eb7bd0
Str Out Max = 1024
Str Out Ptr = 0x7f6fb7eb7144
Completion = 0
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE1'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.892817][SQLDriverConnect.c][1582]
Exit:[SQL_SUCCESS_WITH_INFO]
Connection Out [DSN=DATABASE1;UID=user1;PWD=********;WSID=8ef53561e...]
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE1'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.892903][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x7f6fa0000ef0
[ODBC][10][1509980277.892936][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fa003f1b0
[ODBC][10][1509980277.892952][SQLBindParameter.c][217]
Entry:
Statement = 0x7f6fa003f1b0
Param Number = 1
Param Type = 1
C Type = -18 SQL_C_ULONG
SQL Type = -5 SQL_BIGINT
Col Def = 0
Scale = 0
Rgb Value = 0x7f6fbc3b0e28
Value Max = 0
StrLen Or Ind = (nil)
[ODBC][10][1509980277.892974][SQLBindParameter.c][397]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980277.893002][SQLExecDirect.c][240]
Entry:
Statement = 0x7f6fa003f1b0
SQL = [SELECT [EPOEvents].[AutoID], [EPOEvents].[ReceivedUTC] as [timestamp_epo], [EPOEvents].[ThreatName] as [signature], [EPOEvents]....][length = 3793 (SQL_NTS)]
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE3'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.893946][SQLDriverConnect.c][1582]
Exit:[SQL_SUCCESS_WITH_INFO]
Connection Out [DSN=DATABASE3;UID=user2;PWD=********;WSID=8ef53561e30d;DATABAS...]
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE2'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE3'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.894011][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x7f6fb0001270
[ODBC][10][1509980277.894023][SQLDriverConnect.c][1582]
Exit:[SQL_SUCCESS_WITH_INFO]
Connection Out [DSN=DATABASE2;UID=user2;PWD=********;WSID=8ef53561e30d;DATABAS...]
[ODBC][10][1509980277.894038][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fb0052780
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE2'.
[ODBC][10][1509980277.894058][SQLBindParameter.c][217]
Entry:
Statement = 0x7f6fb0052780
Param Number = 1
Param Type = 1
C Type = -18 SQL_C_ULONG
SQL Type = -5 SQL_BIGINT
Col Def = 0
Scale = 0
Rgb Value = 0x7f6fbc38ce28
Value Max = 0
StrLen Or Ind = (nil)
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.894079][SQLBindParameter.c][397]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980277.894107][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x7f6fa8000eb0
[ODBC][10][1509980277.894112][SQLExecDirect.c][240]
Entry:
Statement = 0x7f6fb0052780
SQL = [SELECT [EPOEvents].[AutoID], [EPOEvents].[ReceivedUTC] as [timestamp_epo], [EPOEvents].[ThreatName] as [signature], [EPOEvents]....][length = 3793 (SQL_NTS)]
[ODBC][10][1509980277.894138][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fa8067230
[ODBC][10][1509980277.894159][SQLBindParameter.c][217]
Entry:
Statement = 0x7f6fa8067230
Param Number = 1
Param Type = 1
C Type = -18 SQL_C_ULONG
SQL Type = -5 SQL_BIGINT
Col Def = 0
Scale = 0
Rgb Value = 0x7f6fbc39ee28
Value Max = 0
StrLen Or Ind = (nil)
[ODBC][10][1509980277.894179][SQLBindParameter.c][397]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980277.894211][SQLExecDirect.c][240]
Entry:
Statement = 0x7f6fa8067230
SQL = [SELECT [EPOEvents].[AutoID], [EPOEvents].[ReceivedUTC] as [timestamp_epo], [EPOEvents].[ThreatName] as [signature], [EPOEvents]....][length = 3793 (SQL_NTS)]
[ODBC][10][1509980278.153007][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153074][SQLNumResultCols.c][156]
Entry:
Statement = 0x7f6fb0052780
Column Count = 0x7f6fbc38ce80
[ODBC][10][1509980278.153103][SQLNumResultCols.c][248]
Exit:[SQL_SUCCESS]
Count = 0x7f6fbc38ce80 -> 44
[ODBC][10][1509980278.153133][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 1
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a0
Column Size = 0x7f6fbc2d83b8
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153169][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [AutoID]
Data Type = 0x7f6fbc2d80a0 -> 4
Column Size = 0x7f6fbc2d83b8 -> 10
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153197][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 1
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153247][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153274][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 2
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a2
Column Size = 0x7f6fbc2d83c0
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153303][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [timestamp_epo]
Data Type = 0x7f6fbc2d80a2 -> 93
Column Size = 0x7f6fbc2d83c0 -> 23
Decimal Digits = 0x7f6fb7eb654e -> 3
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153326][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 2
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153350][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153373][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 3
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a4
Column Size = 0x7f6fbc2d83c8
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153401][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [signature]
Data Type = 0x7f6fbc2d80a4 -> -9
Column Size = 0x7f6fbc2d83c8 -> 128
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153424][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 3
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153447][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153560][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 4
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a6
Column Size = 0x7f6fbc2d83d0
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153589][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [threat_type]
Data Type = 0x7f6fbc2d80a6 -> -9
Column Size = 0x7f6fbc2d83d0 -> 32
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153612][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 4
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153635][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153732][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 5
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a8
Column Size = 0x7f6fbc2d83d8
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153760][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [signature_id]
Data Type = 0x7f6fbc2d80a8 -> 4
Column Size = 0x7f6fbc2d83d8 -> 10
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153783][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 5
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153814][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153837][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 6
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80aa
Column Size = 0x7f6fbc2d83e0
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153864][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [category]
Data Type = 0x7f6fbc2d80aa -> -9
Column Size = 0x7f6fbc2d83e0 -> 128
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153887][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 6
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153909][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.154007][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 7
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80ac
Column Size = 0x7f6fbc2d83e8
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.154035][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [severity_id]
Data Type = 0x7f6fbc2d80ac -> -6
Column Size = 0x7f6fbc2d83e8 -> 3
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
However nothing is being written to the output json file.
Does it look like I'm doing anything wrong? Is there anything else I should try?
#1
avhk
Hello,
I have what I think is a fairly straightforward situation. I'm running queries against an MS-SQL server to retrieve data every 5 minutes. The wrinkle is that I am using unixodbc from a ubuntu 16.04 machine with nxlog to do this. I'm able to retrieve all of the data once on a fresh install of nxlog. However, I'm not seeing new data every 5 minutes and I certain there should be more data as our SQL source is continually writing new entries...
My input section has a fairly complicated query...
<Input in_epo1>
Module im_odbc
ConnectionString DSN=Server;Database=ePO_PROD;UID=epo_user;PWD=epo_pwd;
PollInterval 300
SQL SELECT [EPOEvents].[AutoID], [EPOEvents].[ReceivedUTC] as [timestamp_epo], [EPOEvents].[ThreatName] as [signature], [EPOEvents].[ThreatType] as [threat_type], [EPOEvents].[ThreatEventID] as [signature_id], [EPOEvents].[ThreatCategory] as [category], [EPOEvents].[ThreatSeverity] as [severity_id], [EPOEventFilterDesc].[Name] as [event_description], [EPOEvents].[DetectedUTC] as [detected_timestamp], [EPOEvents].[TargetFileName] as [file_name], [EPOEvents].[AnalyzerDetectionMethod] as [detection_method], [EPOEvents].[ThreatActionTaken] as [vendor_action], CAST([EPOEvents].[ThreatHandled] as int) as [threat_handled], [EPOEvents].[TargetUserName] as [logon_user], [EPOComputerProperties].[UserName] as [user], [EPOComputerProperties].[DomainName] as [dest_nt_domain], [EPOEvents].[TargetHostName] as [dest_dns], [EPOEvents].[TargetHostName] as [dest_nt_host], [EPOComputerProperties].[IPHostName] as [fqdn], [dest_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOComputerProperties].[IPV4x] + 2147483648))),4,1))) ), [EPOComputerProperties].[SubnetMask] as [dest_netmask], [EPOComputerProperties].[NetAddress] as [dest_mac], [EPOComputerProperties].[OSType] as [os], [EPOComputerProperties].[OSServicePackVer] as [sp], [EPOComputerProperties].[OSVersion] as [os_version], [EPOComputerProperties].[OSBuildNum] as [os_build], [EPOComputerProperties].[TimeZone] as [timezone], [EPOEvents].[SourceHostName] as [src_dns], [src_ip] = ( convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),1,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),2,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),3,1)))+'.'+convert(varchar(3),convert(tinyint,substring(convert(varbinary(4),convert(bigint,([EPOEvents].[SourceIPV4] + 2147483648))),4,1))) ), [EPOEvents].[SourceMAC] as [src_mac], [EPOEvents].[SourceProcessName] as [process], [EPOEvents].[SourceURL] as [url], [EPOEvents].[SourceUserName] as [source_logon_user], [EPOComputerProperties].[IsPortable] as [is_laptop], [EPOEvents].[AnalyzerName] as [product], [EPOEvents].[AnalyzerVersion] as [product_version], [EPOEvents].[AnalyzerEngineVersion] as [engine_version], [EPOEvents].[AnalyzerEngineVersion] as [dat_version], [EPOProdPropsView_VIRUSCAN].[datver] as [vse_dat_version], [EPOProdPropsView_VIRUSCAN].[enginever64] as [vse_engine64_version], [EPOProdPropsView_VIRUSCAN].[enginever] as [vse_engine_version], [EPOProdPropsView_VIRUSCAN].[hotfix] as [vse_hotfix], [EPOProdPropsView_VIRUSCAN].[productversion] as [vse_product_version], [EPOProdPropsView_VIRUSCAN].[servicepack] as [vse_sp] FROM [EPOEvents] left join [EPOLeafNode] on [EPOEvents].[AgentGUID] = [EPOLeafNode].[AgentGUID] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] left join [EPOEventFilterDesc] on [EPOEvents].[ThreatEventID] = [EPOEventFilterDesc].[EventId] and (EPOEventFilterDesc.Language='0409') WHERE (DATEDIFF(hour, CAST([EPOEvents].[ReceivedUTC] as date), getutcdate()) between 0 and 2) AND [EPOEvents].[AutoID] > ?
Exec delete($timestamp_epo);
</Input>
To me this looks like a fairly standard query - nxlog should save its position and know to query [EPOEvents].[AutoID] > lastseen_autoid. We added (DATEDIFF(hour, CAST([EPOEvents].[ReceivedUTC] as date), getutcdate()) between 0 and 2) into the mix to limit our results to the last two hours, however, we could equally remove this.
So I enabled ODBC tracing and it certainly seems like nxlog is querying the database successfully. Here's a short snippet:
[ODBC][10][1509980277.861057][SQLDriverConnect.c][726]
Entry:
Connection = 0x7f6fa8000eb0
Window Hdl = (nil)
Str In = [DSN=DATABASE2;Database=DATABASE2;UID=user2;PWD=*************;][length = 58 (SQL_NTS)]
Str Out = 0x7f6fb8eb9bd0
Str Out Max = 1024
Str Out Ptr = 0x7f6fb8eb9144
Completion = 0
[ODBC][10][1509980277.861922][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x7f6fa00008c0
[ODBC][10][1509980277.861965][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fa0000ef0
[ODBC][10][1509980277.862003][SQLDriverConnect.c][726]
Entry:
Connection = 0x7f6fa0000ef0
Window Hdl = (nil)
Str In = [DSN=DATABASE1;Database=DATABASE1;UID=user1;PWD=***;][length = 72 (SQL_NTS)]
Str Out = 0x7f6fb6eb5bd0
Str Out Max = 1024
Str Out Ptr = 0x7f6fb6eb5144
Completion = 0
[ODBC][10][1509980277.862040][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980277.862078][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x7f6fb00009b0
[ODBC][10][1509980277.862093][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fb0001270
[ODBC][10][1509980277.862115][SQLDriverConnect.c][726]
Entry:
Connection = 0x7f6fb0001270
Window Hdl = (nil)
Str In = [DSN=DATABASE3;Database=DATABASE3;UID=user2;PWD=*************;][length = 58 (SQL_NTS)]
Str Out = 0x7f6fb7eb7bd0
Str Out Max = 1024
Str Out Ptr = 0x7f6fb7eb7144
Completion = 0
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE1'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.892817][SQLDriverConnect.c][1582]
Exit:[SQL_SUCCESS_WITH_INFO]
Connection Out [DSN=DATABASE1;UID=user1;PWD=********;WSID=8ef53561e...]
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE1'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.892903][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x7f6fa0000ef0
[ODBC][10][1509980277.892936][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fa003f1b0
[ODBC][10][1509980277.892952][SQLBindParameter.c][217]
Entry:
Statement = 0x7f6fa003f1b0
Param Number = 1
Param Type = 1
C Type = -18 SQL_C_ULONG
SQL Type = -5 SQL_BIGINT
Col Def = 0
Scale = 0
Rgb Value = 0x7f6fbc3b0e28
Value Max = 0
StrLen Or Ind = (nil)
[ODBC][10][1509980277.892974][SQLBindParameter.c][397]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980277.893002][SQLExecDirect.c][240]
Entry:
Statement = 0x7f6fa003f1b0
SQL = [SELECT [EPOEvents].[AutoID], [EPOEvents].[ReceivedUTC] as [timestamp_epo], [EPOEvents].[ThreatName] as [signature], [EPOEvents]....][length = 3793 (SQL_NTS)]
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE3'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.893946][SQLDriverConnect.c][1582]
Exit:[SQL_SUCCESS_WITH_INFO]
Connection Out [DSN=DATABASE3;UID=user2;PWD=********;WSID=8ef53561e30d;DATABAS...]
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE2'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE3'.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.894011][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x7f6fb0001270
[ODBC][10][1509980277.894023][SQLDriverConnect.c][1582]
Exit:[SQL_SUCCESS_WITH_INFO]
Connection Out [DSN=DATABASE2;UID=user2;PWD=********;WSID=8ef53561e30d;DATABAS...]
[ODBC][10][1509980277.894038][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fb0052780
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed database context to 'DATABASE2'.
[ODBC][10][1509980277.894058][SQLBindParameter.c][217]
Entry:
Statement = 0x7f6fb0052780
Param Number = 1
Param Type = 1
C Type = -18 SQL_C_ULONG
SQL Type = -5 SQL_BIGINT
Col Def = 0
Scale = 0
Rgb Value = 0x7f6fbc38ce28
Value Max = 0
StrLen Or Ind = (nil)
DIAG [01000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Changed language setting to us_english.
[ODBC][10][1509980277.894079][SQLBindParameter.c][397]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980277.894107][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x7f6fa8000eb0
[ODBC][10][1509980277.894112][SQLExecDirect.c][240]
Entry:
Statement = 0x7f6fb0052780
SQL = [SELECT [EPOEvents].[AutoID], [EPOEvents].[ReceivedUTC] as [timestamp_epo], [EPOEvents].[ThreatName] as [signature], [EPOEvents]....][length = 3793 (SQL_NTS)]
[ODBC][10][1509980277.894138][SQLAllocHandle.c][1081]
Exit:[SQL_SUCCESS]
Output Handle = 0x7f6fa8067230
[ODBC][10][1509980277.894159][SQLBindParameter.c][217]
Entry:
Statement = 0x7f6fa8067230
Param Number = 1
Param Type = 1
C Type = -18 SQL_C_ULONG
SQL Type = -5 SQL_BIGINT
Col Def = 0
Scale = 0
Rgb Value = 0x7f6fbc39ee28
Value Max = 0
StrLen Or Ind = (nil)
[ODBC][10][1509980277.894179][SQLBindParameter.c][397]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980277.894211][SQLExecDirect.c][240]
Entry:
Statement = 0x7f6fa8067230
SQL = [SELECT [EPOEvents].[AutoID], [EPOEvents].[ReceivedUTC] as [timestamp_epo], [EPOEvents].[ThreatName] as [signature], [EPOEvents]....][length = 3793 (SQL_NTS)]
[ODBC][10][1509980278.153007][SQLExecDirect.c][503]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153074][SQLNumResultCols.c][156]
Entry:
Statement = 0x7f6fb0052780
Column Count = 0x7f6fbc38ce80
[ODBC][10][1509980278.153103][SQLNumResultCols.c][248]
Exit:[SQL_SUCCESS]
Count = 0x7f6fbc38ce80 -> 44
[ODBC][10][1509980278.153133][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 1
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a0
Column Size = 0x7f6fbc2d83b8
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153169][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [AutoID]
Data Type = 0x7f6fbc2d80a0 -> 4
Column Size = 0x7f6fbc2d83b8 -> 10
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153197][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 1
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153247][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153274][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 2
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a2
Column Size = 0x7f6fbc2d83c0
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153303][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [timestamp_epo]
Data Type = 0x7f6fbc2d80a2 -> 93
Column Size = 0x7f6fbc2d83c0 -> 23
Decimal Digits = 0x7f6fb7eb654e -> 3
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153326][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 2
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153350][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153373][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 3
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a4
Column Size = 0x7f6fbc2d83c8
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153401][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [signature]
Data Type = 0x7f6fbc2d80a4 -> -9
Column Size = 0x7f6fbc2d83c8 -> 128
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153424][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 3
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153447][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153560][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 4
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a6
Column Size = 0x7f6fbc2d83d0
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153589][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [threat_type]
Data Type = 0x7f6fbc2d80a6 -> -9
Column Size = 0x7f6fbc2d83d0 -> 32
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153612][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 4
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153635][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153732][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 5
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80a8
Column Size = 0x7f6fbc2d83d8
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153760][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [signature_id]
Data Type = 0x7f6fbc2d80a8 -> 4
Column Size = 0x7f6fbc2d83d8 -> 10
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153783][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 5
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153814][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.153837][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 6
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80aa
Column Size = 0x7f6fbc2d83e0
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.153864][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [category]
Data Type = 0x7f6fbc2d80aa -> -9
Column Size = 0x7f6fbc2d83e0 -> 128
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
[ODBC][10][1509980278.153887][SQLColAttribute.c][293]
Entry:
Statement = 0x7f6fb0052780
Column Number = 6
Field Identifier = SQL_DESC_TYPE_NAME
Character Attr = 0x7f6fb7eb7000
Buffer Length = 256
String Length = (nil)
Numeric Attribute = (nil)
[ODBC][10][1509980278.153909][SQLColAttribute.c][664]
Exit:[SQL_SUCCESS]
[ODBC][10][1509980278.154007][SQLDescribeCol.c][247]
Entry:
Statement = 0x7f6fb0052780
Column Number = 7
Column Name = 0x7f6fb7eb6f00
Buffer Length = 255
Name Length = 0x7f6fb7eb654c
Data Type = 0x7f6fbc2d80ac
Column Size = 0x7f6fbc2d83e8
Decimal Digits = 0x7f6fb7eb654e
Nullable = 0x7f6fb7eb6550
[ODBC][10][1509980278.154035][SQLDescribeCol.c][497]
Exit:[SQL_SUCCESS]
Column Name = [severity_id]
Data Type = 0x7f6fbc2d80ac -> -6
Column Size = 0x7f6fbc2d83e8 -> 3
Decimal Digits = 0x7f6fb7eb654e -> 0
Nullable = 0x7f6fb7eb6550 -> 0
However nothing is being written to the output json file.
Does it look like I'm doing anything wrong? Is there anything else I should try?
You may want to enable DEBUG logging to see if you can find out more.
