Big news! The community will be moving to a new platform April 21. Read more.
Big news! The community will be moving to a new platform April 21. Read more.
Absent Member.
Absent Member.
964 views

IDBased FlexConnector - Query issue

Jump to solution

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

Labels (2)
0 Likes
1 Solution

Accepted Solutions
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

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

Bill

View solution in original post

0 Likes
9 Replies
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

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

0 Likes
Absent Member.
Absent Member.

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

0 Likes
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

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

Bill

View solution in original post

0 Likes
Absent Member.
Absent Member.

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

0 Likes
Absent Member.
Absent Member.

how did you handle it.

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

0 Likes
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class
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
0 Likes
Absent Member.
Absent Member.

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


0 Likes
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

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.

0 Likes
Absent Member.
Absent Member.

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?

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.