External mapping utilizing a join condition in query

0 Likes

I recently had the challenge of having to do an external mapping db  lookup, but needed to use a join condition for my results.  Not much  attendtion has been paid to the more comlex queries that some times need  to be don ein order for these thing to work.  So I decided to share  what I came up with so as to help anyone else out who has similar need.


First  I mapped an event EVENTID to exteranlID and use that value in my  query.  There are many ways to do this I chose to do it through the  console.

extmap.0.properties to include join condition.

type=sql
field.getter=exterlId
field.setter.count=2
field.setter[0]=flexString1
field.setter[1]=flexString2
jdbc.class=sun.jdbc.odbc.JdbcOdbcDriver      - MSSQL  I believe it will different for Oracle and other DBs.
jdbc.url=jdb:odbc:Arcsight_myodbc             -  This is the ODBC we created for a connector on the machineso I I  used it and it works, documentation will state to create an ODBC string.


jdbc.username=myusername          
jdbc.password=myobfuscatedpw                  - This is the obfuscated pw created using the Arcsight utility.

Jdbc.query=select  a.primarykey as primarykey, a.field1 as field1, b.field2 as field2   from table1 as a  JOIN  table2 as b on a.field1 = b.field3 \

Where a.primarykey in (?\u0000?)

b.field3 is the key on the second table needed to get b.field2.

field1 gets mapped to setter0
field2 gets mapped to setter1

KB should be written on these more complex queries in addition to the easy ones

Comment List
Anonymous
Parents
  • Dear all,

    did you manage to find further info re available types (see type=sql) in the extmap.x.properties file? E.g. how should it be set when a stored procedure is called? I have tried it using

    type=sql
    field.getter=targetUserId
    field.setter.count=1
    field.setter[0]=targetServiceName

    jdbc.class=sun.jdbc.odbc.JdbcOdbcDriver
    jdbc.url=jdbc:odbc:CusTypeESS
    jdbc.timeout=60
    jdbc.username=ArcSight_Qry
    jdbc.password=OBFUSCATE.4.8.1:RGh9eTLuCNqfKqloVSEtbSJbyAHiq9rj
    jdbc.charset=US-ASCII

    jdbc.query=execute ESS.dbo.QRY_CUST_TYPE (?\u0000?)

    but got the following error message:

    [2012-10-24 17:51:24,381][ERROR][default.com.arcsight.agent.util.c.d][run]

    java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)

    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)

    at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3149)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:216)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:91)

    at com.arcsight.agent.sg.b$2.a(b$2.java:520)

    at com.arcsight.agent.util.c.d.run(d.java:111)

    [2012-10-24 17:51:24,381][ERROR][default.com.arcsight.agent.sg.b][process] Unexpected exception executing query or getting results

    [2012-10-24 17:51:24,381][ERROR][default.com.arcsight.agent.sg.b][process]

    java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)

    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)

    at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3149)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:216)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:91)

    at com.arcsight.agent.sg.b$2.a(b$2.java:520)

    at com.arcsight.agent.util.c.d.run(d.java:111)

    [2012-10-24 17:51:24,381][INFO ][default.com.arcsight.agent.sdk.d.f.j][processQuery] Forcing removal of connections in connection pool manager.

    [2012-10-24 17:51:24,381][INFO ][default.com.arcsight.agent.sg.b][closeConnection] Connection closed to database for alias [com.arcsight.agent.sg.b_30Ei12TkBABC39svf7xYjhw==_0]

    Any comment is welcome.

    Thanks and regards,

    H.

Comment
  • Dear all,

    did you manage to find further info re available types (see type=sql) in the extmap.x.properties file? E.g. how should it be set when a stored procedure is called? I have tried it using

    type=sql
    field.getter=targetUserId
    field.setter.count=1
    field.setter[0]=targetServiceName

    jdbc.class=sun.jdbc.odbc.JdbcOdbcDriver
    jdbc.url=jdbc:odbc:CusTypeESS
    jdbc.timeout=60
    jdbc.username=ArcSight_Qry
    jdbc.password=OBFUSCATE.4.8.1:RGh9eTLuCNqfKqloVSEtbSJbyAHiq9rj
    jdbc.charset=US-ASCII

    jdbc.query=execute ESS.dbo.QRY_CUST_TYPE (?\u0000?)

    but got the following error message:

    [2012-10-24 17:51:24,381][ERROR][default.com.arcsight.agent.util.c.d][run]

    java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)

    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)

    at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3149)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:216)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:91)

    at com.arcsight.agent.sg.b$2.a(b$2.java:520)

    at com.arcsight.agent.util.c.d.run(d.java:111)

    [2012-10-24 17:51:24,381][ERROR][default.com.arcsight.agent.sg.b][process] Unexpected exception executing query or getting results

    [2012-10-24 17:51:24,381][ERROR][default.com.arcsight.agent.sg.b][process]

    java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)

    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)

    at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3149)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:216)

    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:91)

    at com.arcsight.agent.sg.b$2.a(b$2.java:520)

    at com.arcsight.agent.util.c.d.run(d.java:111)

    [2012-10-24 17:51:24,381][INFO ][default.com.arcsight.agent.sdk.d.f.j][processQuery] Forcing removal of connections in connection pool manager.

    [2012-10-24 17:51:24,381][INFO ][default.com.arcsight.agent.sg.b][closeConnection] Connection closed to database for alias [com.arcsight.agent.sg.b_30Ei12TkBABC39svf7xYjhw==_0]

    Any comment is welcome.

    Thanks and regards,

    H.

Children
No Data
Related Discussions
Recommended