2176 views

ID Based SQL Flexconnector

Jump to solution

Hey All,

Was hoping for some help with creating an ID Based SQL Flexconnector.  I worked through all of the initial kinks and feel like I am quite close.

Attached is a snippet of the agent.log and agent.out.wrapper. Also attached is my properties file.  I am receiving this error :

java.lang.NullPointerException

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(JdbcOdbcPreparedStatement.java:1022)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setLong(JdbcOdbcPreparedStatement.java:568)

    at com.arcsight.agent.sdk.b.c.k.a(k.java:38)

    at com.arcsight.agent.sdk.b.c.s.e(s.java:251)

    at com.arcsight.agent.sdk.b.c.s.run(s.java:800)

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

I have hit a proverbial brick wall in attempting to troubleshoot.  I turned debug logs on the connector but they weren't of much help.  The error is quite vague.

Any help would be greatly appreciated!

Thanks!

Labels (2)
0 Likes
1 Solution

Accepted Solutions

Hey Guys,

First off HUGE THANK YOU to Alexander for all of the help you have provided. I greatly appreciate it!

Thank you Michael also!

I finally got everything working but I had to totally break the rules on this one.  Attached is my final properties file.

As the Timestamp column in SQL was formatted as char I was unable to use anything to convert to datetime

As the EntryID column did not increment properly I could not use that as my ID.

I CAST both EntryID and Timestamp as bigint to ensure I could have a workable format.

Timestamp became DT only for the purpose of converting to a readable date time stamp.

I then used the Timestamp column as my ID as it DID increment properly unlike the EntryID column.

I used the Action & ClientIPAddress as my UniqueID Fields.

I have verified using SQL Mgmt Studio the events are in the correct order with correct timestamp.


Thanks again!

View solution in original post

0 Likes
28 Replies
Vice Admiral
Vice Admiral

Hello Andrew.

Seing logs I see connector starts and even finds start id. Maybe you have problems with main select. Have you check this query with sql developers? You may also configure ODBC source and test your configuration files with my utility:

0 Likes
Vice Admiral
Vice Admiral

In addition to above post.

I recommend you to remove token section in .sdkibdatabase.properties (delete strings token.count and token[x]). Maybe you have problems with token[x].name.

Regards,

Alexander

0 Likes
Fleet Admiral
Fleet Admiral

Hi Andrew,

I confirm you have to follow Alexander second advice which is to remove all token information.

For all Database flex, you do not use them.

In analysing the logs, I have found a mistake (cf. below).

I do not know if this issue is the root cause of your main problem but you have to change it and try.

You have created an ID Flex Database thus id.field should be EntryID

and for maxid.query, you have to replace timestamp by EntryID.

Or you have to change the DB Flex Type by a Time DB Flex.

Thanks

Kind Regards

Michael

0 Likes

Thanks guys for the help. Much appreciated.

I removed the token declarations per Alexander's advice and changed to a Time-Based per Michael's advice also.

I was torn between which one to use but after thinking it out the Time-Based would better suit our needs.

I am now receiving another error related to the Timestamp field and unable to get the last date.

It appears that its maybe having issues parsing the "Timestamp" field?

Attached is my new properties file with old stuff commented out and a snippet from agent.out.wrapper

0 Likes

I have also tried changing timestamp.field=Timestamp to timestamp.field=SELECT max (Timestamp) FROM AuditLog but still the same error. 

0 Likes
Vice Admiral
Vice Admiral

Is agent.out.wrapper same as earlier file?

Can you post a piece of data to analyze?

Please, check the data type of "Timestamp" field. If it isn't TIMESTAMP data type (for example INTEGER or STRING), connector don't work. I don't recommend you using SELECT statements in "timestamp.field=...", it isn't like an id-based flex connector! Maybe it was the best solution to use id-based flex connector.

0 Likes

Reference the agent.out.wrapper in my previous post.  I forgot to add what how the time stamp column is configured:

20150915081623857

Removing the SELECT is easy enough.  With it there or not is still errors out on the time stamp field.  Looking at the logs its almost like it cant find the query for the time stamp.

0 Likes
Vice Admiral
Vice Admiral

What data type is used for fields as "20150915081623857" in your database? It is very strange format for timestamp, maybe it is integer.

0 Likes

So should it look like this? timestamp.field=INTEGER

I gave that a go and that didn't work either.

INFO   | jvm 1| 2015/09/16 08:56:02 | [Wed Sep 16 08:56:02 CDT 2015] [ERROR] Unable to get last date for [jdbc:odbc:MessageWay] with query [null] defaulting to system time [Wed Sep 16 08:56:02 CDT 2015].

This seems to be where its hanging. 

0 Likes
Vice Admiral
Vice Admiral

No, I did not mean it.

So lets begin start over.

1. You want to configure connector to receive information from DB. What database you use (Oracle, MySQL, MsSQL or other)?

2. First of all check your database connection from connector host and query (try to receive result of query "SELECT EntryID,Timestamp,Server,IPAddress,ClientIPAddress,UserName,SessionHash,Action FROM AuditLog WHERE AuditLog.Action Like 'Logon' ORDER BY Timestamp DESC", for Oracle you may use SQL Developer for example). With sql developer you may find data type for every field (for example EntryID - integer, UserName - string)

3. After that read guide for flex connector (FlexConn_DevGuideConfig) find section "Configuration Properties for a Time-based Database FlexConnector". You'll understand, that you missed >? in your select. All data type in mapping section MUST be the same as arcsight fields or you receive errors, this is the main problem for users.

4. If I can see a piece of data from this table my instructions be better.

0 Likes

Capture.PNG

Thank you for your help so far.

1) MsSQL

2) I attempted to use your tool (love it so far!) but had one question.

    -I have input everything that I know to fill in but the Test your SQL query button is greyed out

3) I have read the guide up and down trying to figure out my issue.  I am not a SQL guru so I wasn't aware the ">?" was needed.  Is that needed at the end of the statement?

4) See attached pic for screenshot of table. (Had to omit certain data)

     IP Address fields are of course IP's

     UserName field is a string

     SessionHash is also a string

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.