Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Trusted Contributor.. emilian.darie1 Trusted Contributor..
Trusted Contributor..
1557 views

id-based flex connector missing events/duplicate events

Jump to solution

Please some feedback cause I don't get it why I have nothing in the events, thank you

Hi guys, I know the story bight be old but I have this problem also

There are two situations and both don 't give events in ESM or CEF file:

1)I put agents[0].startatid=0 and i ll get this in agent.log :

2015-08-07 16:15:07,649][ERROR][default.com.arcsight.agent.sdk.b.c.t][processQuery] Event with duplicate ID [|] for [jdbc:oracle:thin:@server_Address:1521:db_name], ignoring

[2015-08-07 16:15:07,649][ERROR][default.com.arcsight.agent.sdk.b.c.t][processQuery] Event with duplicate ID [|] for [jdbc:oracle:thin:@server_Address:1521:db_name], ignoring

[2015-08-07 16:15:07,649][ERROR][default.com.arcsight.agent.sdk.b.c.t][processQuery] Event with duplicate ID [|] for

and this when I start connector:

[Fri Aug 07 16:14:13 CEST 2015] [INFO ] Zone based filtering disabled.

[Fri Aug 07 16:14:13 CEST 2015] [INFO ] Database version [1.0] detected.

[Fri Aug 07 16:14:13 CEST 2015] [INFO ] ET[CEF File[{ceffolder=/opt/arcsight/connectors/_dev/oracle_id_based/current/user/agent/cef, filerotationinterval=3600, maxfilesize=10}]] up.

[GC 112213K->17927K(245760K), 0.0470860 secs]

[Fri Aug 07 16:14:13 CEST 2015] [INFO ] Forwarding messages to [127.0.0.1] port [10514] protocol [UDP]

[Fri Aug 07 16:14:13 CEST 2015] [INFO ] ET[CEF Syslog[{host=127.0.0.1, port=10514, protocol=UDP, forwarder=false}]] up.

[Fri Aug 07 16:14:14 CEST 2015] [INFO ] Name resolution will set host name only for Connector [oracle_id_based]

[Fri Aug 07 16:14:14 CEST 2015] [INFO ] Name resolution will set host name only for Connector [oracle_id_based]

[Fri Aug 07 16:14:14 CEST 2015] [INFO ] Agent [oracle_id_based] started.

[Fri Aug 07 16:14:14 CEST 2015] [INFO ] {C=0, ET=Up, HT=Down, N=oracle_id_based}

[Fri Aug 07 16:14:14 CEST 2015] [INFO ] {C=0, ET=Up, HT=Down, N=oracle_id_based}

[Fri Aug 07 16:14:14 CEST 2015] [INFO ] Agent upgrade status check thread started

[Fri Aug 07 16:14:16 CEST 2015] [INFO ] First event from [ArcSight|ArcSight|ip_local|host_local] received.

[GC 116231K->21173K(245760K), 0.0428720 secs]

[GC 119477K->21580K(245760K), 0.0357610 secs]

[GC 119884K->21648K(245760K), 0.0352840 secs]

[Fri Aug 07 16:15:14 CEST 2015] [INFO ] {Eps=0.016666666666666666, Evts=1}

[Fri Aug 07 16:15:14 CEST 2015] [INFO ] {C=0, ET=Up, HT=Down, N=oracle_id_based, S=1, T=0.016548072149594573}

[Fri Aug 07 16:15:14 CEST 2015] [INFO ] {C=0, ET=Up, HT=Down, N=oracle_id_based, S=1, T=0.016547524490336245}

[GC 119952K->21776K(238592K), 0.0388840 secs]

or

2) I put agents[0].startatid=-1 and get nothing like duplicate id but nothing like events also


[Fri Aug 07 16:15:36 CEST 2015] [INFO ] Zone based filtering disabled.

[Fri Aug 07 16:15:36 CEST 2015] [INFO ] Database version [1.0] detected.

[Fri Aug 07 16:15:36 CEST 2015] [INFO ] Querying the database [jdbc:oracle:thin:@ip_db:1521:db_name] to find out last id written

