dbxquery and time picker

[abilis]

Hi all,

I have a dbxquery from DB connect which return all results in the DB, then I put this in a splunk dashboard with a time picker but the time chosen is no being considered by the dbxquery.

dbxquery query="SELECT * FROM \"HealthMon\".\"dbo\".\"Access\"" connection="HealthMon"

is there a way to limit the results of dbxquery based on the chosen time ?

thanks a lot

[acharlieh]

On my phone right now so I have neither a Splunk nor RDBMS instance to play with off hand so the syntax may be slightly off, and depending on your time field in the database, you may need to do some conversion... but I would use a subsearch with addinfo to build the SQL statement including the time based where clauses, and then insert that query into the parent dbx query... e.g.

|dbxquery connection="HealthMon" [makeresults | addinfo | eval query="SELECT * FROM x WHERE x.time >=".info_min_time." AND x.time <=".info_max_time | return query]

[abilis]

Hi,

i have being trying to make it work, but still not getting results using the time picker...this is what i have

|dbxquery connection="Monitoring"  [makeresults | addinfo | eval query="SELECT * FROM \"HealthMonitor\".\"dbo\".\"AccessAttempt\" WHERE   \"HealthMonitor\".\"dbo\".\"Attempt\".AccessDate >=".info_min_time." AND \"HealthMonitor\".\"dbo\".\"AccessAttempt\".AccessDate <=".info_max_time."" | return query]

I get an error saying
java.sql.DataTruncation: Data truncation

the table has a SQL column name AccessDate and the values format looks like 2018-07-19 07:46:04.003

what else can it be ?

thanks for your help

[sloshburch]

Check the Job Inspector to see what the subsearch returned. Or just run the subsearch to see what it evaluates to. This should help us debug the mismatch.

[niketn]

Since this seems to be for SQL Server, unless date time field stored in DB is actually epoch time and not datetime format,

1) Epoch time would need to be converted to string time for specific format. The following converts it to YYYY-mm-ddTHH:MM:SS.mmm (https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2...)

| makeresults
| addinfo
| eval info_min_time=strftime(info_min_time,"%Y-%m-%dT%H:%M:%S.%3N"), info_max_time=strftime(info_max_time,"%Y-%m-%dT%H:%M:%S.%3N")

2) Based on String time format datetime field value will be generated using convert() function and check against the timestamp field in the DB table.

WHERE datetimefield>=CONVERT(varchar, '$info_min_time$', 126) AND datetimefield>CONVERT(varchar, '$info_max_time$', 126)

You would need to use map command to pass on calculated min time and max time based on Time range selection and also need to use datetime field in your table. (You can also use BETWEEN in WHERE clause)

Splunk Documentation has an example of using map command for dbxquery (it is for executing stored procedure though): http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Commands#Required_Arguments