Time Based Flex Connector (Oracle)

[VBanerjee]

Just wondering if someone can help me out. Here's the scoop. I have setup a time-based flex connector to pull rows from a very small Oracle table. However, when I run the agent, there seems to be a mismatch of the date parameter. The Oracle schema is a Date field. Can I get some assistance? I have included the parser and db schema. Here is what I am seeing in the logs (debug mode on):

[2010-01-29 15:39:28,931][FATAL][default.com.arcsight.agent.kf.d.c.q][processQuery] Unable to get the value for a unique id field [date_added] in fields [{DATE_ADDED=1263833231000, _DB_DSN=lawmgr_arcsight, LOGIN_NAME=xxxx, xxxxxx, _DB_URL=jdbc:odbc:lawmgr_arcsight, LOGIN_ACCOUNT_ID=req28220, _DB_DRIVER=sun.jdbc.odbc.JdbcOdbcDriver}]
[2010-01-29 15:39:28,931][DEBUG][default.com.arcsight.agent.kf.d.c.q][processHashMap] lastId=null
[2010-01-29 15:39:28,931][DEBUG][default.com.arcsight.agent.kf.d.c.q][processHashMap] Full HashMap :{DATE_ADDED=1100239200000, _DB_DSN=lawmgr_arcsight, LOGIN_NAME=XXXXXXXXX, _DB_URL=jdbc:odbc:lawmgr_arcsight, LOGIN_ACCOUNT_ID=xxxxxx, _DB_DRIVER=sun.jdbc.odbc.JdbcOdbcDriver}
[2010-01-29 15:39:28,931][DEBUG][default.com.arcsight.agent.kf.d.c.q][processHashMap] {DATE_ADDED=1100239200000, _DB_DSN=lawmgr_arcsight, LOGIN_NAME=XXXXXXXXX, LOGIN_ACCOUNT_ID=xxxxxx _DB_URL=jdbc:odbc:lawmgr_arcsight, _DB_DRIVER=sun.jdbc.odbc.JdbcOdbcDriver}
[2010-01-29 15:39:28,931][DEBUG][default.com.arcsight.agent.parsers.k][evaluate] Evaluating operation [com.arcsight.agent.parsers.operation.getVendorOperation@1c2ff68] with operands ["Oracle"]
[2010-01-29 15:39:28,931][DEBUG][default.com.arcsight.agent.parsers.k][evaluate] Evaluating operation [com.arcsight.agent.parsers.operation.stringConstantOperation@192df67] with operands ["Oracle-LawMgr"]
[2010-01-29 15:39:28,931][FATAL][default.com.arcsight.agent.kf.d.c.q][processQuery] Unable to get timestamp field [date_added] in fields [{DATE_ADDED=1100239200000, _DB_DSN=lawmgr_arcsight, LOGIN_NAME=XXXXXXXXX, _DB_URL=jdbc:odbc:lawmgr_arcsight, LOGIN_ACCOUNT_ID=xxxxxx, _DB_DRIVER=sun.jdbc.odbc.JdbcOdbcDriver}].
[2010-01-29 15:39:28,931][DEBUG][default.com.arcsight.agent.kf.d.c.q][processQuery] Query for [jdbc:odbc:lawmgr_arcsight] took: [0] rows processed: [64]

SQL> describe oclp000.clp_arcsight;
Name                                      Null?    Type
----------------------------------------- -------- ----------------
LOGIN_ACCOUNT_ID                              VARCHAR2(40)
LOGIN_NAME                                         VARCHAR2(255)
DATE_ADDED                                         DATE

lawmgr.sdktbdatabase.properties:

version.order=1
version.id=10.x
version.query=

query=SELECT date_added, login_account_id, login_name /
      FROM oclp000.clp_arcsight \
WHERE date_added > ? \
ORDER BY date_added

timestamp.field=date_added
uniqueid.fields=

event.endTime=date_added
event.deviceCustomString1=login_account_id
event.deviceCustomString2=login_name
event.deviceProduct=__stringConstant("Oracle-LawMgr")
event.deviceVendor=__getVendor("Oracle")

[tliu]

Hi Vinay - moving this thread to the Interact space, where most of the question/answer activity takes place.

Trisha

[ilia.tivin@hpe.]

Hey VBanerjee,

The uniqueid is an optional field, the field is for identifying two different events (that appear at the same time).

My suggestion is either dropping it, or configuring it as "uniqueid.field=LOGIN_ACCOUNT_ID,LOGIN_NAME".

The flex framework is a tricky one , you can try almost anything.

