Searching in an index ingested from database tables

[Sunil2020]

Dear Splunkers,

I am trying to achieve below and would like to ask for help in suggestions, solutions or pointers for the same.

Scenario:

I have two database tables A and B and both are related by unique identifier (i.e. order number). We have a situation where there are cases which are taking more time to process the orders (say more than 15 minutes) which can be found from table A. Table B has data in terms of events occurred during order placed and order served. 

  1) We would like to see what's going on for the orders which is taking more time than 15 minutes. There could be reasons like rush hours, counter operator is not available, more customers due to some offers or something.

 2) How best can we derive the patterns for the give data?

How best we can write searches and create reports or dashboards to achieve the above scenario to demonstrate operational efficiency of a store? 

Your help is highly appreciated.

[isoutamo]

Hi

basically you have two option (probably you should mix them?).

- collect needed log files from app servers to get audit and technical trail 

- use DB Connect to get data directly from DB

to said what is the best way needs more information of your systems. 
r. Ismo

[Sunil2020]

Hi @isoutamo 

Thank you for your inputs. I am using DB connect and got the data from both tables into spunk.

Table A has nearly 100000 records where Table B has more than 4 million records. Using data from table A where i can find how many orders took long time. Table B data will give me details like who (user) has served that order and what he (user) was doing (might have other orders, or any other reasons) between the time order came in and served.

I need pointers to build search query for above scenario.

Thank You.

[isoutamo]

Hi

based on your current information, I create an alert or report which figure out with DBX from table A which transactions take too long and then enrich that data from table B data already in Splunk. That could be also a dashboard. 
r. Ismo

[Sunil2020]

Thank you @isoutamo 

As of now i have static data for 15 days ingested in the splunk to be analyzed and i am struggling with search query basically. Table A gives me orderID which took longer while table B gives me user who processed the order and also how many other task or activity for that user during that timeframe (from order came in to order processed).

Can you please help me with search query for above case?

Thank You.