I am having a problem developing a connector for the TMG using the Databse ID based Flexconnector.
The root of the problem is that by default TMG creates a new database for each day. And the you have include that in the query. I have writen the below SQL query. This query first acceses the database for that day and the select the log which are then descended by time entered. It also uses a store procedure which converts the IP to the xxx.xxx.xxx.xxx form.
DECLARE @DATUM VARCHAR(8); SET @DATUM = CONVERT(VARCHAR(8),GETDATE(),112); DECLARE @BAZA VARCHAR(23); SET @BAZA = 'ISALOG_' + @DATUM + '_FWS_000' exec ('USE ' + @BAZA + ' select LogTime, dbo.fnIpAddressToText(SourceIP) AS [SourceIP], dbo.fnIpAddressToText(DestinationIP) AS [DestinationIP], dbo.fnIpAddressToText(OriginalClientIP) AS [OriginalClientIP], [DestinationNetwork], [DestinationPort], [protocol], [Action], [connectiontime], [SourceNetwork], [ClientUserName], [resultcode], [rule], [ApplicationProtocol], [Bidirectional], [bytessent], [bytessentDelta], [Payload], [bytesrecvd], [bytesrecvdDelta], [connectiontime], [connectiontimeDelta], [DestinationName], [ClientUserName], [ClientAgent], [sessionid], [connectionid], [Interface], [IPHeader], [Payload], [GmtLogTime], [ipsScanResult], [ipsSignature], dbo.fnIpAddressToText(NATAddress)as[NATAddress], [FwcClientFqdn], [FwcAppPath], [FwcAppSHA1Hash], [FwcAppTrusState], [FwcAppInternalName], [FwcAppProductName], [FwcAppProductVersion], [FwcAppFileversion], [FwcAppOrgFileName], [InternalServiceInfo], [ipsApplicationProtocol], [FwcVersion] FROM FirewallLog order by Logtime desc;')
I have also included the flex connector I am currently working on, it is located in the text file.
The problem I am having is that I am receiving JAva Null pointer exception, which I assume are related to the fact to how query works - or does not:).
When I check in SQL Visual studio, only the database version check query runs - on the user default database (Model) , but not on the current one.
The query made available by Ian, worked well with odbc driver. Unfortunately I had to interrupt the connector development for a while, therefore, since then I don´t make any progress. But, I guess, using Ian’s query and the arcsight manual for w3c tmg connector won’t be too hard to develop the connector.
Thanks for your post - I keep trying that query and variations of it, but I get an error each time - centered around the use of the ? in the query.
- When I tried the QUOTENAME(?,'''') that you have in the original query, I get this error: Conversion failed when converting date and/or time from character string.
- Changing it to QUOTENAME('?','''') returns this error: The index 1 is out of range. (since Java is using a PreparedStatement to process the ?)
- Changing it to CAST(? as varchar) returns this error: Incorrect syntax near '12' (this was at 12:52 PM, so Java wasn't able to parse the date properly).
- Changing it to QUOTENAME(CAST(? as varchar), '''')returns this error: Conversion failed when converting date and/or time from character string.
I've tried some other combinations, all fruitlessly. These were the most likely to work. Any ideas?
It sounds like the '?' substitution the connector is doing is not being recognised as a valid MSSQL timestamp format. As im surr you know the connector replaces the '?' with the time of thr last event, or if it is thr first connect the time now.
I haven't come across this problem before. What version of the framework and mssql odbc driver are you using? I have had problems with the very latest version of the odbc driver and mssql 2008 (although not like this)
Can you use the mssql debug tools to check the query being submitted by the connector, to see what the problem is?
I took another look at this, and I agree with you. Any idea what format the '?' substitution is supposed to return as?
I'm running the FlexCon on a Server 2008 R2 64-bit server, with a 32-bit version of MSSQL Server 2008 Express, and the Microsoft JDBC Driver (version 4.0).
I've been trying to debug in SQuirreL and Microsoft Server Management Studio, using the same driver/credentials, but I can't figure out how to replace the '?' with a comparable value. This whole problem centers around what value is returned when ArcSight Java replaces the '?' with a datetime. Every other part of the query works fine.
Note: when I test QUOTENAME(?,'''') in SQuirreL and MSMS, both return "Error: Incorrect syntax near '?'", requiring me to add single quotes around the question mark to get the query to process.
I do have a workaround, but I would prefer to use your more elegant solution. Any other advice you can provide would be greatly appreciated.
I am afraid I don't have access to the test rig at the moment. However, I think I would start by turning on debug mode in the agent, to see if the agent.log file shows you the query that the connector is actually issuing (including the ? substitution).
You turn on debug in the agent.properties file in the /current/user/agent directory by adding the following properties:
Let me know if this doesn't help. Good luck.
Thanks again for your help with this - the query works fine if I use the sun.jdbc.odbc.JdbcOdbcDriver instead of the Microsoft JDBC driver. However, I'm running into a new problem where I'm inexplicably seeing duplicate entries. Did you see this in your implementation?
I've included code snippets in the attachment to illustrate the problem. The same event is sent twice, after two different runs of the SQL query.
- SQL query runs with ? value 1382445410367
- Event found with logTime 1382445414877 (which is a larger, more recent time than 1382445410367)
- Event with unique ID |1382445414877|58349|http://csb.stanford.edu/class/public/pages/sykes_webdesign/05_simple.html|200 sent
- SQL query runs with ? value 1382445415940
- Event found with logTime 1382445414877 (which is a smaller, less recent time than 1382445415940 and should be invalid)
- Event with unique ID |1382445414877|58349|http://csb.stanford.edu/class/public/pages/sykes_webdesign/05_simple.html|200 sent, but this is the same ID as the event above, so it's sent twice!
Any idea why this would be happening?
Added attachment instead of code snippets in the post. Message was edited by: Jordan Anderson
I have looked through your log, and can see the duplication. But I don't know why it is happening. Could it be because the query row limit? Are your firewalls producing a very high EPS? I wonder if the duplication might be a second batch of events from the previous query, but its just a guess and it shouldn't be happening!
How often are you getting duplicate event? Do you have persistence, or startat proprties enabled in the agent.properties file? Neither should matte, but it might be worth experimenting?
In you flex connector do you have uniqueid setting? The connector framework has the ability to identify duplicate events with the same timestamp by defining a list of fields that the connector can use to check for duplicate events:
So in your case, if we get an event with the same timestamp as an event from the previous query batch, and it has the same ips and ports then treat it as a duplicate and chuck it away. When not in debug mode the agent.log will show lines like the following when it happens:
2013-10-23 10:11:10,640][ERROR][default.com.arcsight.agent.sdk.b.g.ab][processQuery] Event with duplicate ID [blah] for [jdbc:odbc:tmg3], ignoring
Hoe this helps.
I had a look at the parser from your related post, and can see you have defined the uniqueid.fields setting.
I wonder if it is a bug in the connector framework, or unexpected behavior from the persistence setting? Perhaps try a different version of the connector to see if it makes any difference? What version are you using?
The best way to verify what is going on would be to run a trace on the database itself, and look at the timestamp that the connector is actually sending (not just what the debug says its doing). There is a discussion on how to trace queries in SQL express here: Logging ALL Queries on a SQL Server 2008 Express Database? - Stack Overflow
Hope this helps, good luck! If you solve the problem please report back to the forum.
Just to let everyone know, How I tackled the Local SQL on the TMG. The challenge is that the databases are rotated daily.
The second challenge is that Flexconnector does not work with variables at all. We are slowly moving from TMG, but this was a lengthy experience and some research was included, to get this solution that is quite a workaround . This solution does not require for anything to be installed on the server. Just a scheduled task is needed on the server.
- We had to enable the SQL to listen to remote queries (default for SQL express is not to listen for remote connections) - this is needed for the connector to remotely attach to the SQL express.
- We then created a synonym along with a scheduled task to recreate the synonym whenever it detects a new database was added (The trigger is the appropriate event Id from SQL) The create synonym then creates a "proxy" where we can query the latest databases and the latest events in it.
--GFirst remopve synonym when it exists
WHERE name = N'CurrentWebProxyLog'
DROP SYNONYM dbo.CurrentWebProxyLog
--Get the actual database name
DECLARE @PROXYDB VARCHAR (24)
SET @PROXYDB = (SELECT TOP 1 [name]FROM master.dbo.sysdatabases where name LIKE '%WEB%' ORDER BY NAme DESC)
DECLARE @SQL NVARCHAR(1000)='CREATE SYNONYM dbo.CurrentWebProxyLog FOR '+@PROXYDB+'.dbo.WebProxyLog '
IF (@PROXYDB IS NOT NULL)
EXEC sp_executeSQL @statement=@SQL
I am also appending the connector properties file for the TMG. Both the firewall and the proxy log. The Proxy log is based on Jordan Anderson work. I just remapped some stuff.