the thing that I might wonder is that if the "/" slash character you inserted is making the framework mad, try substituting it to a backslash ( "\" )

And last thing, if nothing helped... post an example of a row from the DB.

[VBanerjee]

Thanks for responding...I really appreciate it. After the suggestions, I am still receiving the same messages in the log. Here is an example of a few rows in the database. I formatted the date field to include the time.

SQL> SELECT TO_CHAR(DATE_ADDED,'MMDDYYYY HH24:MI:SS'), LOGIN_ACCOUNT_ID, LOGIN_NAME
  2  FROM X.CLP_ARCSIGHT;

TO_CHAR(DATE_ADDE LOGIN_ACCOUNT_ID                         LOGIN_NAME
----------------- ---------------------------------------- --------------------
03082005 08:17:03 reqxxxxx                                  Anderl, R
03082005 08:23:45 reqxxxxx                                  Huss, M
03082005 09:42:31 reqxxxxx                                  Hansen, L
03082005 08:22:32 reqxxxxx                                  Cook,
02022005 17:03:51 reqxxxxx                                  Reiss, J
05042009 09:20:56 tstxxxxx                                   Reiss, J

A straight select without date format;

SQL> select * from X.clp_arcsight;

LOGIN_ACCOUNT_ID                         LOGIN_NAME           DATE_ADDE
---------------------------------------- -------------------- ---------
reqxxxxx                                 Anderl, R         08-MAR-05
reqxxxxx                                  Huss, M           08-MAR-05
reqxxxxx                                  Hansen, L       08-MAR-05
reqxxxxx                                  Cook, K            08-MAR-05
reqxxxxx                                  Reiss, J          02-FEB-05
tstxxxxx                                  Reiss, J          04-MAY-09

[ilia.tivin@hpe.]

Well, I'd suggest leaving the date_added field as a Date one.

You can try the query: select to_date(date_added,'MM/DD/YYYY HH24:MI:SS'), LOGIN_ACCOUNT_ID, LOGIN_NAME FROM X.CLP_ARCSIGHT WHERE to_date(date_added,'MM/DD/YYYY HH24:MI:SS') => ?

I dont remember if the to_date will work on a date field.

btw, can you paste the log from the start of the process?

[VBanerjee]

Please see attached files. It looks like a format/parameter issue.

[ilia.tivin@hpe.]

Well...

Already seems better

now its only a ORA error.

Seems that it doesnt like my conversions.

try running this query in TOAD or the like:

SELECT to_date(date_added, 'MM/DD/YYYY HH24:MI:SS'), login_account_id, login_name FROM oclp000.clp_arcsight WHERE to_date(date_added, 'MM/DD/YYYY HH24:MI:SS') => to_date(to_char(?, 'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY HH24:MI:SS') ORDER By date_added

[cmatalia]

Hi,

Try populating the custom date fields(deviceCustomDate1) instead of endTime.

Thanks,

Chintan

[VBanerjee]

Thanks for assisting..I do appreciate it. The query below works in sqlplus when I force a date in, which is to be the parameter (?) in the parser.

What I need the agent to do is get past events, so I set the following parameter in the agent.properties file:

agents[0].startatdate=01/01/2004 00:00:00

select to_char(date_added, 'MM/DD/YYYY HH24:MI:SS'), login_account_id, login_name FROM oclp000.clp_arcsight
where date_added > to_date('01/01/2004 00:00:00','MM/DD/YYYY HH24:MI:SS');

TO_CHAR(DATE_ADDED, LOGIN_ACCOUNT_ID            LOGIN_NAME
------------------- ----------------------------------------                     -------------------------
03/08/2005 08:17:03         req66184                                 Anderl, Rich
03/08/2005 08:23:45         req62645                                 Huss, Mike
03/08/2005 09:42:31         req24439                                 Hansen, Laurie

However, when I use the same query using the parameter (?), ArcSight does not like it. There always seems to be a format problem. See attached log. What format is ArcSight passing in the timestamp.field parameter?

version.order=1

version.id=10.x

version.query=SELECT login_account_id from oclp000.clp_arcsight

query=select to_char(date_added, 'MM/DD/YYYY HH24:MI:SS'), login_account_id, login_name FROM oclp000.clp_arcsight \

where date_added >= to_date(?,'MM/DD/YYYY HH24:MI:SS') \

ORDER BY date_added

timestamp.field=date_added

uniqueid.fields=

event.deviceCustomDate1=date_added

event.deviceCustomString1=login_account_id

event.deviceCustomString2=login_name

event.deviceProduct=__stringConstant("Oracle-LawMgr")

event.deviceVendor=__getVendor("Oracle")

[cmatalia]

Try this query:

select date_added, login_account_id, login_name FROM oclp000.clp_arcsight where date_added >= '01/01/2004' ORDER BY date_added

[VBanerjee]

It seems you have to have the parameter (?) in the parser, otherwise you get a null pointer exception:

[2010-02-01 15:31:48,269][FATAL][default.com.arcsight.agent.kf.d.c.q][processQuery()]
java.lang.NullPointerException
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(JdbcOdbcPreparedStatement.java:1023)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setTimestamp(JdbcOdbcPreparedStatement.java:903)
at com.arcsight.agent.kf.d.c.db.a988591878(db.java:150)
at com.arcsight.agent.kf.d.c.q.g(q.java:200)
at com.arcsight.agent.kf.d.c.q.run(q.java:636)
at java.lang.Thread.run(Thread.java:619)

[VBanerjee]

Your query including the parameter (?) yields the same result as the original post:

select date_added, login_account_id, login_name FROM oclp000.clp_arcsight where date_added >= ? ORDER BY date_added