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 :
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!
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.
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.
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 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
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.
Reference the agent.out.wrapper in my previous post. I forgot to add what how the time stamp column is configured:
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.
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.
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.
Thank you for your help so far.
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