topic Re: Can not create savedsearch from a search containing sql query inside with dbxquery in Splunk Search

Can not create savedsearch from a search containing sql query inside with dbxquery

harry2007gsp — Fri, 13 Oct 2017 03:27:57 GMT

how can i use a search(ex:abc) as savedsearch when search abc contains sql query inside it?

Re: Can not create savedsearch from a search containing sql query inside with dbxquery

gjanders — Fri, 13 Oct 2017 12:53:59 GMT

Splunk search processing language is a different language and you cannot use SQL syntax, there are documentation links from the link mentioned there which may help.

Also there is a documentation page on SPL for SQL users

Re: Can not create savedsearch from a search containing sql query inside with dbxquery

gjanders — Fri, 13 Oct 2017 13:01:54 GMT

Alternatively if you are trying to write SPL that runs a SQL query via the DB Connect application the documentation is here

An example from the documentation is:

dbxquery query="select * from actor where actor_id > ? and actor_name = ?" connection="mysql" params="3,BOB"

Re: Can not create savedsearch from a search containing sql query inside with dbxquery

harry2007gsp — Tue, 29 Sep 2020 16:14:57 GMT

I know how to run query with db connect. This query is working fine :

| inputlookup my_lookup.csv
| eval searchquery="SELECT field1, field2 FROM mongo_collection WHERE field1 > ".field_constant_from_my_lookup." "
| map search="|dbxquery connection=mongo_database_connection query="$searchquery$""

when it is run directly.
But when it is run from outside with:
| savedsearch above_query_name

it does not work and says:
Error in 'savedsearch' command: Encountered the following error while building a search for saved search 'above_query_name': Error while replacing variable name='searchquery'. Could not find variable in the argument map.

Re: Can not create savedsearch from a search containing sql query inside with dbxquery

gjanders — Fri, 13 Oct 2017 14:27:42 GMT

Did you try passing a dummy argument to see if that works?

| savedsearch above_query_name searchquery="dummy"

Re: Can not create savedsearch from a search containing sql query inside with dbxquery

harry2007gsp — Fri, 13 Oct 2017 14:43:32 GMT

With that dummy argument I get this:
[map]: java.sql.SQLException: Invalid SQL statement entered.

Re: Can not create savedsearch from a search containing sql query inside with dbxquery

harry2007gsp — Fri, 13 Oct 2017 14:50:13 GMT

With dbxquery , we can use sql inside spl. My problem is that the search i made is working fine with run directly but does not run when run with :
|savedsearch query_name
from outside in a new search.

Re: Can not create savedsearch from a search containing sql query inside with dbxquery

olex_k7 — Mon, 02 Mar 2020 13:50:56 GMT

Hello fellows,

We also had a very similar issue like described by @harry2007gsp, if we put the dbxquery into a saved search, we get the following error.
Even though the same search worked perfectly when we run it directly.

Splunk version 7.2.7 says "Unrecognized option". After a long job inspection we figured out, Splunk automatically adds by calling saved searches "| search" at the beginning of the line! resulting in "| search | dbxconnect [..."
And because the dbxquery has to be the first line operator, everything crashes.

Original state:

The call:

| savedsearch "DBXQUERY"

The saved search:

| dbxquery [| makeresults \`getSplunkAppName\` | eval query="SELECT COUNT(*) FROM TABLE WHERE SPLUNK_APP = '".SplunkApp."'" | return query] connection="SomeDB"

The Macro (just gives the name of hte current splunk application):

| eval [rest /services/search/jobs splunk_server=local | addinfo | where sid = info_sid | rename eai:acl.app as SplunkApp | return SplunkApp]

And after removing the leading pipeline in the saved search, splunk stopped to add "| search".
Also the new working saved search has bekome:

dbxquery [| makeresults \`getSplunkAppName\` | eval query="SELECT COUNT(*) FROM TABLE WHERE SPLUNK_APP = '".SplunkApp."'" | return query] connection="SomeDB"