ID-Based Flexconnector with more than 1 JOIN

[tifoso]

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

[chrisb1]

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?

[tifoso]

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

[gportnoy1]

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.

[gportnoy1]

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)

[tifoso]

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

[gportnoy1]

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.