All Apps and Add-ons

dbxquery and time picker

abilis
Explorer

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
Influencer

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
Explorer

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

0 Karma

sloshburch
Splunk Employee
Splunk Employee

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.

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...