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?
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
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
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")".
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.