Help with sql request

[templier]

Hello, all 🙂
I have a new request for help.

First time a use next request for every table:
Input Type: Advansed

SELECT RecordId,CompId,UserId,EventId,Type,ProcessName,Pid,DeviceTypeId,Action,Name,Info,AdditionDate,Reason,ReasonMnemonic,DeviceType FROM "DevicelockDB"."dbo"."DLAuditLog" Column_list WHERE RecordId > ? ORDER BY RecordId ASC

Checkpoint Column RecordId and value.

But now, i want use full request with all interesting data from database.
I creat a request but can't get the following query to work:

SELECT AdditionDate,DLAuditLog.RecordId AS record_ID,DLUsers.UserName,DLStations.NetworkAddr,DLAuditLog.ProcessName,DLAuditLog.Action,DLAuditLog.Name,DLShadowFiles.OriginalFileName,DLAuditLog.DeviceType,DLAuditLog.Reason,DLAuditLog.ReasonMnemonic,DLAuditLog.Info,DLAuditLog.CompId AS compID_1,DLAuditLog.UserId AS userID_1,DLAuditLog.EventId,DLAuditLog.Type,DLAuditLog.Pid,DLAuditLog.DeviceTypeId,DLUsers.UserId AS userID_2,DLStations.CompId AS compID_2,DLShadowFiles_Users.UserId AS userID_3,DLShadowFiles_Users.ShadowId AS shadow_1,DLShadowFiles.ShadowId AS shadow_2
FROM DLAuditLog
INNER JOIN
DLUsers ON DLUsers.UserId = DLAuditLog.UserId
INNER JOIN
DLStations ON DLStations.CompId = DLAuditLog.CompId
INNER JOIN
DLShadowFiles_Users ON DLShadowFiles_Users.UserId = DLAuditLog.UserId
INNER JOIN
DLShadowFiles ON DLShadowFiles.ShadowId = DLShadowFiles_Users.ShadowId 
ORDER BY RecordId,compID_2,NetworkAddr,userID_2,userID_3,shadow_2,OriginalFileName,UserName,EventId,Type,ProcessName,Pid,DeviceTypeId,Action,Name,Info,AdditionDate,Reason,ReasonMnemonic,DeviceType

Maybe anyone can help with that, i want use this request in splunk db connect and follow data based on RecordId field, only new event write to splunk base.

Thanks!

[Richfez · Accepted solution]

One tip: I nearly always use a view when I get into a SQL Query this complex. Create your view that does all your joins and any field cast/convert or formatting you need, then use DB connect to just SELECT ... FROM vw_MyViewName.

For your specific problem, you seem to have the record id in there ( SELECT AdditionDate, DLAuditLog.RecordId AS record_ID, DLUsers.UserName ... but it's named differently. You should be able to treat it exactly the same as your test:

... INNER JOIN  DLShadowFiles ON DLShadowFiles.ShadowId = DLShadowFiles_Users.ShadowId 
WHERE record_id > ? 
ORDER BY RecordId,compID_2,NetworkAddr,userID_2 ...

NOTE I don't know what DB you are using so I don't know if you need record_id or DLAuditLog.RecordId in there (e.g. I don't know if your DBMS allows aliases there or not).

If that doesn't get you sorted out, the next steps are to run the SQL query in whatever your DMBS uses as a console and get it working the way you want first. Once working you could create a view out of it or even just paste it into Splunk and add the one little line for your WHERE clause.

Happy Splunking!
-Rich