1
response

Hello,

I have set up windows 2012R2+SQL2014 environment to verify the im_obdc function, now it is working to gather data from database, but there are two problems encountered.

1. How can I limit the rows to fetch from table every time ? if there is no limitation, it will affect the database performance at the first time .
2. I have a ID in SQL statement , and followed the instruction to have "where id > ? " in SQL statement. but nxlog always execute the same SQL statement and return same data to nxlog agent.

The following are configuration of input and SQL statement from SQL profiler

Configuration of input
==================
<Input in>
Module im_odbc
ConnectionString DSN=SEPM;uid=sem5;pwd=Admin123;database=sem5
#MaxIdSQL SELECT MAX(TIME_STAMP) from V_ALERTS
#ReadFromLast True
SQL SELECT a.USN, a.ALERT_IDX, a.SOURCE, a.VIRUSNAME_IDX, a.NOOFVIRUSES, a.FILEPATH, a.DESCRIPTION, a.ACTUALACTION_IDX, a.REQUESTEDACTION_IDX, a.ALERTDATETIME, a.USER_NAME, a.SOURCE_COMPUTER_NAME, a.SOURCE_COMPUTER_IP, a.TIME_STAMP, a.SOURCE_COMPUTER_IP_TEXT, v.VIRUSNAME, v.TYPE, PAT.VERSION, PAT.SEQUENCE, LOWER(S.NAME), LOWER(G.NAME), LOWER(P.NAME), Q.NAME, I.COMPUTER_DOMAIN_NAME, I.COMPUTER_NAME, I.CURRENT_LOGIN_USER, I.CURRENT_LOGIN_DOMAIN, I.IP_ADDR1_TEXT, I.MAC_ADDR1, I.OS_LANG, I.DISK_TOTAL, I.MEMORY, I.OPERATION_SYSTEM, I.SERVICE_PACK, I.BIOS_VERSION, SA.AGENT_VERSION, SA.AGENT_TYPE, SA.PROFILE_VERSION, SA.STATUS, SA.LAST_UPDATE_TIME, SA.INFECTED, SA.WORSTINFECTION_IDX, SA.LAST_VIRUS_TIME, SA.LAST_SCAN_TIME, SA.LAST_DOWNLOAD_TIME, SA.CONTENT_UPDATE, SA.PROFILE_SERIAL_NO, SA.MAJOR_VERSION, SA.LICENSE_STATUS, SA.LICENSE_EXPIRY FROM V_ALERTS a with (NOLOCK) LEFT JOIN VIRUS v ON a.VIRUSNAME_IDX = v.VIRUSNAME_IDX LEFT JOIN V_SEM_COMPUTER I ON I.COMPUTER_ID = a.COMPUTER_IDX LEFT JOIN SEM_AGENT SA ON I.COMPUTER_ID = SA.COMPUTER_ID LEFT JOIN IDENTITY_MAP S ON SA.DOMAIN_ID = S.ID LEFT JOIN IDENTITY_MAP G ON SA.GROUP_ID = G.ID LEFT JOIN IDENTITY_MAP P ON SA.LAST_SERVER_ID = P.ID LEFT JOIN IDENTITY_MAP Q ON SA.LAST_SITE_ID = Q.ID LEFT JOIN PATTERN PAT ON SA.PATTERN_IDX = PAT.PATTERN_IDX WHERE a.TIME_STAMP > ? ORDER BY a.TIME_STAMP
#SavePos True
PollInterval 30
</Input>

SQL Statement from Profiler
=======================
exec sp_executesql N'SELECT a.USN, a.ALERT_IDX, a.SOURCE, a.VIRUSNAME_IDX, a.NOOFVIRUSES, a.FILEPATH, a.DESCRIPTION, a.ACTUALACTION_IDX, a.REQUESTEDACTION_IDX, a.ALERTDATETIME, a.USER_NAME, a.SOURCE_COMPUTER_NAME, a.SOURCE_COMPUTER_IP, a.TIME_STAMP, a.SOURCE_COMPUTER_IP_TEXT, v.VIRUSNAME, v.TYPE, PAT.VERSION, PAT.SEQUENCE, LOWER(S.NAME), LOWER(G.NAME), LOWER(P.NAME), Q.NAME, I.COMPUTER_DOMAIN_NAME, I.COMPUTER_NAME, I.CURRENT_LOGIN_USER, I.CURRENT_LOGIN_DOMAIN, I.IP_ADDR1_TEXT, I.MAC_ADDR1, I.OS_LANG, I.DISK_TOTAL, I.MEMORY, I.OPERATION_SYSTEM, I.SERVICE_PACK, I.BIOS_VERSION, SA.AGENT_VERSION, SA.AGENT_TYPE, SA.PROFILE_VERSION, SA.STATUS, SA.LAST_UPDATE_TIME, SA.INFECTED, SA.WORSTINFECTION_IDX, SA.LAST_VIRUS_TIME, SA.LAST_SCAN_TIME, SA.LAST_DOWNLOAD_TIME, SA.CONTENT_UPDATE, SA.PROFILE_SERIAL_NO, SA.MAJOR_VERSION, SA.LICENSE_STATUS, SA.LICENSE_EXPIRY FROM V_ALERTS a with (NOLOCK) LEFT JOIN VIRUS v ON a.VIRUSNAME_IDX = v.VIRUSNAME_IDX LEFT JOIN V_SEM_COMPUTER I ON I.COMPUTER_ID = a.COMPUTER_IDX LEFT JOIN SEM_AGENT SA ON I.COMPUTER_ID = SA.COMPUTER_ID LEFT JOIN IDENTITY_MAP S ON SA.DOMAIN_ID = S.ID LEFT JOIN IDENTITY_MAP G ON SA.GROUP_ID = G.ID LEFT JOIN IDENTITY_MAP P ON SA.LAST_SERVER_ID = P.ID LEFT JOIN IDENTITY_MAP Q ON SA.LAST_SITE_ID = Q.ID LEFT JOIN PATTERN PAT ON SA.PATTERN_IDX = PAT.PATTERN_IDX WHERE a.TIME_STAMP > @P1 ORDER BY a.TIME_STAMP',N'@P1 bigint',2664642240

Thanks in advanced.

AskedSeptember 2, 2018 - 5:59am

Answer (1)

  1. I believe the ODBC driver handles the limit internally with cursors. Are you seeing any issues?
  2. You need to make soure your SELECT returns an id column in the result set.