All Apps and Add-ons

Help with sql request

templier
Communicator

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!

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

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

View solution in original post

Richfez
SplunkTrust
SplunkTrust

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

Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...