UPDATE! The community will be go into read-only on April 19, 8am Pacific in preparation for migration on April 21. Read more.
UPDATE! The community will be go into read-only on April 19, 8am Pacific in preparation for migration on April 21.Read more.
Absent Member.
Absent Member.
644 views

ID-Based Flexconnector with more than 1 JOIN

I've been trying to build a id-based DB flexconnector with more than one join:

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 >=?

But the console shows me some error just before "User" so I guess the 2nd Join is the problem. Further I've deleted the 2nd and 3rd Join

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 >=?

and it's working. So I really asking for someone to tell me if this will not gonna work ever cuz it isn't possible?

Does anyone have some more experience?

BR, Sly

Labels (2)
0 Likes
6 Replies
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

It's completely possible - I have connectors with 10+ joins, much to my dismay.  There's most likely a problem in your joins - have you tried executing the query against the DB directly instead of through the flex connector?

0 Likes
Absent Member.
Absent Member.

yes I did, and it's working perfect in M$ Sql Express Studio. So I guess it should also work at the flexconnector.

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

I've also used multiple joins in my queries, so this should theoretically work. Is this by any chance the Lumension Sanctuary DB? Some of the table names sound familiar. I tried figuring out a query to pull events from it, but ran into a different problem. If it's indeed Sanctuary, i'll share my observation with you and would like to see if you have any ideas on how to overcome it.

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

Now that i glance at it again, I think the problem is that you need  to specify which table to pull the fields from, so the SELECT portion of  your query should look like:

SELECT entry1.EntryID, user1.UserID (or whichever table those columns are in)

0 Likes
Absent Member.
Absent Member.

hey gary,

u got it 😉 it's definitly sanctuary or lumension endpoint security like the call it today. since the syslog isn't a solution cuz every client will send directly to the connector when u do this kind of setup in the manager, this option wasn't an option anymore. So I'm trying to get arround with this stuff and reengineered that this 3 join statements to join this 3 tables would be enough to get the information I would like to receive.

I would be happy to share with you.

BR, Silvan

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

Silvan,


Below is the query I had come up with in Query Analyzer, but I never go to convert it into a flexconnector for the reason i'll explain in a second:

SELECT  E.EntryID, C.ComputerName, A.ActionName,DATEADD(minute, DATEDIFF(minute, getutcdate(), GETDATE()), E.UTCDateTime) AS TDetected, DATEADD(minute, DATEDIFF(minute, getutcdate(), GETDATE()), E.TransferredDate) AS TReceived, U.NTUserName, U.ADUserName, D.Value, DT.Name

FROM       ActivityLog.Entry E LEFT OUTER JOIN ActivityLog.Computer C ON E.ComputerID = C.ComputerID JOIN ActivityLog.Action A ON E.ActionID=A.ActionID JOIN ActivityLog.[User] U ON E.UserID=U.UserID RIGHT OUTER JOIN

(ActivityLog.Entry_Data ED JOIN ActivityLog.Data D ON ED.DataID=D.DataID JOIN ActivityLog.DataType DT ON ED.DataTypeID = DT.DataTypeID)

ON E.EntryID = ED.EntryID

ORDER by E.EntryID


The problem I ran into with pulling the events through SQL is that some information for an event, like the type of the device, the serial number of the device, etc, were all stored in one table (i believe Entry_Data). The problem is that there are multiple entries per event as you can imagine (device type - Blackberry, Serial -xxxxx, etc) but using outer joins you can only bring them in as separate rows. This results in identical events being created, the only difference being whatever was contained in that Entry Data table, and it frequently has 5-6 entries per event. I banged my head against the wall for a while here, but in the end gave up and decided to try the syslog route. There is a way to do what is needed, since SQL supports a PIVOT command to pivot that table, turning rows into columns, but my SQL voodoo is not powerfull enough. So if you figure it out, please let me know.

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.