cancel
Showing results for 
Search instead for 
Did you mean: 
hlckalana
Level 7

SIEM Collector v11-Oracle Database issue

Hi Community,

We install McAFee SIEM Collector agent V11 for getting logs from Oracle database. We succefully installed Cilent. And we can get the connectivity till following step. But we can’t get any log to our SIEM. Debug file has been attached below. Please anyone can help on this issue will appreciate.

commercial_crdit_issue.png

DB admins in the environment are mentioning that below SELECT request is invalid.

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DIAG 1 OracleAccess::GetRecords Data Query: SELECT T_UM_USER.FIRST_NAME, to_char(T_UM_USER.UM_CHANGED_TIME, 'YYYY-MM-DD HH24:MISmiley FrustratedS') as UM_CHANGED_TIME, T_UM_USER.UM_ID, T_UM_USER.UM_REQUIRE_CHANGE, T_UM_USER.UM_SALT_VALUE, T_UM_USER.UM_TENANT_ID, T_UM_USER.UM_USER_NAME, T_UM_USER.UM_ID FROM CC_MAIN.T_UM_USER WHERE ((>='')) order by T_UM_USER.UM_ID

Please anyone can help on this issue will appreciate.

log file view as follow.

.................................................

..........................................................

............................................................<135> Aug 10 16:29:26 localhost SIEMCollector DEBUG 0 CollectorService::_init Getting Client creds for: siem

<135> Aug 10 16:29:26 localhost SIEMCollector DEBUG 0 CollectorService::_init and parent: SQL

<135> Aug 10 16:29:26 localhost SIEMCollector DEBUG 0 CollectorService::_init Got creds with impersonate: False

<134> Aug 10 16:29:26 192.168.200.85 SIEMCollector INFO 1 ClientWrapper::init Client initializing

<134> Aug 10 16:29:26 192.168.200.85 SIEMCollector INFO 1 ClientWrapper::init Client initialized

<134> Aug 10 16:29:26 localhost SIEMCollector INFO 0 CollectorService::_init Loaded client=oracle|{b8285741-4de7-439c-9053-45b011cf88fb}, host=192.168.200.85, dsid=1, debug=Diagnostic

<134> Aug 10 16:29:26 localhost SIEMCollector INFO 0 CollectorService::_init Initializing threadpool at size: 1

<134> Aug 10 16:29:26 localhost SIEMCollector INFO 0 CollectorService::_init Initializing MEF connections

<134> Aug 10 16:29:27 localhost SIEMCollector INFO 0 CollectorService::_init starting

<135> Aug 10 16:29:27 localhost SIEMCollector DEBUG 0 CollectorService::Work Assigning client[1] to worker

<134> Aug 10 16:29:27 192.168.200.85 SIEMCollector INFO 1 ClientWrapper::start Client started

<135> Aug 10 16:29:27 192.168.200.85 SIEMCollector DEBUG 1 MEFManager::GetConnection Activating connection: 1

<135> Aug 10 16:29:27 192.168.200.85 SIEMCollector DEBUG 1 MEFManager::GetConnection Active: 1

<135> Aug 10 16:29:27 192.168.200.85 SIEMCollector DEBUG 1 MEFClient::Begin connection: 1

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DEBUG 1 SqlBookmarkManager:Smiley FrustratedqlBookmarkManager Creating new Bookmark with: Plugins\{b8285741-4de7-439c-9053-45b011cf88fb} : bookmark

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DIAG 1 OracleAccess::GetMaxBookmarkValues Max Query: select max(UM_ID) from T_UM_USER

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DEBUG 1 SqlBookmarkManager:Smiley FrustratedqlBookmarkManager Get max bookmark query failed

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DIAG 1 OracleAccess::GetRecords Data Query: SELECT T_UM_USER.FIRST_NAME, to_char(T_UM_USER.UM_CHANGED_TIME, 'YYYY-MM-DD HH24:MISmiley FrustratedS') as UM_CHANGED_TIME, T_UM_USER.UM_ID, T_UM_USER.UM_REQUIRE_CHANGE, T_UM_USER.UM_SALT_VALUE, T_UM_USER.UM_TENANT_ID, T_UM_USER.UM_USER_NAME, T_UM_USER.UM_ID FROM CC_MAIN.T_UM_USER WHERE ((>='')) order by T_UM_USER.UM_ID

