How can I get list of runbooks that uses a particular Database using PowerShell in a runbook

Question

Hi ,

we have created plenty of runbooks to load data to our SQL warehouse. and the runbooks are keep increasing due to business need.

we are in need of achieving particular task. that requires list of runbooks that is using the specific xyz database. currently we have hardcoded the list of runbooks and achieved our task. but the problem is every time new runbook is created we also need to update the list manually. so we are hoping to maintain the list dynamically.

is there way we can get the list of runbooks which are using particular Database using powershell script used in a Runbook.

Answer 1

@Ponkam, Sumanth (Cognizant) Thank you for posting this question on Microsoft Q&A.

There isn't any out of the box solution to get the list of runbooks connecting to a particular SQL Server. One way through which this can be achieved is as follows:

1. Get a list of all runbooks in automation account.
2. Get the content of runbooks (the PS script)
3. Perform a content match to see if you are referencing the SQL server (using its name/connection string etc.) OR if the sql server name and connection details are stored in an Automation variable, perform a content match to see if that variable is referenced in the script.

The following script provides the code to perform content match for a particular "string" in the runbook code - runbooks/Utility/AzRunAs/IdentifyRunAsRunbooks.ps1

Note, in this sample code, line 43 does a content match for the string "AzureRunAsConnection" to check if this connection is used in the script.

For your scenario, this string will be replaced with "SQL server name" or the "Automation variable name", depending on how you are referencing the sql server.

Hope this helps.

If the answer did not help, please add more context/follow-up question for it. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.