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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...