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
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]
Since this seems to be for SQL Server, unless date time field stored in DB is actually epoch time and not
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
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
what else can it be ?
thanks for your help