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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...