All Apps and Add-ons

Splunk DB connect: Can you help me convert my SQL query into SPL?

mcbradford
Contributor

So this is working, but I need to do some joins to enrich the information:

| dbxquery query="SELECT  DetectionTime, Process, ThreatName, Path, CleaningAction, ExecutionStatus, ActionSuccess, PendingActions, ErrorCode, RemainingActions, LastRemainingActionsCleanTime FROM \"CM_WCB\".\"dbo\".\"EP_Malware\"" connection="sccm"  

A little internet searching helped me identifie this query as what I need:

"SELECT
    Computer_System_DATA.Name00 as ComputerName,
    DetectionTime,
    Users.UserName,
    Process,
    ThreatName,
    Path,
    EP_ThreatSeverities.Severity,
    EP_ThreatCategories.Category,
    CleaningAction,
    ExecutionStatus,
    ActionSuccess,
    PendingActions,
    ErrorCode,
    RemainingActions,
    LastRemainingActionsCleanTime
    FROM dbo.EP_Malware
    INNER JOIN dbo.Computer_System_DATA on EP_Malware.MachineID = Computer_System_DATA.MachineID
    INNER JOIN dbo.EP_ThreatCategories on EP_Malware.CategoryID = EP_ThreatCategories.CategoryID
    INNER JOIN dbo.EP_ThreatSeverities on EP_Malware.SeverityID = EP_ThreatSeverities.SeverityID
    INNER JOIN dbo.Users on EP_Malware.UserID = Users.UserID
    ORDER BY DetectionTime ASC"

How do I convert sql query above into SPL?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The dbxquery command takes an SQL statement as an argument so your "SPL" is simply

| dbxquery query="SELECT Computer_System_DATA.Name00 as ComputerName, DetectionTime, Users.UserName, Process, ThreatName, Path, EP_ThreatSeverities.Severity, EP_ThreatCategories.Category, CleaningAction, ExecutionStatus, ActionSuccess, PendingActions, ErrorCode, RemainingActions, LastRemainingActionsCleanTime FROM dbo.EP_Malware INNER JOIN dbo.Computer_System_DATA on EP_Malware.MachineID = Computer_System_DATA.MachineID INNER JOIN dbo.EP_ThreatCategories on EP_Malware.CategoryID = EP_ThreatCategories.CategoryID INNER JOIN dbo.EP_ThreatSeverities on EP_Malware.SeverityID = EP_ThreatSeverities.SeverityID INNER JOIN dbo.Users on EP_Malware.UserID = Users.UserID ORDER BY DetectionTime ASC"

However, IME DBX does not work well with complex SQL queries. I recommend creating a view with the needed joins and using DBX to read from the view.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...