Highlighted
Absent Member.
Absent Member.
844 views

Invalid Column Index - Time based Flexconnector - Error

Dear All,

I'm trying to install - time based flex conenctor, once i connected - getting fatal exception as Invalid column index in logs.

I've selected - connector type = Arcsight Time based flex connector . 

Below listed my SDK.properties files

--------------------------------------------------------------------------------------------------------------

version.order=1

query=select a.user_name,b.message,b.log_date from fnd_user a,WWSSO_AUDIT_LOG_TABLE b where a.end_date is  null and b.user_name=a.user_name and b.user_name not like '5%' and trunc(b.log_date)=trunc(sysdate);

uniqueid.fields=b.log_date

timestamp.field=b.log_date

additionaldata.enabled=true

event.endTime=b.log_date

event.destinationUserName=a.user_name

event.deviceReceiptTime=a.end_date

event.name=b.message

event.deviceProduct=__stringConstant("ERP")

--------------------------------------------------------------------------------------------------------------

Please let me know any changes needs to be done here!

regards

Santhosh I

Labels (3)
Tags (2)
0 Likes
Reply
7 Replies
Highlighted
Absent Member.
Absent Member.

Please find the error's which displaying on log files!!

[Sun Dec 02 15:32:19 AST 2012] [ERROR] Device connection to [jdbc:oracle:thin:@1

72.30.0.76:1526:KTCAPPS|cviv0406] down.(Invalid column index)

FATAL EXCEPTION:

java.sql.SQLException: Invalid column index

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.ja

va:70)

        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:1

12)

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java

:173)

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java

:229)

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java

:403)

        at oracle.jdbc.driver.OraclePreparedStatement.setTimestampInternal(Oracl

ePreparedStatement.java:6704)

        at oracle.jdbc.driver.OraclePreparedStatement.setTimestamp(OraclePrepare

dStatement.java:6691)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setTimestamp(Oracle

PreparedStatementWrapper.java:301)

        at com.arcsight.agent.sdk.b.h.e.a(e.java:444)

        at com.arcsight.agent.sdk.b.h.e.a(e.java:434)

        at com.arcsight.agent.sdk.b.h.z.a(z.java:235)

        at com.arcsight.agent.sdk.b.h.z.run(z.java:731)

        at java.lang.Thread.run(Thread.java:662)

FATAL EXCEPTION:

Failed to process query [[select a.user_name,b.message,b.log_date from fnd_user

a,WWSSO_AUDIT_LOG_TABLE b where a.end_date isá null and b.user_name=a.user_name

and b.user_name not like '5%' and trunc(b.log_date)=trunc(sysdate);] for [Sun De

c 02 15:32:18 AST 2012]/[1354451538744]] for [jdbc:oracle:thin:@172.30.0.76:1526

:KTCAPPS].

------------------------------------------------

regards

Santhosh I

0 Likes
Reply
Highlighted
Honored Contributor.
Honored Contributor.

Hi Santhosh,

Can you try this query and let me know your results. This error pops up when you are trying to select something which is not there. Also please try to run this query direcly on db and fine tune from there. If it works there please export the content as CSV and post it here.

select a.user_name,b.message,b.user_name,a.end_date,b.log_date from fnd_user a,WWSSO_AUDIT_LOG_TABLE b where a.end_date is  null and b.user_name=a.user_name and b.user_name not like '5%' and trunc(b.log_date)=trunc(sysdate)


Regards,

Aneesh Salimkumar

0 Likes
Reply
Highlighted
Absent Member.
Absent Member.

Hi Aneesh,

Is that possible, that we can use single query to fetch logs form 2 different tables,, I tried below query!

query = select a.user_name,b.message,b.log_date,a.end_date \

from fnd_user a,WWSSO_AUDIT_LOG_TABLE b \

where b.log_date >=?

this works fine, but giving only one log once I started the connector, after that no logs were fetching out!!

regards

Santhosh I

0 Likes
Reply
Highlighted
Honored Contributor.
Honored Contributor.

Dear Santhosh,

Sorry for the delay. I was not around for the two days. Yes you can use a join query to fetch logs. Did you checked the agent logs. Event might be dropped due to duplicates. Please upload the agent logs and configuration file. If the configuration is just the as you given above definitely that is the issue. Please take care of below things .

1) You don't have to give timestamp field as unique ID. By default timestamp field is the first unique ID field that is being tried. Then the connector will move to the unique filed if two events happens at similar timestamp field. If the unique ID filed is also same then the connector will drop the event considering it as duplicate. If this is the issue then you should find lines like this in the agent.log files Event with duplicate ID.

If event duplication is the real issue then the solution is simple. All you need to do is identify a filed that is unique to every row of results returned by the query. You can start with a.user_name may be. Try some thing like below.If still found duplicate events then you need to work more to find a good unique ID. You can give multiple Values separated by a comma in uniqueid.fields. If you can upload the confguration file and event export i may be able to help you.

Please let me know if this was helpful to you.

uniqueid.fields=a.user_name

timestamp.field=b.log_date

Regards,

Aneesh Salimkumar

0 Likes
Reply
Highlighted
Honored Contributor.
Honored Contributor.

Hi Santhosh,

Did you able to solve the issue. Please let me know if you need further assistance. If solved please mark the incident as solved so others can get be benefited from your post.

Regards,

Aneesh Salimkumar 

0 Likes
Reply
Highlighted
Absent Member.
Absent Member.

Hello Aneesh,

Sorry for the delay, Im was busy in traveling.

The issue got solved, able to view ERP logs on console, please find query below which worked fine.

Before:-

query = select a.user_name,b.message,b.log_date,a.end_date \

from fnd_user a,WWSSO_AUDIT_LOG_TABLE b \

where b.log_date >=?

After (working):-

query = select a.user_name,b.message,b.log_date,a.end_date from fnd_user a,WWSSO_AUDIT_LOG_TABLE b WHERE b.log_date >=?

timestamp.field=b.log_date

uniqueid.fields=b.log_date,a.user_nam

--------------------------------------------------------------

I wrote query in a single line!, and "WHERE" in uppercase and added these two fields in uniqueID! but i dont know which solved my issue, but anyhow its solved!!

regards

Santhosh I



0 Likes
Reply
Highlighted
Honored Contributor.
Honored Contributor.

Hi Santhosh,

You can provide queries in both ways. The first method you tried is more elegant looking than giving it all in one line. I think the issue was with unique id field. Lets make simple and correct. Both methods i.e. either you can give it all in one line or lines seperated by \ as you have given on your query are correct and will b e accepted by the connector

You can easily verify this by putting the below in your file. As i said timestamp filed is the first unique id field. You don't have give the timestamp field to the unique id field. If you can test this it will be great so that we can confirm the issue and make a correct answer and mark it as solved. This should also work if i am not wrong. By the way did you checked the agent logs for the error i mentioned. Please let me know if it is working. Also Let me know if my replies were helpful in guiding you to solve the issue.

query = select a.user_name,b.message,b.log_date,a.end_date \

from fnd_user a,WWSSO_AUDIT_LOG_TABLE b \

where b.log_date >=?

timestamp.field=b.log_date

uniqueid.fields=a.user_name

Regards,

Aneesh Salimkumar

0 Likes
Reply
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.