Splunk Search

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

Julia1231
Communicator

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.

Labels (2)
Tags (3)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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

 

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

Julia1231
Communicator

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?

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Communicator

@PickleRick Thank you.

I found and correct it finally. 

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

 

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...