topic Re: How can I add a date range to dbquery based on search dates on interface? in Getting Data In

How can I add a date range to dbquery based on search dates on interface?

alexsmirnoff — Fri, 16 Feb 2018 02:47:27 GMT

Hello
Can someone please tell me how to add a date range to dbquery. I wish the time range of the image (text boxes in the edit Search, Reports, and alerts) as shown in the image.

I assume this is $job.earliestTime$ and $job.latestTime$

What I want is something like

| dbquery MyDatabase "SELECT DISTINCT TOP 10 * FROM V_MyView MyDate BETWEEN " + $job.earliestTime$ + " and "  + $job.latestTime$

Basically, return everything in a date range as my view is too big and I only need the range that is selected.

But I keep getting the error command="dbquery", A database error occurred: Invalid SQL statement or JDBC escape, terminating ''' not found.

I also tried things like

| dbquery MyDatabase "SELECT DISTINCT TOP 10 * FROM V_MyView MyDate BETWEEN " . $job.earliestTime$ . " and " . $job.latestTime$

dbquery must be the first command so I cannot use anything like eval 😞
I'm sure I cant be the first person to have this issue.
Please help.

Re: How can I add a date range to dbquery based on search dates on interface?

493669 — Fri, 16 Feb 2018 03:14:11 GMT

Hi,
Try @sowings or @ziegfried answer from https://answers.splunk.com/answers/75999/splunk-db-connect-dbquery-inline-search-and-time-filtering-not-working.html

Re: How can I add a date range to dbquery based on search dates on interface?

alexsmirnoff — Fri, 16 Feb 2018 04:00:41 GMT

The only thing close to what I ased is the last example on this link. It did not work when I pumped in a basic sql query. I tried this (except with a real view):

| dbquery MyDatabase [ eval query= "SELECT DISTINCT TOP 10 * FROM V_MyView "]

This did not work at all 😞 All I got was SPLUNK errors.

Can please direct me to something that is a simple complete working example. Anthing that can use a eval and joins 2 strings.

I saw this page ages ago, tried thier example and they didnt work. My real query has averages, counts and calculations, so it cannot be a two set query. There are millions of rows of data in my query (big data).

The second part of the question is very simply. I should not be given a URL. What are the variable names for Earliest and Latest (based on the image).

Are they
$job.earliestTime$ and $job.latestTime$

if not... what are they?

I would have thought this would be the first example on the user guide on how to use dbquery.

Re: How can I add a date range to dbquery based on search dates on interface?

493669 — Fri, 16 Feb 2018 04:57:58 GMT

For your time range what is your input type is it text or time
also have you checked @brunton2 answer
here input type="time" is used and then used $form.et$, $form.lt$ in dbquery like:

| dbquery mydb "SELECT timestamp_column, other_column1, other_column2 FROM mytable | AND (TIME_STAMP >= TO_DATE('$form.et$', 'YYYY-MM-DD HH24:MI:SS') AND TIME_STAMP <= TO_DATE('$form.lt$', 'YYYY-MM-DD HH24:MI:SS'))"

currently I am unable to test these query in my local...

Re: How can I add a date range to dbquery based on search dates on interface?

micahkemp — Fri, 16 Feb 2018 15:03:06 GMT

This is an ugly search, but you could try something like:

| dbquery MyDatabase 
    [| makeresults 
    | addinfo 
    | eval info_max_time=if(info_max_time="+Infinity", now(), info_max_time) 
    | eval earliest=strftime(info_min_time, "%Y-%m-%d %H:%M:%S"), latest=strftime(info_max_time, "%Y-%m-%d %H:%M:%S") 
    | eval search="\"SELECT DISTINCT TOP 10 FROM V_MyView MyDate BETWEEN ".earliest." and ".latest."\""]

The subsearch creates a dummy event, adds the search info to the event (addinfo), calculates string representations for earliest/latest, and crafts a string for the query using those stringified dates. That query string is named search, which causes the subsearch to place the contents of the search field directly into your search, resulting in a search that looks like:

| dbquery MyDatabase "SELECT DISTINCT TOP 10 FROM V_MyView MyDate BETWEEN 1970-01-01 00:00:00 and 2018-02-15 18:58:09"