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!

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...

Want to Reduce Costs, Mitigate Risk, Improve Performance, or Increase Efficiencies? ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...