Finding lesser executed stored procedures in database monitoring

[Marcie_Sirbaugh]

Has anyone found a way to look at metrics for stored procedures that are not in the Top 200 queries.  I have been able to narrow the timeframe down to minutes and find some, but that is very time consuming and painful.  Is there an API that would allow us to pull metrics based on stored procedure name.  Or is there a way to increase the number of TOP queries to like 300? Any information related to accessing these low executed stored procedures would be appreciated.

[Morelz]

Hi Marcie

Not entirely sure if the Db Collector stores all stored procedures, or if it only at most stores the top 200.

What you can do is, on the screen with top queries/procedures, open developer options in your browser and check the API Call it makes, you will see the value of query size in the payload, you can then make an API call and just amend the payload to increase the size to e.g 300.

This will only work if AppD actually stores the data obviously:)

Update : tested and it works if you increase the value in the size field:)

Ciao

[Marcie_Sirbaugh]

Thank you for the info,. sounds like this might just work.  Would you be able to send me a sample api call, pretty new to API's.

thx, Marcie

[Morelz · Accepted solution]

Hi Marcie

In short you would need to do the following

1. Create an API user in the controller with the relevant permissions that would be able to query the data you are after.

2. The API call will consist of 2 things, the first is to query the token API of AppD, to be used in any API call to get data from the controller.

https://<fqdn of controller>/controller/api/oauth/access_token

You can then use this token response in the follow up call to request the API of the top 300 queries

The API I used for queries is as below, using the POST method

https://<fqdn of controller>/controller/databasesui/databases/queryListData

With the payload as below, with the db_server_id specific to the database in your controller and the size parameter, and then start and end dates in epoch format.

'{"dbConfigId":-1,"dbServerId":<db_server_id>,"field":"query-id","size":300,"filterBy":"time","startTime":1658298356592,"endTime":1658301956592,"waitStateIds":[],"useTimeBasedCorrelation":false}'

When you do this, you will get the output in json 

Ciao