Highlighted
ekirca1 Valued Contributor.
Valued Contributor.
608 views

ID based Flex connector development fails /*

Jump to solution

Hi ,

I am integrating a ID based Flex connector to fetch log from internal application. The application runs on SQL server 2012.

Created MS SQL JDBC connectivity for that DB sever. Tested success

I created a ID based config file. ( sdkibdatabase.properties ) but it failes ( unable to detect version )

I got a few helpful resource from DB admin and check that there are 3 DB object in this query and some INNER Join parameters.

Was hoping for some help with creating an ID Based SQL Flexconnector.

Labels (3)
0 Likes
1 Solution

Accepted Solutions
Honored Contributor.. yurlov.a Honored Contributor..
Honored Contributor..

Re: ID based Flex connector development fails /*

Jump to solution

I think, it is a driver problem. Try to download ODBC 32 bit driver for sql server 2012 and configure it. To test your flex with ODBC driver you can use my util .  If all is OK, reinstall connector for odbc driver.

View solution in original post

0 Likes
12 Replies
Outstanding Contributor.. andrew.dalbor Outstanding Contributor..
Outstanding Contributor..

Re: ID based Flex connector development fails /*

Jump to solution

Erkan,

Make sure you have at least this 1 line in the beginning of your properties above the query.

version.order=1

Refer to page 50 of the Flex Connector Developer's Guide

0 Likes
ekirca1 Valued Contributor.
Valued Contributor.

Re: ID based Flex connector development fails /*

Jump to solution

Yes it must start with "version.order=1"

I have shared my config file below

comments.startwith=#

version.order=1

version.id=5

version.query=SELECT TOP 10 AuditID FROM IRAudit WITH (NOLOCK)

##################################################

## syntax is not correct, just want to see easy

##################################################

query=select

dbo.IRAudit.AuditID,

dbo.IRAudit.AuditDate,

dbo.IRAudit.AuditDateOffset,

dbo.IRAudit.AuditOperationID,

dbo.IRAudit.AuditedName,

dbo.IRAudit.AuditedType,

dbo.IRAudit.ICUID,

# 7 Rows from IRAudit Table

dbo.RecordingData.RecordingFileName,

dbo.RecordingData.CategoryID,

dbo.RecordingData.RecordingTitle,

dbo.RecordingData.RecordingDate,

dbo.RecordingData.CallIDkey

# 5 Rows from RecordingData Table

dbo.AuditOperation.AuditOperationName,

# 1 Row from AuditOperation Table

FROM

IRAudit

INNER JOIN

RecordingData ON RecordingID = RECORDINGID INNER JOIN

AuditOperation ON AuditOperationID = AuditOperationID

# IRAudit + RecordingData + AuditOperation (?)

WHERE   

(dbo.RecordingData.CategoryID = @CategoryID) AND (dbo.RecordingData.RecordingDate > @StartDate)and

(dbo.RecordingData.RecordingDate < @FinishDate)

######################################

maxid.query=select max(AuditID) from IRAudit

id.field=AuditID

uniqueid.fields=AuditID

### Field Mappings ###

id.field=AuditID

uniqueid.fields=AuditID

token.count=12

token[0].name=AuditID

token[0].type=Integer

token[1].name=AuditDate

token[1].type=TimeStamp

token[1].format=MM.dd.yy HH:mm

token[2].name=AuditDateOffset

token[2].type=Integer

token[3].name=AuditOperationID

token[3].type=Integer

token[4].name=AuditOperationName

token[4].type=String

token[5].name=AuditedName

token[5].type=String

token[6].name=AuditedType

token[6].type=Integer

token[7].name=ICUID

token[7].type=String

token[8].name=RecordingFileName

token[8].type=String

token[9].name=CategoryID

token[9].type=Integer

token[9].name=RecordingTitle

token[9].type=String

token[10].name=RecordingDate

token[10].type=TimeStamp

token[10].format=yyyy-MM-dd HH:mm:ss.SSS

token[11].name=CallIDkey

token[11].type=Integer

additionaldata.enabled=true

event.deviceVendor=__getVendor("ACME")

event.deviceProduct=__stringConstant("ICBM")

event.categoryBehavior=__stringConstant("/Access/Start")

event.categoryDeviceGroup=__stringConstant("/Application")

event.categoryObject=__stringConstant("/Host/Application/Service")

0 Likes
Outstanding Contributor.. andrew.dalbor Outstanding Contributor..
Outstanding Contributor..

Re: ID based Flex connector development fails /*

Jump to solution

I would recommend you start by commenting out your version.id and version.query lines to see if the issue possibly lies there.

0 Likes
Honored Contributor.. yurlov.a Honored Contributor..
Honored Contributor..

Re: ID based Flex connector development fails /*

Jump to solution

Hello, Erkan.

I recommend you the next steps:

1. First of all, your Excel document contains QUERY. There are DECLARE and SET sections, so it is a script, not a query. ArcSight flex supports queries ONLY. If you want receive db data, you don't need variables. So remove @.., it doesn't work.

Edit: WHERE (dbo.RecordingData.CategoryID = @CategoryID) AND (dbo.RecordingData.RecordingDate > @StartDate)and

(dbo.RecordingData.RecordingDate < @FinishDate) to

WHERE (dbo.RecordingData.CategoryID = 1796) AND (dbo.RecordingData.RecordingDate > ?)

But in this case you need timebased flex. If you want use idbased flex, you need unique ID. In your xls I noticed AuditID the same! IS IT WRIGHT? If it is unique you can use id-based flex.

2. Secondly, your query is not correct (I think you post sdkibdatabase.properties file in readable format). You move to next line without '\' (see guide for details). Avoid # comments into your query. Could you post original file?

3.Thirdly, you use tokenization. Remove this section! Work with your database fields types, for example, if type AuditID is bigint use direct mapping with long ArcSight field (deviceCustomNumber1 or other): event.deviceCustomNumber1=AuditID. Use sql developer to verify your sql and get fields info.

Regards,

Alexander

0 Likes
ekirca1 Valued Contributor.
Valued Contributor.

Re: ID based Flex connector development fails /*

Jump to solution

Hi,

Yes AuditID field is unique, in the xls file it all same but the the actual one is unique.

So I can use AuditID field for my ID based flex connector.

Changed the syntax as your mention

<acme_icbm.sdkibdatabase.properties>

+++++++++++++++++++++++++++

comments.startwith=#

version.order=1

version.id=1

version.query=select top 10 AuditID from IRAudit

maxid.query=select max(AuditID) from IRAudit \

query=select AuditID, AuditDate, AuditDateOffset, AuditOperationID, AuditedName, AuditedType, ICUID, RecordingFileName, CategoryID, RecordingTitle, RecordingDate, CallIDkey, AuditOperationName \

from IRAudit INNER JOIN RecordingData ON RecordingID = RECORDINGID INNER JOIN AuditOperation ON AuditOperationID = AuditOperationID \

where AuditID is not NULL AND AuditID >= ? \

order by AuditID asc

id.field=AuditID

uniqueid.fields=AuditID

event.endTime=RecordingDate

event.deviceCustomString1=AuditDate

event.deviceCustomString1Label=__stringConstant("AuditDate")

event.deviceCustomString2=ICUID

event.deviceCustomString2Label=__stringConstant("ICUID")

event.deviceCustomString3=RecordingFileName

event.deviceCustomString3Label=__stringConstant("RecordingFileName")

event.deviceCustomString4=RecordingTitle

event.deviceCustomString4Label=__stringConstant("RecordingTitle")

event.deviceCustomString5=CallIDkey

event.deviceCustomStringLabel=__stringConstant("CallIDkey")

event.categoryDeviceGroup=__stringConstant("/Application")

event.categoryObject=__stringConstant("/Host/Application/Database")

event.deviceVendor=__getVendor("ACME")

event.deviceProduct=__stringConstant("ICBM")

0 Likes
Honored Contributor.. yurlov.a Honored Contributor..
Honored Contributor..

Re: ID based Flex connector development fails /*

Jump to solution

It's seem to be good. I recommend you to comment version.id and version.query. If you want receive first 10 rows add query.limit=10, but users configure unlimited flex more often. Remove \ at the end of maxid.query=... \. It's not necessary, because you don't move the part of maxid.query to next file row.

0 Likes
ekirca1 Valued Contributor.
Valued Contributor.

Re: ID based Flex connector development fails /*

Jump to solution

Screen Shot 2015-10-07 at 10.29.15.png

I commented version.id and version.query and also tried query limit but no way

result says that there is no query but here it is

0 Likes
Honored Contributor.. yurlov.a Honored Contributor..
Honored Contributor..

Re: ID based Flex connector development fails /*

Jump to solution

I think, it is a driver problem. Try to download ODBC 32 bit driver for sql server 2012 and configure it. To test your flex with ODBC driver you can use my util .  If all is OK, reinstall connector for odbc driver.

View solution in original post

0 Likes
ekirca1 Valued Contributor.
Valued Contributor.

Re: ID based Flex connector development fails /*

Jump to solution

Sure I will try with ODBC drivers.

Already installed another Flex connector (TimeBased ) with JDBC driver and its working fine

Step1.

http://msdn.microsoft.com/en-us/sqlserver/aa937724

Download

Step2.

sqljdbc_4.0\enu\auth\x86\sqljdbc_auth.dll

file from the driver download to the

$ARCSIGHT_HOME/jre/bin

Step3.

copy Sqljdbc4.jar

$ARCSIGHT_HOME/current/user/agent/lib

Step4.

change Sqljdbc.jar file name

0 Likes
Honored Contributor.. yurlov.a Honored Contributor..
Honored Contributor..

Re: ID based Flex connector development fails /*

Jump to solution

I propose another way. If I have problems with database connector, I install odbc driver for windows workstation and configure ODBC Source. You may download x32 odbc driver Download Microsoft® ODBC Driver 11 for SQL Server® - Windows from Official Microsoft Download Center

Install it and enter odbcad32.exe to configure this connection in ODBC manager dialog. Test connection, start util, enter necessary data (you may refer to this post:). If it is OK, update connector settings (select sun.jdbc.odbc.JdbcOdbcDriver during install).

0 Likes
ekirca1 Valued Contributor.
Valued Contributor.

Re: ID based Flex connector development fails /*

Jump to solution

Yes.

Part of the problem was driver.

Uninstalled ID based Flex Connector x86 and installed x64, and there is no "unable to detect db version" message anymore. Next step I changed my config file a bit, just inner join 2 tables with RecordingID not 3 of them.

It works now Thanks all

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.