<131> Aug 10 16:29:28 192.168.200.85 SIEMCollector ERROR 1 SQLClient::GetNextRecordData Failed to retrieve next record

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DEBUG 1 ClientWrapper::start GetEventHandler returned false

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DEBUG 1 MEFClient::End connection: 1

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DEBUG 1 MEFManager::ReleaseConnection Releasing connection: 1

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DEBUG 1 MEFManager::ReleaseConnection Active: 0

<131> Aug 10 16:29:28 192.168.200.85 SIEMCollector ERROR 1 ClientWrapper::start The client returned false from GetData or UpdateBookmark and is being shutdown.

<134> Aug 10 16:29:28 192.168.200.85 SIEMCollector INFO 1 ClientWrapper::_shutdownClient Shutting down client

<134> Aug 10 16:29:28 192.168.200.85 SIEMCollector INFO 1 ClientWrapper::_shutdownClient Client shutdown

<134> Aug 10 16:29:47 localhost SIEMCollector INFO 0 CollectorService:Smiley SurprisednStop OnStop called

<134> Aug 10 16:29:47 localhost SIEMCollector INFO 0 CollectorService:Smiley SurprisednStop Deinit LPC

<131> Aug 10 16:29:47 localhost LPC ERROR 0 McAfeeAgent::_log DeInitializing LPC

<134> Aug 10 16:29:47 localhost LPC INFO 0 McAfeeAgent::_log Stopping LPC runtime monitor

<134> Aug 10 16:29:47 localhost LPC INFO 0 McAfeeAgent::_log Successfully released thread resources

..............................................................

..............................................................

<134> Aug 10 16:29:50 localhost SIEMCollector INFO 0 CollectorService::_init Initializing threadpool at size: 1

<134> Aug 10 16:29:50 localhost SIEMCollector INFO 0 CollectorService::_init Initializing MEF connections

<134> Aug 10 16:29:51 localhost SIEMCollector INFO 0 CollectorService::_init starting

<135> Aug 10 16:29:51 localhost SIEMCollector DEBUG 0 CollectorService::Work Assigning client[1] to worker

<134> Aug 10 16:29:51 192.168.200.85 SIEMCollector INFO 1 ClientWrapper::start Client started

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DEBUG 1 MEFManager::GetConnection Activating connection: 1

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DEBUG 1 MEFManager::GetConnection Active: 1

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DEBUG 1 MEFClient::Begin connection: 1

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DEBUG 1 SqlBookmarkManager:Smiley FrustratedqlBookmarkManager Creating new Bookmark with: Plugins\{b8285741-4de7-439c-9053-45b011cf88fb} : bookmark

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DIAG 1 OracleAccess::GetMaxBookmarkValues Max Query: select max(UM_ID) from T_UM_USER

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DEBUG 1 SqlBookmarkManager:Smiley FrustratedqlBookmarkManager Get max bookmark query failed

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DIAG 1 OracleAccess::GetRecords Data Query: SELECT T_UM_USER.FIRST_NAME, to_char(T_UM_USER.UM_CHANGED_TIME, 'YYYY-MM-DD HH24:MISmiley FrustratedS') as UM_CHANGED_TIME, T_UM_USER.UM_ID, T_UM_USER.UM_REQUIRE_CHANGE, T_UM_USER.UM_SALT_VALUE, T_UM_USER.UM_TENANT_ID, T_UM_USER.UM_USER_NAME, T_UM_USER.UM_ID FROM CC_MAIN.T_UM_USER WHERE ((>='')) order by T_UM_USER.UM_ID

<131> Aug 10 16:29:51 192.168.200.85 SIEMCollector ERROR 1 SQLClient::GetNextRecordData Failed to retrieve next record

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DEBUG 1 ClientWrapper::start GetEventHandler returned false

<135> Aug 10 16:29:51 192.168.200.85 SIEMCollector DEBUG 1 MEFClient::End connection: 1

...............................................................................

.......................................................................................

<131> Sep 05 12:34:50 localhost LPC ERROR 0 McAfeeAgent::_log DeInitializing LPC

<134> Sep 05 12:34:57 LKKKDMON01 SIEMCollector INFO 1 ClientWrapper::init Client initializing

