| makeresults
| eval inputdate= "2018-01-01 23:00:00"
| map search=" | dbxquery connection=conn_name query=\"select startdate FROM tablename WHERE startdate > '$inputdate$'\" " maxsearches=100000
| fields field1, field2
1) I have more than 100,000 in database, but it is fetching only 10,000 records. Not sure why? Is it the max records that can be pulled using map search?
2) so I added maxsearches to my query(maxsearches=100000), but still its fetching only 10,000 records. Even if I change maxsearches to 10, still its displaying 10,000 records.
Please throw some light on this issue?
Thanks in advance.
Fixed the issue. Here is my code.
| dbxquery connection=conn_name query="select field1, field2 FROM tablename WHERE startdate > ? "
[| makeresults
| appendcols
[| inputlookup kv_lookup
| rename inputdate as params
| fields params]]
Fixed the issue. Here is my code.
| dbxquery connection=conn_name query="select field1, field2 FROM tablename WHERE startdate > ? "
[| makeresults
| appendcols
[| inputlookup kv_lookup
| rename inputdate as params
| fields params]]
Excellent! I had been playing around with something similar, but didn't have a way to test it. Thank you for sharing your solution!
You are welcome.
Thank you for spending your time for my question.
@angelinealex you should go ahead and accept your own Answer and mark this question as answered!
sure. done 🙂
You might try updating the subsearch stanza in limits.conf
[subsearch]
This stanza controls subsearch results.
NOTE: This stanza DOES NOT control subsearch results when a subsearch is called by
commands such as join, append, or appendcols.
* Read more about subsearches in the online documentation:
http://docs.splunk.com/Documentation/Splunk/latest/Search/Aboutsubsearches
maxout =
Maximum number of results to return from a subsearch.
This value cannot be greater than or equal to 10500.
* Defaults to 10000.
Thank you for the link about subsearches.
But if the maxout cannot be more than 10500, then the subsearch can return extra 500 records only right?
I have more than 100,000 records
It wouldn't have anything to do with maxsearches
, because that controls the number of iterations that the map search query will run, but it will only run one map search query per event that precedes the map command. Since your preceding query creates only a single event, the map search query will only run once anyway.
I looked up dbxquery
, and the documentation claims to return 100,000 results by default unless the maxrows
argument is used. Given that you're only getting 10k results, it doesn't seem like it is the source of your issues, but you might try it. Here's the documentation:
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Commands
by default dbxquery is fetching 100,000, but when dbxquery is used inside map search, the final result is 10,000 only.
I'm pretty sure this has to do with a default limit on subsearches returning 10,000 events. This gives you two options:
1. Change the limit of subsearches to return a larger number of events
2. Restructure this query so it doesn't use a subsearch
I'd honestly vote for #2. Why are you doing this in a subsearch, rather than just straight up calling | dbxquery
and supplying the date as either a hard-coded value or a token? Is this in a dashboard or a scheduled search? I bet we could help you restructure this pretty quickly, so you don't have to mess with your limits.conf at all.
Its a scheduled search.
I will have to get the inputdate from kvstore and pass it to dbxquery. That is why I used map search.
I am also trying to restructure the query now. But couldnt get the correct query so far.
Please let me know if you the solution for this.
Give this a try
UPdated
| dbxquery connection=conn_name query="select startdate FROM tablename WHERE startdate > ".[| makeresults | eval search="2018-01-01 23:00:00" | table search]
Tried the above and getting the below error
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[|makeresults | eval search="2018-01-01 23:00:00"
| dbxquery connection=conn_name query=\"select startdate FROM tablename WHERE startdate > [| inputlookup kv_lookup | fields search]
Then its fetching all the data and its not considering date from kv_lookup.
Try the corrected one now...
Getting the below error,
Error in 'dbxquery' command: External search command exited unexpectedly with non-zero error code 1.
Check the value set for the MAX_ROWS
in the .py file , try modify this param
{SPLUNK_HOME}\etc\apps\splunk_app_db_connect\bin\dbxquery.py
i dont have such file in this path