Hello NXlog world! I'm having some challenges to pull data from a SQL 2012 (running on W2K16) database using the im-odbc connector. This is my input:

> <Input MSSQL_IN> > Module im_odbc > ConnectionString Driver={ODBC Driver 17 for SQL Server}; Server=XXXXXXXX;
> Trusted_Connection=yes; DATABASE=XXXXX; > PollInterval 5 > IdType timestamp > SQL SELECT event_time AS 'id', f., a.name AS action_name
> FROM fn_get_audit_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\XXXXX\Log\Audit-File.sqlaudit', default,
> default) AS f
> INNER JOIN sys.dm_audit_actions AS a
> ON f.action_id = a.action_id
> WHERE event_time > ? > <Exec> > delete($id); > rename_field($event_time, $EventTime); > </Exec> > > </Input>

The error I'm getting when starting NXlog is as follows:

2019-10-16 13:51:03 INFO nxlog-4.4.4431 started 2019-10-16 13:51:03 INFO im_odbc successfully connected to the database 2019-10-16 13:51:03 ERROR SQLExecDirect failed, 42000:2:300:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]CONTROL SERVER permission was denied on object 'server', database 'master'.; 42000:3:297:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The user does not have permission to perform this action. (odbc error code: -1)`

I have tried to enable of the permissions on the database (GRANT) but the CONTROL SERVER is throwing me off. I'm pretty sure is a stupid thing I haven't thought about, but since I am very new to NXlog, and obviously not a SQL admin, I am defeated! for now, so I'm hopeful someone, can help me with some guidance so I can troubleshoot further. TIA and happy hump day!

JC

Hello,

I am using NXLog EE with the im_odbc module to read application logs from an SQL database table.

After writing these logs to a file or forwarding them to a SIEM I seem to see random white space characters in various fields. Is there anyway possible where I can "cut out" this white space so it no longer appears in the log?

For example, in the below there is white space in the USERID field after sa. Example log here:

<13>Jan 4 16:32:56 PAGBSSC1SQL032 2022-01-04 16:32:56 PAGBSSC1SQL032 INFO id="63548" INDEX1="1" PRODNAME=" " CMPNYNAM="ABF plc " USERID="sa " INQYTYPE="2" DATE1="2022-01-04 00:00:00" SECDESC="Successful Attempts to Log In " DEX_ROW_ID="63548"

Here is my NX Log EE config file:

define INSTALLDIR C:\Program Files\nxlog

#ModuleDir %INSTALLDIR%\modules #CacheDir %INSTALLDIR%\data #SpoolDir %INSTALLDIR%\data

define CERTDIR %INSTALLDIR%\cert define CONFDIR %INSTALLDIR%\conf\nxlog.d

Note that these two lines define constants only; the log file location

is ultimately set by the LogFile directive (see below). The

MYLOGFILE define is also used to rotate the log file automatically

(see the _fileop block).

define LOGDIR %INSTALLDIR%\data define MYLOGFILE %LOGDIR%\nxlog.log

If you are not using NXLog Manager, disable the include line

and enable LogLevel and LogFile.

include %CONFDIR%*.conf

LogLevel INFO LogFile %MYLOGFILE%

<Extension _syslog> Module xm_syslog </Extension>

This block rotates %MYLOGFILE% on a schedule. Note that if LogFile

is changed in managed.conf via NXLog Manager, rotation of the new

file should also be configured there.

<Extension _fileop> Module xm_fileop

# Check the size of our log file hourly, rotate if larger than 5MB
<Schedule>
    Every   1 hour
    <Exec>
        if ( file_exists('%MYLOGFILE%') and
             (file_size('%MYLOGFILE%') >= 5M) )
        {
             file_cycle('%MYLOGFILE%', 8);
        }
    </Exec>
</Schedule>

# Rotate our log file every week on Sunday at midnight
<Schedule>
    When    @weekly
    Exec    if file_exists('%MYLOGFILE%') file_cycle('%MYLOGFILE%', 8);
</Schedule>

</Extension>

<Input odbc> Module im_odbc ConnectionString DSN=NXLog; Driver={ODBC Driver 17 for SQL Server}; Server=PAGBSSC1SQL032;
Trusted_Connection=yes; Database=DYNAMICS IdType integer SQL SELECT DEX_ROW_ID AS id, * FROM DYNAMICS.dbo.SY05000 WHERE DEX_ROW_ID > ? PollInterval 5 Exec delete($id); Exec if not ($raw_event =~ /sa/) drop (); </Input>

<Output udp> Module om_udp Host 10.180.13.28:514 Exec to_syslog_bsd(); </Output>

<Route transfer> Path odbc => udp </Route>

Any help would be greatly appreciated!

TIA

Hello, I'm trying to query the oracle sys.aud$ table using the nxlog odbc input module (Oracle 12.1.0.2.0) for new audit-events. I'm using NXlog and the odbc module 4.3.4308. The Error that NXLog is presenting me is: ERROR SQLDescribeParam returned zero parameter_size or decimal_digit(999, 0)

I'm querying the table as follows:

<Input input-asdf> Module im_odbc SQL select NTIMESTAMP# AS id, SESSIONID, ENTRYID, STATEMENT, TIMESTAMP#, USERID, USERHOST, TERMINAL, ACTION#, RETURNCODE, OBJ$CREATOR, OBJ$NAME, AUTH$PRIVILEGES, AUTH$GRANTEE, NEW$OWNER, NEW$NAME, SES$ACTIONS, SES$TID, LOGOFF$LREAD, LOGOFF$PREAD, LOGOFF$LWRITE, LOGOFF$DEAD, LOGOFF$TIME, COMMENT$TEXT, CLIENTID, SPARE1, SPARE2, OBJ$LABEL, SES$LABEL, PRIV$USED, SESSIONCPU, NTIMESTAMP#, PROXY$SID, USER$GUID, INSTANCE#, PROCESS#, XID, AUDITID, SCN, DBID, SQLBIND, SQLTEXT, OBJ$EDITION FROM sys.aud$ WHERE NTIMESTAMP# > ? order by NTIMESTAMP# ASC;

ConnectionString DSN=asdf;uid=fdsa;pwd=fdsa;database=asdf SavePos TRUE MaxIdSQL select MAX(NTIMESTAMP#) as maxid from sys.aud$ PollInterval 900 IdType Timestamp

</Input>

I'm not sure if the NTIMSTAMP# column is in the correct format, so I tried casting it using TO_DATE(TO_CHAR(NTIMESTAMP#, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') into a datetime since according to documentation the NTIMESTAMP is from the type Timestamp(6). If it is better to query using another Parameter I'm also happy to use another parameter from the sys.aud$ table!

Also I had the problem before that when restarting the NXLog agent some data would be queried again. I figured that this should be resolved with the MaxIdSQL parameter, however I couldn't verify that yet.

Best regards

Hello,

I hope someone can help me. I need to pick application logs from a few MSSQL database tables. 3 of 4 works perfect -there are no issues getting the data, but one table is causing issues.

Below you can see input I am using. As you can see, there is one column name with the Danish character, however, I am not sure if only this is causing trouble. The error I receive is:

ERROR SQLExecDirect failed, 42000:2:102:[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'Ą'.; 42000:3:319:[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a s (odbc error code: -1)

However, If I remove this column from SELECT, another error occurs:
ERROR id column not found or its type is unsupported.

I received a similar error than I left some additional spaces in the query, but after that, I used notepad++ to make sure I don't leave any hidden characters.

=========================================================
</Input>

<Input odbc_besked>
Module im_odbc
ConnectionString Driver={SQL Server Native Client 11.0};Server=SERVERNAME\mssql2014,port;Database=database_name;Trusted_Connection=yes;
SQL SELECT BeskedType, \
Oprettet, \
InternBeskedId, \
SvarPåInternBeskedId, \
Retning, \
BeskedArt, \
SoapBeskedId, \
BeskedId, \
HttpStatusCode, \
Fra, \
Til, \
Failover, \
EnvelopeStartTag, \
SoapHeaderElement, \
IndeholderSoapFault, \
Stack \
FROM table_name WITH (NOLOCK) WHERE InternBeskedId > ?

</Input>

Hi!

I have a problem using module im_odbc. I use sql query to receive some data from sql server. For example, query looks like "SELECT [Name] From [MyTable] WHERE [Id] > ?". In some moment, the "?" has stored incorrect value (I don't know how, but it's bigger then MAX(Id) value in my table) and sinse that moment, query has not return any data. 

Please, help me reset value of "?" (nxlog service restart not help). And, maybe, you can give me some recommendations, how to avoid this problem in future?

 

UPD: I reset value of "?" by renaming input name in config file. The first query after input renaming was executed with "0" as value of "?" and return all rows from table. But next time query executed with "?"-value "161027120023", wich bigger than max value of "Id" column in my table (max value was 161027115813 at that time). What am i doing wrong?

<Input odbc1>
    Module                im_odbc
    ConnectionString    DSN=im_odbc_test;database=MyDB;
    SQL                    SELECT [Name] From [MyTable] WHERE [Id] > ?;
    PollInterval        120
    SavePos                TRUE
    IdIsTimestamp        FALSE
</Input>