Splunk Search

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

harry2007gsp
Path Finder

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

olex_k7
Engager

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.

alt text

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"
0 Karma

gjanders
SplunkTrust
SplunkTrust

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"
0 Karma

harry2007gsp
Path Finder

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
SplunkTrust
SplunkTrust

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

| savedsearch above_query_name searchquery="dummy"

?

0 Karma

harry2007gsp
Path Finder

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

0 Karma

gjanders
SplunkTrust
SplunkTrust

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

0 Karma

harry2007gsp
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...