All Apps and Add-ons

Why is mapsearch only displaying limited amount of results?

angelinealex
Communicator
| 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.

1 Solution

angelinealex
Communicator

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

View solution in original post

angelinealex
Communicator

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

View solution in original post

ragedsparrow
SplunkTrust
SplunkTrust

Excellent! I had been playing around with something similar, but didn't have a way to test it. Thank you for sharing your solution!

0 Karma

angelinealex
Communicator

You are welcome.
Thank you for spending your time for my question.

0 Karma

niketnilay
Legend

@angelinealex you should go ahead and accept your own Answer and mark this question as answered!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

angelinealex
Communicator

sure. done 🙂

0 Karma

ragedsparrow
SplunkTrust
SplunkTrust

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

angelinealex
Communicator

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

0 Karma

elliotproebstel
Champion

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

0 Karma

angelinealex
Communicator

by default dbxquery is fetching 100,000, but when dbxquery is used inside map search, the final result is 10,000 only.

0 Karma

elliotproebstel
Champion

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.

0 Karma

angelinealex
Communicator

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.

0 Karma

somesoni2
Revered Legend

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

angelinealex
Communicator

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.

0 Karma

somesoni2
Revered Legend

Try the corrected one now...

0 Karma

angelinealex
Communicator

Getting the below error,

Error in 'dbxquery' command: External search command exited unexpectedly with non-zero error code 1.

0 Karma

splunker12er
Motivator

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

0 Karma

angelinealex
Communicator

i dont have such file in this path

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.