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

[harry2007gsp]

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

[olex_k7]

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"

[gjanders]

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"

[harry2007gsp]

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.

[gjanders]

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

| savedsearch above_query_name searchquery="dummy"

?

[harry2007gsp]

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

[gjanders]

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

[harry2007gsp]

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.