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 :
from outside in a new search.
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"
I know how to run query with db connect. This query is working fine :
| inputlookup mylookup.csv
| eval searchquery="SELECT field1, field2 FROM mongocollection WHERE field1 > ".fieldconstantfrommylookup." "
| map search="|dbxquery connection=mongodatabaseconnection query="$searchquery$""
when it is run directly.
But when it is run from outside with:
| savedsearch abovequeryname
it does not work and says:
Error in 'savedsearch' command: Encountered the following error while building a search for saved search 'abovequeryname': Error while replacing variable name='searchquery'. Could not find variable in the argument map.
Did you try passing a dummy argument to see if that works?
| savedsearch above_query_name searchquery="dummy"
With that dummy argument I get this:
[map]: java.sql.SQLException: Invalid SQL statement entered.
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.
| 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"