External mapping utilizing a join condition in query

0 Likes
over 10 years ago

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
  • Hi Alexander,

    nope, at least in the version I know/met till now. Moreover it is stated in the related presentation, that

    "No way to use the Getter for multiple columns in a Primary Key"

    KR,

    H.

  • Dear All,

    Can I use two getter field in extmap file?

    Regards,

    Alexander

  • Unfortunately not a nice solution but it worked. A kind of content duplication was needed, we created a scheduled job to have the data refreshed in a table like a materialized view (terminology depending on the respective environment/RDBMS), at the end the extmap config file should only contain a simple select statement i.e. I didn't manage to find a way to call a stored procedure.

  • I hadn't thought of that... So something like SELECT getter, setter FROM OPENQUERY(LOCALSERVER, 'EXEC my_sproc') WHERE getter in (?u0000?) ?  The only drawback that I see to this approach is that you have to drag back the whole result set from the stored proc before filtering it with the in clause so performance may not be great and linked servers have other performance costs but it's a feasible solution given those limitations.

  • Hi Superman/Yarick,

    as far as I remember, there were two basic changes after the first trial:

    1. as per FlexDevGuide usage of JDBC Driver instead of ODBC ~

    - For Microsoft SQL Server, use: com.sqlserver.jdbc.SQLServerDriver,

    - Database URL ~ For Microsoft SQL Server 2005 and later, use: jdbc:sqlserver://host:port;databasename=name

    2. the stored proc was executed via a linked server, its result stored in a temporary table, which then was queried with a select statement.

    So the "solution" was just as Richard already described in his answer.

    HTH.

  • The more I look at this (and I've spent a while at it now with SQL Profiler) the less certain I am that you'll be able to use a stored proc.  The problem is two-fold and lies in the fact that the external mapper uses a parameterised query (this is good practice and helps prevent sql injection etc) and adds a variable number of parameters based on some internal logic, this isn't a problem when using dynamic sql as the parameters just get added to the in clause "where getter in (@p1, @p2, @p3...)", however, when you define a stored proc you declare the parameters up front, if the number of parameters from the mapper was fixed, this would be ok but it's variable. The best bet and one referenced in Arrays and Lists in SQL Server 2008  was to somehow force the connector to call the stored proc with the parameters passed as a single parameter formatted as a comma separated list, then split the list and insert to a temporary table using a udf and use the temporary table in a join etc. I can't see any way to do this.  My previous answer of using a TVP doesn't work as it's not supported by the JDBC driver and it's not the way the mapper adds the parameters anyway.  The last option is that if we knew the upper limit of the number of parameters that were being passed by the mapper we could declare that number of parameters in the stored proc and assign them a default value of NULL making them optional, insert the non-null variable to a temp table and join on the temporary table but that's subject to breaking if HP change the number of parameters etc.

    If you're being asked to use a stored proc as a security measure i.e. the DBA doesn't want to give you access to the base table to run arbitrary queries perhaps you could come to a compromise by using a view and querying this dynamically instead?

  • At least with Postgres you can do the same as what I documented in this post though this is really the equivalent of a mssql table valued function.  For MSSQL (2008+) you probably need to use table valued parameters as described here -> Arrays and Lists in SQL Server 2008 to pass a list to a stored procedure as a parameter

  • Dear H.      Did you have the time to find a solution for the stored procedure issue ?   If so, could you share some details.   I am facing a very similar issue when calling a stored procedure.  Thank you.

    Yarick

  • 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.

  • Have just found the presentation but indeed no proper documentation. The only example is type=sql, but I would have been interested in knowing all the possible types... Anyway, thanks a lot

Related Discussions
Recommended