<134> Sep 05 12:34:57 LKKKDMON01 SIEMCollector INFO 1 ClientWrapper::init Client initialized

<134> Sep 05 12:35:13 LKKKDMON01 SIEMCollector INFO 1 ClientWrapper::start Client started

<135> Sep 05 12:35:13 LKKKDMON01 SIEMCollector DEBUG 1 MEFManager::GetConnection Activating connection: 1

<135> Sep 05 12:35:13 LKKKDMON01 SIEMCollector DEBUG 1 MEFManager::GetConnection Active: 1

<135> Sep 05 12:35:13 LKKKDMON01 SIEMCollector DEBUG 1 MEFClient::Begin connection: 1

<135> Sep 05 12:35:14 LKKKDMON01 SIEMCollector DEBUG 1 SqlBookmarkManager:Smiley FrustratedqlBookmarkManager Creating new Bookmark with: Plugins\{a02a5743-c631-47f1-bd80-4e264cb579c3} : bookmark

<135> Sep 05 12:35:14 LKKKDMON01 SIEMCollector DIAG 1 OracleAccess::GetMaxBookmarkValues Max Query: select max(cus_code) from Emp_termination

<135> Sep 05 12:35:14 LKKKDMON01 SIEMCollector DEBUG 1 SqlBookmarkManager:Smiley FrustratedqlBookmarkManager Get max bookmark query failed

<135> Sep 05 12:35:14 LKKKDMON01 SIEMCollector DIAG 1 OracleAccess::GetRecords Data Query: SELECT Emp_termination.cus_code, Emp_termination.cus_code FROM TEST_USER.Emp_termination WHERE ((>='')) order by Emp_termination.cus_code

Config.xml file

<?xml version="1.0" encoding="UTF-8"?>

<EventCollectorConfig LogLevel="Error" MaxLogSize="20971520">

    <Credentials CredentialType="LocalCollector" Authenticated="true"/>

    <Receiver IPAddress="192.168.2.125" Port="8082" Encrypt="False" AdapterIPAddress="192.168.200.26"/>

    <HostGroup Name="Oracle" Enabled="true" UseParentLogging="false" LogLevel="Diagnostic">

        <Credentials CredentialType="OtherAccount" Authenticated="true" Username="siem" Password="AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAA+mUz7miDwkWJv2oJze5q6QQAAAACAAAAAAAQZgAAAAEAACAAAAAfmyG/6S9FlhT7E13BiuNsQ2ec63Yb7VCsf8ep9uZvugAAAAAOgAAAAAIAACAAAAC1JsQu8G9zNkXmbUbR3QxZh6u2uA0tdv4FiP4MWeU95xAAAACuCsDTlLDM/UvqRoncjCVJQAAAAEVv6zQ5MwvYB4gq3aO08ERlv31kTx//GiH9hIh2rARof/2pk1TG/lb4lC/KAqY+azIU3o2YD1P5++p57hUXWhM="/>

        <Host Enabled="true" LocalHost="false" Host="192.168.200.85" IsHostValid="true" UseParentLogging="true">

            <Credentials CredentialType="OtherAccount" Authenticated="true" Username="siem" Password="AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAA+mUz7miDwkWJv2oJze5q6QQAAAACAAAAAAAQZgAAAAEAACAAAADZAfehdUGN1BswedvpcsidUeg0AMoGnbJWpUuCnEHm8gAAAAAOgAAAAAIAACAAAACrvY4xAoYTG9usFvvWCAzD6tUfW3hI+06WPwqmtp2ytxAAAAAOLaK73nuGZ9momw0I+SgIQAAAAFrVUMI1+OL9ayi6V3t+zdcvuM0Ff2qW14wLtfLKWPPIG4y22qmMkONB1cugOQ8zrcV4dhTJLEBWZQJjS4jfWs4="/>

            <Client Enabled="true" IsClientValid="true" Name="Ora_Cust_Table" HostId="LKKKDMON01" ID="{a02a5743-c631-47f1-bd80-4e264cb579c3}" PluginType="Selectable" ClientType="SQL">

                <Configuration Key="ConfiguredTransType" Value="MEF"/>

                <SQLLogConfig SQLLogConfigVersion="v3" Origin="User">

                    <DataBaseAccess DataBaseType="Oracle Server" DataBasePort="1521" AuthenticationMode="Database Security" ServiceName="oratstdb" DataBaseSelected="TEST_USER" DataBaseCommunicationSecurity="Default"/>

                    <ESMDataStructure ESMStructureType="MEF"/>

                    <TableList>

                        <SelectedTableList>

                            <SelectedTableElement SelectedTable="Emp_termination"/>

                        </SelectedTableList>

                    </TableList>

                    <Mapping>

                        <OrderedMappingList>

                            <MappingElement CompleteFieldName="Emp_termination.cus_code" FieldName="cus_code" EsmFieldMapping="msg" DBDataType="2"/>

                        </OrderedMappingList>

                    </Mapping>

                    <Query>SELECT Emp_termination.cus_code, Emp_termination.cus_code FROM TEST_USER.Emp_termination</Query>

                    <BookmarkDBField CompleteBookmarkFieldName="Emp_termination.cus_code" BookmarkFieldName="cus_code" DBDataType="2" WhereBy="Complete" OrderBy="Complete"/>

                </SQLLogConfig>

            </Client>

        </Host>

    </HostGroup>

