ALERT! The community will be read-only starting on April 19, 8am Pacific as the migration begins. Read more for important details.
ALERT! The community will be read-only starting on April 19, 8am Pacific as the migration begins.Read more for important details.
Commander
Commander
258 views

Time based connector flex - SQL server - error Failed to process the Query

Jump to solution

I´ve set in the configuration file: test.sdktbdatabase.properties, according to below, but after stopping and starting the connector, the query error appear:

INFO | jvm 1 | 2021/03/31 18:27:09 | Failed to process query [[SELECT [login_time] as timestamp, [instance] as Servidor, username, client_hostname, [database_name] as db, client_app_name ] for [Wed Mar 31 18:27:08 BRT 2021]/[1617226028668]] for [jdbc:sqlserver://srvcentraldb\centraldb:1433;databasename=adm_database].
INFO | jvm 1 | 2021/03/31 18:27:09 | [Wed Mar 31 18:27:09 BRT 2021] [INFO ] Transport initialization [3F8iciHgBABDQ0HKadyt-bg==] complete. Done in [1000] ms.
INFO | jvm 1 | 2021/03/31 18:27:10 | [Wed Mar 31 18:27:10 BRT 2021] [INFO ] First event from [ArcSight|ArcSight|10.210.24.40|coletoresspo.intra.spobrz.br] received.

####################
content of configuration file: test.sdktbdatabase.properties:

version.order=1

query = SELECT [login_time] as timestamp, [instance] as Server, username, client_hostname, [database_name] as db, client_app_name

FROM [ADM_DATABASE].[COLLECT].[LOGON_TRACK]

ORDER BY login_time

timestamp.field=timestamp

uniqueid.fields=timestamp

######

Does the above query using SELECT is not acceptable for the flex connector? I am asking because the query works fine in a DBeaver (DataBase client)..

Thank you for any suggestion in advance,

1 Solution

Accepted Solutions
Commander
Commander

hi, sorry for delaying to answer, I wasn't well with my health, but today I am little better.

Thank you so much for answering, about the question related with integrated authentication and name resolution, I will check them,
I am using the local user created in the Database to connect to DB.

about quick look,
- I will fix the jdbc url
- I will remove the square brackets from query fields
- I will add "WHERE" part to configuration file
- about the error the query in the config file, it is running in the operation system in English, therefore, I didn´t translate anything.

I will perform the check recommended, and I will inform the results,

the errors in the ~/current/log/agent.log related with queries are:
[2021-04-07 10:31:25,664][FATAL][default.com.arcsight.agent.sdk.d.e.z][processQuery()] Failed to process query [[SELECT [LOAD_DATE], [ID_LOGON_TRACK] ] for [Wed Dec 31 21:00:29 BRT 1969]/[29768]] for [jdbc:sqlserver://srvcentraldb\centraldb:1433;databasename=adm_bdados].
[2021-04-07 10:31:30,771][FATAL][default.com.arcsight.agent.sdk.d.e.z][processQuery()]
com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:714)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:723)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setLong(SQLServerPreparedStatement.java:893)
--
[2021-04-07 10:31:30,771][FATAL][default.com.arcsight.agent.sdk.d.e.z][processQuery()] Failed to process query [[SELECT [LOAD_DATE], [ID_LOGON_TRACK] ] for [Wed Dec 31 21:00:29 BRT 1969]/[29768]] for [jdbc:sqlserver://srvcentraldb\centraldb:1433;databasename=adm_bdados].
[2021-04-07 10:31:35,866][FATAL][default.com.arcsight.agent.sdk.d.e.z][processQuery()]
com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:714)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:723)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setLong(SQLServerPreparedStatement.java:893)

thank you very much

View solution in original post

3 Replies
Knowledge Partner Knowledge Partner
Knowledge Partner

hi

is there anything else in the logs - can it resolve the shortname "srvcentraldb", should it be using integrated Authentication?

From a quick look there are a few things here to check:

  • your JDBC Connection is already connecting to adm_database - so you can drop that from the query
  • should the JDBC Connection read:
    • jdbc:sqlserver://srvcentraldb/centraldb:1433;databasename=adm_database
  • Remove the square brackets from the query fields - shouldnt be needed and not sure they work in a Flex
  • there should be a "WHERE" part of the query to ensure the connector only pulls events since the last connection - i.e.
    • where timestamp >= ? 
  • The error and the query in the config file dont match - but i am assuming that is because you have translated to English for us?

Additionally, i would probably add more fields to the uniqueid fields - to make sure that multiple events with the same timestamp are not dropped as duplicates.

Let us know how you get on...

Commander
Commander

hi, sorry for delaying to answer, I wasn't well with my health, but today I am little better.

Thank you so much for answering, about the question related with integrated authentication and name resolution, I will check them,
I am using the local user created in the Database to connect to DB.

about quick look,
- I will fix the jdbc url
- I will remove the square brackets from query fields
- I will add "WHERE" part to configuration file
- about the error the query in the config file, it is running in the operation system in English, therefore, I didn´t translate anything.

I will perform the check recommended, and I will inform the results,

the errors in the ~/current/log/agent.log related with queries are:
[2021-04-07 10:31:25,664][FATAL][default.com.arcsight.agent.sdk.d.e.z][processQuery()] Failed to process query [[SELECT [LOAD_DATE], [ID_LOGON_TRACK] ] for [Wed Dec 31 21:00:29 BRT 1969]/[29768]] for [jdbc:sqlserver://srvcentraldb\centraldb:1433;databasename=adm_bdados].
[2021-04-07 10:31:30,771][FATAL][default.com.arcsight.agent.sdk.d.e.z][processQuery()]
com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:714)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:723)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setLong(SQLServerPreparedStatement.java:893)
--
[2021-04-07 10:31:30,771][FATAL][default.com.arcsight.agent.sdk.d.e.z][processQuery()] Failed to process query [[SELECT [LOAD_DATE], [ID_LOGON_TRACK] ] for [Wed Dec 31 21:00:29 BRT 1969]/[29768]] for [jdbc:sqlserver://srvcentraldb\centraldb:1433;databasename=adm_bdados].
[2021-04-07 10:31:35,866][FATAL][default.com.arcsight.agent.sdk.d.e.z][processQuery()]
com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:714)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:723)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setLong(SQLServerPreparedStatement.java:893)

thank you very much

View solution in original post

Commander
Commander

Hello, kevquinlan,

thank you for all information sent, after reviewing and changing several part inside of flex connector configuration file, the query was executed with success.

and important point was the documentation, “FlexConn_DevGuide.pdf”, that indicated several Mandatory fields (on page 85, 86, 87), such as on of you commented: "WHERE".

therefore, thank you so much about quick look sent.

regards,

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.