Error in 'eval' command (Expected IN) when using filter time token in dbxquery

[Julia1231]

Hi everyone,

I want to create a Dashboard where the time filter (a customize, no preset by Splunk) will effect the result in the table.

Data from table comes from a database, so I use dbxquery.

When I run  the script below, I get an error: 
Error in 'eval' command: The expression is malformed. Expected IN.

I have no idea what is wrong, anyone has an idea please?

| dbxquery connection="connection_name"
[| makeresults
| eval time1 = tostring("-2h@h")
| eval time2 = tostring("@h")
| eval time2 = if(time2=="", now(), time2)
| eval time1 = if(time2=="now", relative_time(now(), time1), time1)
| eval time2 = if(time2=="now", now(), time2)
| eval time1 = if(match(time1,"[@|y|q|mon|w|d|h|m|s]"), relative_time(now(), time1), time1)
| eval time2 = if(match(time2,"[@|y|q|mon|w|d|h|m|s]"), relative_time(now(), time2), time2)
| eval time1 = strftime(time1, "%Y-%m-%d %H:%M:%S")
| eval time2 = strftime(time2, "%Y-%m-%d %H:%M:%S")
| eval query = "SELECT * FROM "catalog"."schema"."table" WHERE date_time BETWEEN '" . time1 . "' AND '" . time2 . "' "
| return query]

Thanks a lot.

[PickleRick]

Firstly, if you have such problem, try to narrow the scope of your search to find where exactly the error is.

In your case - get the subsearch "out" of the main search and try to run it independently. See what the results are and whether they match the expected format for supplying into the main search.

Secondly - if you copy-pasted the search as you run it - it won't fly.

 eval query = "SELECT * FROM "catalog"."schema"."table" WHERE date_time BETWEEN '" . time1 . "' AND '" . time2 . "' "

 This one has broken quotes.

[Julia1231]

Hi @PickleRick 

Thanks for your feedback.

I tried this part, it shows result as expected:

|dbxquery connection="DBconnection" query="SELECT * FROM "catalog"."schema"."table" WHERE date_time BETWEEN NOW() - INTERVAL '2 HOURS' AND NOW() "

But when I put it back into the subsearch as the beginning, it shows again the error. 

I don't know if that's what you are talking about?

[PickleRick · Accepted solution]

I must say that if it runs as it is written, I'm surprised since the quotes in the query parameters are obviously off. But indeed if I paste it into my splunk it fails not on the command parsing phase but on the execution (which is understandable since I don't have any db connections configured).

But anyway.

Just run the subsearch itself

| makeresults
| eval time1 = tostring("-2h@h")
| eval time2 = tostring("@h")
| eval time2 = if(time2=="", now(), time2)
| eval time1 = if(time2=="now", relative_time(now(), time1), time1)
| eval time2 = if(time2=="now", now(), time2)
| eval time1 = if(match(time1,"[@|y|q|mon|w|d|h|m|s]"), relative_time(now(), time1), time1)
| eval time2 = if(match(time2,"[@|y|q|mon|w|d|h|m|s]"), relative_time(now(), time2), time2)
| eval time1 = strftime(time1, "%Y-%m-%d %H:%M:%S")
| eval time2 = strftime(time2, "%Y-%m-%d %H:%M:%S")
| eval query = "SELECT * FROM "catalog"."schema"."table" WHERE date_time BETWEEN '" . time1 . "' AND '" . time2 . "' "
| return query

And you will see that it fails.

Try to trim it to see which command is the culprit. (I already showed you that).

[Julia1231]

@PickleRick Thank you.

I found and correct it finally. 

| eval query = "SELECT * FROM catalog.schema.table WHERE date_time BETWEEN '" . time1 . "' AND '" . time2 . "' "