[GC 112769K->18647K(245760K), 0.0445030 secs]

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] Query for [jdbc:oracle:thin:@ip_db:1521:db_name] will start at id [258834]

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] ET[CEF File[{ceffolder=/opt/arcsight/connectors/_dev/oracle_id_based/current/user/agent/cef, filerotationinterval=3600, maxfilesize=10}]] up.

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] Forwarding messages to [127.0.0.1] port [10514] protocol [UDP]

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] ET[CEF Syslog[{host=127.0.0.1, port=10514, protocol=UDP, forwarder=false}]] up.

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] Name resolution will set host name only for Connector [oracle_id_based]

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] Name resolution will set host name only for Connector [oracle_id_based]

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] Device connection to [jdbc:oracle:thin:@ip_db:1521:db_name|user_db] up.

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] Agent [oracle_id_based] started.

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] {C=0, ET=Up, HT=Down, N=oracle_id_based}

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] {C=0, ET=Up, HT=Down, N=oracle_id_based}

[Fri Aug 07 16:15:38 CEST 2015] [INFO ] Agent upgrade status check thread started

[Fri Aug 07 16:15:40 CEST 2015] [INFO ] First event from [ArcSight|ArcSight|ip_local|host_local] received.

[Fri Aug 07 16:16:38 CEST 2015] [INFO ] {Eps=0.03333333333333333, Evts=2}

[Fri Aug 07 16:16:38 CEST 2015] [INFO ] {C=0, ET=Up, HT=Down, N=oracle_id_based, S=2, T=0.03311477581296775}

[Fri Aug 07 16:16:38 CEST 2015] [INFO ] {C=0, ET=Up, HT=Down, N=oracle_id_based, S=2, T=0.033114227527857344}

[GC 116951K->21445K(245760K), 0.0533540 secs]

the parser is like this:

version.order=1

#version.query=SELECT version FROM V$INSTANCE

#version.id=11.2.0.4.0

query=select l.log_id,l.log_type,l.user_id,u.login,u.is_admin,create_date,to_char(substr(l.description,1,2000)) as "description",l.sub_id1,l.sub_id2,l.IP,l.hostname \gents[0].startatid=-1

    from nn_web.webjet_adminlog l left join nn_web.users u on l.user_id=u.user_id \

       where  l.log_id > ?

maxid.query=select max(log_id) from nn_web.webjet_adminlog

id.field=log_id

uniqueid.fields=log_id

token.count=3

token[0].name=log_id

token[0].type=Numeric

token[1].name=IP

token[1].type=ipaddress

token[2].name=login

token[2].type=Numeric

event.deviceCustomNumber1=log_id

event.sourceAddress=IP

event.sourceUserId=login

event.deviceVersion=version.id

event.deviceProduct=__stringConstant("Oracle")

Labels (3)
0 Likes
1 Solution

Accepted Solutions
Rodion Super Contributor.
Super Contributor.

Re: id-based flex connector missing events/duplicate events

Jump to solution

Hi!

If data comes from Oracle table (yes, it does, oracle jdbc-driver is used), try following

uniqueid.fields=LOG_ID

as connector is case sensitive and Oracle returns dataset with headers in upper case.

0 Likes
9 Replies
Rodion Super Contributor.
Super Contributor.

Re: id-based flex connector missing events/duplicate events

Jump to solution

Hi!

If data comes from Oracle table (yes, it does, oracle jdbc-driver is used), try following

uniqueid.fields=LOG_ID

as connector is case sensitive and Oracle returns dataset with headers in upper case.

0 Likes
Trusted Contributor.. emilian.darie1 Trusted Contributor..
Trusted Contributor..

Re: id-based flex connector missing events/duplicate events

Jump to solution

well,I have just done that-I put agents[0].startatid=0 and while I don't know if is good or bad at least I see a lot of activity in cef and in stdout

I ll get back with results ( but when agents[0].startatid=-1 I did not see anything happening)..anyway amazing , we have something at least,it s not ideal but it s different

0 Likes
Established Member.. anwarrhce1
Established Member..

Re: id-based flex connector missing events/duplicate events

Jump to solution

changing startatid=0 will make the connector read all logs in db from starting ID. Whenever you restart the connector it will read the same data again.

this is a good idea to check the parser if that works or not, but dont keep it in production else you will end up with duplicated events whenever connector restarts.

Check if your db is generating any events or no when you keep startatid =1.

0 Likes
Trusted Contributor.. emilian.darie1 Trusted Contributor..
Trusted Contributor..

Re: id-based flex connector missing events/duplicate events

Jump to solution

Thanks Anwar, I am checking this with 0 because of your reasons and then I ll go to -1

With zero the cef is populated, for the moment just with the src cef field, but not the others two:

event.deviceCustomNumber1=log_id

event.sourceUserId=login

