I just setup DB Connect for the first time. I'm having trouble wrapping my head around how it operates. Every other input I have is from a Universal Forwarder - (I understand how they work.) DB Connect does not appear to use time/date stamps in the same way as a UF input.
For example, I can create a SQL query within the DB Connect DataLab that returns the results I want. However, using dbxquery in a search string for a span over the past 24 hours, returns 3 year old events!
| dbxquery connection=SQL query="SELECT * FROM SQLCrypto"."dbo"."ProcessLog" | where Message like "%[IsSuccess]:False%"
Can anyone suggest how I can rein in my results to actually include only events in the past 24 hours?
Ultimately, I am looking to create an alert which triggers once an hour for %[IsSuccess]:False% events.
I found a successful solution to this issue. Basically the dbxquery contains primarily SQL query language and did not respond properly to common splunk search language. In short, you really need to make this work with SQL language (not my forte).
In the DB Connect 3.1.1 app, Data Lab>SQL Explorer you can input your SQL code until you get the results you want, and it spits out the Splunk search string on the right, where you can "open in search". This provides the working search string.
Also, saving as a real-time alert would not work, so I set it up on a 30 minute cron schedule, which is */30 * * * *
, and the search string looks back 31 mins from NOW to catch the desired events.
| dbxquery query="SELECT * FROM \"SQLCrypto\".\"dbo\".\"ProcessLog\" WHERE [Message] LIKE '%![IsSuccess!]:False%' ESCAPE '!') AND [Created] BETWEEN DATEADD(MINUTE,-31,GETDATE()) AND GETDATE() ORDER BY [Id] DESC " connection="SQL"
I found a successful solution to this issue. Basically the dbxquery contains primarily SQL query language and did not respond properly to common splunk search language. In short, you really need to make this work with SQL language (not my forte).
In the DB Connect 3.1.1 app, Data Lab>SQL Explorer you can input your SQL code until you get the results you want, and it spits out the Splunk search string on the right, where you can "open in search". This provides the working search string.
Also, saving as a real-time alert would not work, so I set it up on a 30 minute cron schedule, which is */30 * * * *
, and the search string looks back 31 mins from NOW to catch the desired events.
| dbxquery query="SELECT * FROM \"SQLCrypto\".\"dbo\".\"ProcessLog\" WHERE [Message] LIKE '%![IsSuccess!]:False%' ESCAPE '!') AND [Created] BETWEEN DATEADD(MINUTE,-31,GETDATE()) AND GETDATE() ORDER BY [Id] DESC " connection="SQL"
I'm guessing this is not a common issue for DB Connect users? I cannot find any details on why dbxquery returns every matching event and ignores the search time restraint (eg; last hour, last day, etc)
Just update your SQL to have where condition of the period your interested in.
E.g
| dbxquery connection=SQL query="SELECT * FROM dbo.ProcessLog where timefield >= (sysdate - 1) AND message like '%[IsSuccess]:False%'"
Assuming your using a oracle db
That did not work syntactically (Splunk complained), however this statement was accepted;
| where timefield >= (sysdate - 30) | where Message like "%[IsSuccess]:False%"
However, zero results were produced. I have a matching "False" event on Oct 11th, 2017 that fails to show with the "sysdate" parameter.