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 &lt;Schedule&gt; Every 1 hour &lt;Exec&gt; if ( file_exists('%MYLOGFILE%') and (file_size('%MYLOGFILE%') &gt;= 5M) ) { file_cycle('%MYLOGFILE%', 8); } &lt;/Exec&gt; &lt;/Schedule&gt; # Rotate our log file every week on Sunday at midnight &lt;Schedule&gt; When @weekly Exec if file_exists('%MYLOGFILE%') file_cycle('%MYLOGFILE%', 8); &lt;/Schedule&gt; </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 , Trying to create an ODBC connect for NXLog to connect to.   NXLog is installed on the same Windows 2012 server as the SQL Server 2008R2 instance.   Scenario 1: 32-bit ODBC is setup as a System DSN with a SQL Server account that has DBO access to the desired database NXLog service is setup to run under the System account.      - I've tried both drivers available on the system ("SQL Server Native Client 10.0"  and "SQL Server") - get the same result in the error log for each: ERROR im_odbc couldn't connect to the database, 28000:2:18456:[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ''. (odbc error code: -1)and ERROR im_odbc couldn't connect to the database, 28000:2:18456:[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. (odbc error code: -1)   Scenario 2: Same ODBC, but with a Windows account that has full Admin access to the desired databases, and is the same account logged into Windows NXLog service is setup to run under this same account. Goal is to have the same user account accessing everything, in the hope of getting it to connect.   Same error messages as above.   Login failed for user ' '.     Since the error messages don't show the user that is failing to login, I'm having trouble narrowing down where the failure is at.   NXLOG.conf file: <Input call_logs> Module im_odbc ConnectionString DSN=SIEM_NXLog;database=recorder; SQL SELECT ident as id ,at.audit_time as EventTime ,am.audit_module_name as Message FROM mytables...  WHERE at.ident>? SavePos TRUE </Input>   There's one line in the documentation that has me scratching my head: SECTION 6.2.18 (ODBC) The data source must be accessible by the user which nxlog is running under.   I'm not sure if this means that the NTService account needs database access? Or, if the service must be under a Windows account user that has database access? Or, by using a ODBC->System DSN , shouldn't the ODBC already be accessible to all users on the system?   Any thoughts or insight would be helpful. Thanks in advance.   Cheers,  Peter          

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>