DBConnect, $earliest$ and $latest$

[devicenul1]

Anyway to pass the earliest and latest variables from a time range picker to the DB Connect Query command in a specified format?

for example:

|dbquery LiveDB "select * from table between '$earliest$="%Y-%m-%d"' and '$latest$="%Y-%m-%d"'"

Because DB Query needs to be the first command of a search I've tried doing the eval's as a subsearch, something like:

|dbquery LiveDB "select * from table between '$early$' and '$late$' [eval e=$earliest$ | eval l=$latest$ | eval early=strptime(e, "%Y-%m-%d") | eval late=strptime(l, "%Y-%m-%d") ]

but doing that seems to just put it into a "Search is waiting for input" state ... any ideas?

[devicenul1 · Accepted solution]

The $earliest$ and $latest$ variables are in epoch time, so I just wrote a stored procedure that converts it to DATE and runs the query based on that.

SQL:

Create Procedure [dbo].[procedureName]
    @EpochBeginDate INT,
    @EpochEndDate INT
AS
BEGIN
Set NOCOUNT ON

DECLARE @BeginDate DATE;
DECLARE @EndDate DATE;
IF @EpochBeginDate IS NULL
BEGIN
    --Defaulting to three months ago
    SELECT @BeginDate = DATEADD(MONTH, -3, GETDATE()); 
    END
ELSE 
BEGIN
    --adding EpochBeginDate to 01-01-1970
    SELECT @BeginDate = DATEADD(s, @EpochBeginDate, '19700101'); 
    END

IF @EpochEndDate IS NULL
BEGIN
    --Defaulting to Today
    SELECT @EndDate = GETDATE(); 
    END
ELSE 
BEGIN
    --adding EpochEndDate to 01-01-1970
    SELECT @EndDate = DATEADD(s, @EpochEndDate, '19700101'); 
    END


-- Insert statements for procedure here
SELECT
FROM
WHERE
END

[gfreitas]

Hi, I have the same problem. The only workaround I could do is to pipe the results and compare the time like this: "| where date_field > relative_time(now(),"-1mon@mon")".

[devicenul1]

Ahh I see, that's a good workaround but I'd really like to limit my searches out of SQL to only relevant data. This seems like such a simple thing. I've exhausted all research into figuring out how to do this either as an inline search or through the xml dashboards. I've moved my research into using the HTML dashboards and trying to catch this via the javascript. I'll update this thread if I make any progress.