2
responses

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?

 

AskedNovember 6, 2017 - 7:39pm

Answer (1)