All Apps and Add-ons

DBConnect Search Results local

ccsfdave
Builder

Greetings,

I was chatting with a Splunk SE earlier in the week and was complaining that I had a DBConnect to a remote database. Remote is relative here, it is still in our MAN connected via 10Gb fiber. Anyway, I was complaining that my form generated search was taking upwards of 7 minutes to complete. He suggested that I bring the data over to Splunk, especially since it is not terribly time sensitive so a nightly or weekly would work fine.

I suspect if I brought over a dump locally, I could have Splunk monitor the directory it goes into. But would DBConnect recognize it as a database and treat it as such? Is there a way to bring over the data locally from the search?

For completeness, this is the search:

| dbquery "DBNAME" "SELECT * FROM 01003" limit=1000| rename BID as YID  | join YID [|dbquery "DBNAME" "SELECT * FROM 01004"] |eval Zip=substr(ZIPCDE,1,5) | search Zip=$zip$ |table ADR,CITY,STATE,ZIPCDE,USRCD

Any advise you have to bring over the MSSQL database and do something like cron the local population of the data w/o causing duplicates would be very much appreciated.

Thanks,

Dave

0 Karma
1 Solution

ccsfdave
Builder

So I had a dashboard that first populated a dropdown with zipcodes, the first panel was simply a count of results, the next one was full details of the results. So what I did is schedule 3 searches similar to what was used in the above picklist and panels. Each search then outputs to a lookup. I then changed the dashboard to use inputlookups and even the token works!

So it was really painful before, it would take about 2 minutes to populate the dropdown - then about 6.5 minutes to get the results after a selection was made from the dropdown.

This is now all done in about 5 seconds!!!

View solution in original post

0 Karma

ccsfdave
Builder

So I had a dashboard that first populated a dropdown with zipcodes, the first panel was simply a count of results, the next one was full details of the results. So what I did is schedule 3 searches similar to what was used in the above picklist and panels. Each search then outputs to a lookup. I then changed the dashboard to use inputlookups and even the token works!

So it was really painful before, it would take about 2 minutes to populate the dropdown - then about 6.5 minutes to get the results after a selection was made from the dropdown.

This is now all done in about 5 seconds!!!

View solution in original post

0 Karma

barakreeves
Splunk Employee
Splunk Employee

Your first query seems benign from a performance point of view. The bottleneck may be on the second query since there is no limit you set on the number of results returned (the 1st query has the limit set to 1k). You may want to run that query and see how many results are typically returned...divide and conquer the problem.

If you haven't already, try installing Splunk on Splunk app (SoS) and check the logs to see where the problem is. Is it a Java issue running JDBC? I'm not sure. The logs should be able to answer these questions.

If your decision is to move data from MSSQL, how about creating a SSIS package that runs a SQL job, exports results to a CSV file that can be used by Splunk as a lookup table? Try it for the first query only and check your performance. If it is still unacceptable, add to the SSIS job. Each time the job runs, either wipe out the previous lookup file(s) or first version them by making a backup and then wipe it out.

Hope this helps.

0 Karma

ccsfdave
Builder

@barakreeves,

I know the first search, the one with the limit is zip codes so it will return about 25 at most. The second search is returning on average about 500 results.

When I first get to the page, I have the first search looking for zip codes in the db and dynamically populating a dropdown. During that process, which takes about 60 seconds, I get this:

[subsearch]: Subsearch produced 979896 results, truncating to maxout 50000.

I could do a simple lookup for the dropdown but, it would be nice to limit the zip codes to ones that will result in values but I can live with it.

0 Karma

ccsfdave
Builder

@barakreeves,

Is the limit in the search on the results or records to search against?

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.