Ask questions. Get answers. Find technical product solutions from passionate experts in the NXLog community.
Can NXLOG module im_dbi use AD Service account to authenticate to DB read?
bane created
I am trying to read from a MSSQL DB with an AD service account. I know the im_dbi module supports local DB account for auth but not sure about how to leverage AD service account credentials.
bane created
NXLOG im_ODBC module to collect data from table of MSSQL
Sam_wang created
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.
Sam_wang created
IMDBI Setting configuration at load time and at use time
Tuxizm created
Hello
I was wondering if it is possible to have multiple instances of im_dbi at one time. It is important for access to my global variables. If it is possibe, then my question is: does NXLog make nx_im_dbi_conf_t for each module instance? Where I should store variables for module if I want to have per load visibility?
Tuxizm created
im_dbi & MS SQL
ghostcat created
Hello Team,
I'm looking to use nxlog to retrieve a table entries from MS SQL , To be clear I do not require any of the database server logging only the info contained within a few tables within a database on the server.
I have followed the setup and battled first under windows and then linux versions of nxlog. I am now trying to use libdbi-freetds without success after having out of memory upon using the mysql hooks.
Here is my im_dbi without the user and pass for security.
<Input DB01-elog>
Module im_dbi
SavePos TRUE
SQL SELECT AllXml FROM db_table
Driver freetds
Option host 192.168.148.227
Option port 49000 (non standard port changed from 1433)
Option username domain\username
Option password password
Option dbname database
</Input>
The SQL server im attempting to connect to is MS SQL Server 2014, I also attempted this using mysql however recieved out of memory from the libdbi during execution of the query.
The OS machine has 8GB of mem available currently upped from 4GB when the out of memory initially occured.
I'm secretly hoping someone has run into this before and has been able to successfully retrieve table data and log it from MS SQL Server 2014.
If not im happy to work with the team to resolution on this.
Many Thanks,
GC
ghostcat created