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

[mcbradford]

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?

[richgalloway]

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.