0

Using DBA Tools to gather a variety of information from a list of SQL Servers, particularly their patch level. I have a larger Powershell script that allows for selecting a set of options by entering their relevant number (1,2,3,4,5). I have been able to get database backups working on a single SQL server through this tool, but I cannot get a list of patch levels using DBA Tools. Specifically, the commands run but I don't receive any output through the tool. Interestingly, the same command does output when I use it via terminal on a single instance of SQL Server.

The option I am working to fix in my larger script is '3' - Check Patches. I verified the basic DBA Tools syntax works on a single instance via terminal as well as receiving a simple text output when option '3' is selected (just to confirm it is running as expected). The command below works and outputs in terminal like I want, but the larger function below it using the same syntax does not. The only difference being operating on a list of servers instead of a single instance:

WORKS:

Get-DBAInstalledPatch -ComputerName SQLINSTANCENAME

Unfortunately when I plug this same logic into a function, the output I receive is the following:

DOESN'T WORK: Function Input:

# Define a list of SQL Server instances
    $instances = @(
       "Server1",
       "Server2"
        )

            # Iterate through the list of instances
            foreach ($instance in $instances) {
                Write-Host "Checking patch level for instance: $instance"
                
                # Retrieve the latest patch level for the instance
                $patchLevel = Get-DbaInstalledPatch -ComputerName $instance 
                
                # Output the patch level
                if ($patchLevel) {
                    Write-Host "Latest patch level: $patchLevel"
                } else {
                    Write-Host "Unable to retrieve patch level for instance: $instance"
                }
}

Output:

Select Operation...: 3
Checking patch level for instance: Server1
Latest patch level:
Checking patch level for instance: Server2
Latest patch level:

Why does no data populate for the $patchLevel variable when used in the script?

rickandm00rty
  • 1
  • 1
  • Doesn't `Get-DbaInstalledPatch` return a list of objects? What's the output of `Get-DBAInstalledPatch -ComputerName SQLINSTANCENAME | Get-Member`? – AlwaysLearning Jun 02 '23 at 08:34
  • @AlwaysLearning That worked!!! The formatting is certainly off as it pulls more data than I need but I can clean that up with some googling. I had no idea that commands through terminal automatically pulled the object members. Not sure if this is the right place to ask, but do you know why the same command functions differently in terminal vs. a script? I do not use `Get-member` when I just use DBA Tools in the terminal and the output is what I am looking for. Thanks again!! – rickandm00rty Jun 02 '23 at 14:26
  • I don't think it's a case of terminal vs. script as much as `Write-Host` expects scalar values. `Get-DbaInstalledPatch` returns **all** the service packs, cumulative updates or patches installed on an instance. – AlwaysLearning Jun 02 '23 at 14:55
  • Fantastic explanation. As you can tell I don't script very often so my understanding of these dynamics is poor. I'll need to do this more often. Thanks again for your help @AlwaysLearning :) – rickandm00rty Jun 02 '23 at 17:33

0 Answers0