Getting Data In

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

alexsmirnoff
New Member

alt text

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.

0 Karma

micahkemp
Champion

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"
0 Karma

alexsmirnoff
New Member

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.

0 Karma

493669
Super Champion

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...

0 Karma

493669
Super Champion
0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...