How to get DB top 10 query wait states in AppD dashboard.

[Ashok_Sreemant2]

Hi Team,

I want to get DB top 10 query wait states in AppD dashboard. Kindly suggest.

[Hardik__Makwana]

Thank you, @Cansel.OZCAN  this information helped me alot.

[Cansel_OZCAN]

Hi Hardik,

The query that i sent to you is working for the whole collectors (I have only 1 collector so it is showing only 1 🙂 )  

If you have more than 1 collector you need to add "WHERE" clause with "server-id" property in order to filter your exact collector match. (you can also find which server-id is equal to which collector name via Chrome 12 Network tools. I sent you a reference screenshot) 

The query below will be useful for you to filter the exact collector,

In my example, I'm only working with a collector that server-id =4838 

you can find your collector's server-id via Chrome-FireFox brows. developer tools like this below,

First Open you database collector via AppD controller UI below at the same time you can find the server-id detail over Browser's "Network-Response tab" 

After finding the exact server-id property you can use this Select query below for the result

If you want to compare result via Default dashboard widget as you can see it is also same below,

Btw you can also find wait-stat-id explanation detail same way (over Chrome developer tool)

If you want more detail please feel free.

Thanks

Cansel

[Hardik__Makwana]

Dear Cansel, 

The query you have shared is running properly on one collector, but what if there are multiple collectors? It is showing me the wait state with its numeric IDs and giving a count for it as well. Another thing was, can I show the name of query with it's ID? Please check if the query is right or wrong because it is still not showing. One more thing, I want to let you know my setup is on prem. Please find the attachment below

Thanks & Regards,

Hardik

[Hardik__Makwana]

Hello Cansel,

I did the same, but it is showing me a syntax error. Please find the attachment below.

[Cansel_OZCAN]

Hi Hardik,

Actually, this is not a syntax error, after "FROM" you specify the data source and there is no data source like "DB5". You have to use "dbmon_wait_time" this comes from event service shards. 

Another thing is (sorry this is my fault 🙂 ) I accidentally removed "count" before " (`wait-state-id`) " that is bolded below. Btw this query is based on a controller that has only 1 DB collector, if you have more than 1 collector you need to specify 'server-id'  column with "WHERE" clause.

SELECT `wait-state-id`, count(`wait-state-id`) FROM dbmon_wait_time

Thanks

Cansel

[Hardik__Makwana]

@Cansel.OZCAN  I have analytics, but it is still not showing. Can you please tell me the query for getting the top 10 sessions by weight widget? 

[Cansel_OZCAN]

Hi Hardik,

You can use this ADQL as a simple

SELECT `wait-state-id`, (`wait-state-id`) FROM dbmon_wait_time

This query gathers data based on "wait state id" but you can change this query based on "wait state name" 

For Example wait state id 59 = Using CPU

Thanks

Cansel

[Cansel_OZCAN]

Hi Ashok,

I you have Analytics you can use ADQL in order create Top 10 query wait states widget

Thanks

Cansel