IDBased FlexConnector - Query issue

[marcellolino1]

In a nutshell, I have a multiple idbased connector pulling date from MSSQL.

All the data required is spread across multiple tables, and multiple JOINs were  added to the query. On the last JOIN (Highlighted below), the substring had to  be used to split the C.SYSTEMNAME record, which contains "DOMAIN\SYSTEMNAME"  together. So for the JOIN to work, the substring was added to remove the  "DOMAIN\" from C.SYSTEMNAME. The query works fine in MSSQL, but when running in  the FlexConnector the Highlighted piece does not return anything. I thought the  issue was the backslash and I added the escape '\\' and '\\\', but did not work.  Then I removed the backslash and added the number 5 to the substring "on  (A.SYSTEMNAME = substring(c.systemname, 5, len(c.systemname)) ) "  and still did not work. There is no error message in the agent.log. I have been  running it in debug mode. It seems the substring is ignored or maybe not  supported. Any ideas?

query= \
SELECT F.ID, C.EVENTID AS EVENTID1, D.EVENTID_DESCRIPTION, D.EVENTTYPE_DESCRIPTION, \
C.EVENTTIMESTAMP, C.SYSTEMNAME, C.ADMINID, C.GROUPID, C.AFF1ID, C.AFF2ID, C.EVENTINFO, \
E.OBJECT_VALUE, F.EVENTSTATUS, F.USERID, F.TICKETNUMBER, F.TICKETTYPE, F.USERCOMMENT, \
F.ENTRYTIME, F.EVENTID AS EVENTID2, \
A.MACHINECATEGORY ,A.CONTACT_GROUP, A.PRIMARY_CONTACT, A.SECONDARY_CONTACT, A.CONTACT_GROUP_EMAIL \
FROM TEVENTS C WITH(NOLOCK) JOIN LKUP_DOMAINEVENTS D WITH(NOLOCK) \
on (D.EVENTID_ID = C.EVENTID)  \
left outer JOIN LMS_OBJECT E ON (E.OBJECT_IDX = C.GROUPID) \
JOIN TEVENTS_JUSTIFICATION F ON (C.ID=F.EVENTID) \
JOIN SERVERX.InvTAS.dbo.LOISCombined A  \
on (A.SYSTEMNAME = substring(C.SYSTEMNAME, CHARINDEX('\', C.SYSTEMNAME)+1, len(C.SYSTEMNAME)) ) \
where (C.JUSTIFIABLE = 1)  \
AND F.ID >= ? order by F.ID

Thanks,
Marcello

[bill.ryals]

Hi,

Are you trying to use the default ODBC client. Download the Java (sqljdbc.jar) and use that client. I had issues with query's not returning any results with the old ODBC client and this resolved the issue for me.

Bill

[tifoso]

I've got some simliar issue with the MSSQL Flexconnector using ODBC. Does anyone know if there's also a possiblity to replace this driver?

BR, Sly

[bill.ryals · Accepted solution]

Try the Java (sqljdbc.jar) version, it solved my issue with the MSSQL database.

Bill

[marcellolino1]

Agree. that's the driver I am using and so far no issues since it was implemented months ago.

[tifoso]

how did you handle it.

Just put sqljdbc.jar into .../current/user/lib and then change to the right Connection into the Connector Wizzard?

[bill.ryals]

I put the jar here

$connector/current/user/agent/lib/sqljdbc.jar

and then used this connection in the agent.properties.

agents[0].JDBCDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver

[tifoso]

ok. I found out - it doesn't matter if you put the sqljdbc.jar into current/user/lib or current/user/agent/lib, but the first would be formaly better IMHO.

My query is the following:

query = SELECT \

                                 EntryID, UserID \
                 FROM    sx.ActivityLog.Entry as entry1 \
                                 join sx.ActivityLog.Action as action1 on entry1.ActionID = action1.ActionID \
                                 join sx.ActivityLog.User as user1 on user1.UserID = entry1.UserID \
                                 join sx.ActivityLog.Computer as computer1 on entry1.ComputerID = computer1.ComputerID \
                 WHERE   EntryID >?

I found out, when I delete the 2nd JOIN the QUERY will run ok, but into the SQL Editor I'm able to query exact this QUERY and it will be OK. This seems very, very strange to me.

BR, Sly

[bill.ryals]

That is odd, I did notie issues with the odbc driver, but they subsided with the new driver. Is there maybe an issue with the query?

join sx.ActivityLog.User as user1 on user1.UserID = entry1.UserID \   Is the user1.UserID correct, would it not be entry1.UserID?

I may be wrong, but not sure about your schema.

[tifoso]

this would mean:

join sx.ActivityLog.User as user1 on entry1.UserID = entry1.UserID ? doesn't make any sense to me. I've ActivityLog.Entry Tabel with the FK Column UserID and the ActivityLoger.User Table with FW Column UserID. so to join this would make sense:

join sx.ActivityLog.User as user1 on user1.UserID = entry1.UserID?