CEF:0||Oracle|1.0|||Unknown| eventId=167815 art=1438961035378 rt=1438961035378 src=IP_sursa sourceZoneURI=/All Zones/ArcSight System/Public Address Space Zones/RIPE NCC/212.0.0.0-213.255.255.255 (RIPE NCC) ahost=host_local agt=ip_local agentZoneURI=/All Zones/ArcSight System/Private Address Space Zones/RFC1918: 10.0.0.0-10.255.255.255 av=7.1.4.7475.0 atz=Europe/Amsterdam aid=is \=\= at=sdkiddatabase dtz=Europe/Amsterdam _cefVer=0.1 ad.HOSTNAME=ip_host_sursa  ad.LOG__ID.l=189241 ad.SUB__ID1.l=3136 ad.USER__ID.l=-1 ad.description=ERROR: formName:LEKAR fail:requiredFields\n\nnode:node2\nURI: /formmail.do?savedb\=LEKAR&useFormDocId\=3136\nDomain: t-www.nn.cz\nUser-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/28.0.1500.63 Safari/537.36 ad.LOG__TYPE.l=120 ad.CREATE__DATE.d=1426681818000 ad.IS__ADMIN.l=0 ad.SUB__ID2.l=-1

0 Likes
Established Member.. anwarrhce1
Established Member..

Re: id-based flex connector missing events/duplicate events

Jump to solution

You don't need tokens in Database type flex connectors.

you can directly do the event mappings.

Have you verified you sql query on database sql cmd line ?

does it return the results ?

0 Likes
Trusted Contributor.. emilian.darie1 Trusted Contributor..
Trusted Contributor..

Re: id-based flex connector missing events/duplicate events

Jump to solution

Guys, thanks a lot for the help

I have just created and I have events in ESM, of course I am still missing those two but I hope I ll sort it out

-I ll try on DB to see what it is there-is a client DB, not mine...

-I ll try also without tokens

You guys saved my day

0 Likes
skrc851 Respected Contributor.
Respected Contributor.

Re: id-based flex connector missing events/duplicate events

Jump to solution

Hi All..I did come across a similar error today, this is a time based Flex-connector and did try almost all the possible solutions discussed on this community however had no luck. I did replace my original query with a very straight forward one-line query which din't work though, below is what written in to the parser file

version.order=1

version.id=1

version.query=SELECT USER_NAME from FND_USER

query = SELECT B.USER_NAME, B.USER_ID, B.CREATION_DATE FROM FND_USER B WHERE B.CREATION_DATE <= ? Order by B.CREATION_DATE

timestamp.field= B.CREATION_DATE

uniqueid.fields= B.CREATION_DATE, B.USER_ID, B.USER_NAME

event.name=__stringConstant("XX system logs")

event.sourceUserName=B.USER_NAME

event.sourceUserId=B.USER_ID

event.deviceReceiptTime=B.CREATION_DATE.

I did attempt almost all suggestions from various posts in the community including

replace the < with >, ? with a specific date, create and assign a new field by modifying the query as below, etc.,

SELECT B.USER_NAME, B.USER_ID, B.CREATION_DATE, COUNT(B.CREATION_DATE) as EVENT_COUNT FROM FND_USER B WHERE B.CREATION_DATE <= ? group by B.USER_ID, B.USER_NAME B.CREATION_DATE order by B.CREATION_DATE

though these changesdin't get me the results. All I faced is the same error below

"[ERROR][default.com.arcsight.agent.sdk.d.b.k][processQuery] Event with duplicate ID [|||] for [jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=XX)(INSTANCE_NAME=YYYY))], ignoring"

Looking forward for your suggestions, thanks. in advance.

0 Likes
Highlighted
Rodion Super Contributor.
Super Contributor.

Re: id-based flex connector missing events/duplicate events

Jump to solution

Hi Saravanakumar!

You may add rownum pseudocolumn to your query as:

SELECT B.USER_NAME, B.USER_ID, B.CREATION_DATE, rownum as ROW_ID FROM FND_USER B WHERE B.CREATION_DATE <= ? Order by B.CREATION_DATE

and use ROW_ID  as uniqueid.fields=ROW_ID

These pseudoculumn contains growing integer number for each row in resulting dataset, so it always unique in dataset.

0 Likes
skrc851 Respected Contributor.
Respected Contributor.

Re: id-based flex connector missing events/duplicate events

Jump to solution

Great Idea! Thanks for your help!..the update I have today on this issue is the data type of 'creation_date' was not matching and so a conversion to to_timestamp did get me out of this error. However it is not all i.e now no errors seen in the agent logs, also no logs delivered by the connector, I mean I don't see any logs in the ESM. I'll attempt this "Row_ID" in the query today and update my results. BTW, do you have any other suggestions apart from the given to work on this 'no logs seen' issue, thanks once again!

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.