</EventCollectorConfig>

0 Kudos
8 Replies
proxima
Level 10

Re: SIEM Collector v11-Oracle Database issue

Hi,

What permissions the user has?

Regards

MK

0 Kudos
hlckalana
Level 7

Re: SIEM Collector v11-Oracle Database issue

Hi proxima,

We gave all permissions to user (Read, write and execute)

0 Kudos
proxima
Level 10

Re: SIEM Collector v11-Oracle Database issue

hmm so actually you're using sysdba role?

What about select any table?

DB admin said that this query is invalid - what exaclty?

Did you tried to check it from some SQl client (for example SQL Dev)?

Regards

MK

0 Kudos
hlckalana
Level 7

Re: SIEM Collector v11-Oracle Database issue

Hi proxima,

SELECT T_UM_USER.FIRST_NAME, to_char(T_UM_USER.UM_CHANGED_TIME, 'YYYY-MM-DD HH24:MISmiley FrustratedS') as UM_CHANGED_TIME, T_UM_USER.UM_ID, T_UM_USER.UM_REQUIRE_CHANGE, T_UM_USER.UM_SALT_VALUE, T_UM_USER.UM_TENANT_ID, T_UM_USER.UM_USER_NAME, T_UM_USER.UM_ID FROM CC_MAIN.T_UM_USER WHERE ((>='')) order by T_UM_USER.UM_ID

The above query is auto generated by SIEM Collector. We want to fill only the above shown (in question) diagram.

Yes we tried SQL client. Above query was not working because of   where clause (.... FROM CC_MAIN.T_UM_USER WHERE ((>='')) order by T_UM_USER.UM_ID).

We think this query will be generated by .xml file

0 Kudos
hlckalana
Level 7

Re: SIEM Collector v11-Oracle Database issue

WHERE ((>='')) order by T_UM_USER.UM_ID
in this where clause there is no any parameters between ">=" sign. So the Where clause is totally incorrect

0 Kudos
lratcliffe
Level 7

Re: SIEM Collector v11-Oracle Database issue

Indeed.  This has happened because of the preceding two lines:

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DIAG 1 OracleAccess::GetMaxBookmarkValues Max Query: select max(UM_ID) from T_UM_USER

<135> Aug 10 16:29:28 192.168.200.85 SIEMCollector DEBUG 1 SqlBookmarkManager:Smiley FrustratedqlBookmarkManager Get max bookmark query failed

Because the bookmark has not filled in correctly, the automatically generated query doesn't make sense.

hlckalana
Level 7

Re: SIEM Collector v11-Oracle Database issue

Hi Iracliffe,

According to our scenario  "Id" has both Numbers and characters (eg- id=930970095V) in oracle database data type. And username has only characters in data type of database (eg-username=Kalana).

So what do you think to put most suitable Mapping parameters for these type?? Could you please help me in this matter will be much appreciated...  

0 Kudos
mehmetemin
Level 7

Re: SIEM Collector v11-Oracle Database issue

Hi ​;

Have you solved this issue?

Thanks.

0 Kudos