Server goes dorment after 5 instances

[Adrian]

Afternoon all,

I’m attempting to write a very simple PS script to query my SQL Agents failed job history to create a report. SQL instances are on several servers but in the same domain. After queering the filth or so server in my server list, the process appears to stall (ise output stops)

The server I’m running the script on is our CMS server and looking at the VM performance console I was expecting a spike in CPU or memory utilization, but nothing.

$JobResults = Get-SqlAgentJobhistory -ServerInstance $server -JobName "STIG SQLHasherCheck" -OutcomeType Fail | Sort-Object RunDate -Descending | Select-Object -Property JobName, RunDate @{Name="RunStatus"; Expression{}if($_.RunStatus -eq 0) {"Failed"}else{"Succeeded"}}} -First 1
$JobName = $JobResults.JobName
$JobRunDate = $JobResults.RunDate.toString("MMddyyyy h:mm tt")
$JobOutcome = $JobResults.RunStatus

[grey0ut]

Can you share more of your code, particularly the loop that you’re using?

$Server is in your code example but we can’t see where it was defined.
Also, I don’t think your calculated property syntax is correct. You’ve got Exression{} instead of Expression={<yourcode>} then you have an if/else statement. I suspect this is supposed to be inside your Expression scriptblock.

[Adrian]

@Adrian

$Serverlist = "c:\temp\serverlist.txt"  #input list of servers

$results = foreach ($server in $ServerList) {
 try {
 $JobResults = Get-SqlAgentJobhistory -ServerInstance $server -JobName "STIG       SQLHasherCheck" -OutcomeType Fail | Sort-Object RunDate -Descending | Select-Object -Property JobName, RunDate @{Name="RunStatus"; Expression{}if($_.RunStatus -eq 0) {"Failed"}else{"Succeeded"}}} -First 1
$JobName = $JobResults.JobName
$JobRunDate = $JobResults.RunDate.toString("MMddyyyy h:mm tt")
$JobOutcome = $JobResults.RunStatus
}catch{
  write-host "Errors:  $($_.Exception.Message)"
}
}

I’ve added the code loop above

[vern_anderson]

If you run the following command does it say the “ServerInstance” parameter accepts strings or is it looking for a specific object?

Get-Help Get-SqlAgentJobhistory -Full

Does it say something like [[-ServerInstance] <string>] ? or is it looking for an object.

Some other things to keep in mind depending on your level of SQL knowledge:

1. Not every SQL instance is the servers name instances can be named whatever the DBA wants to call it. Yes by default if you do not name it it defaults to the servers name.
2. MSSQL can have more than one instance running on a given server so you will only be finding the “default” instance

Let us know what the HELP says about that parameter.

[vern_anderson]

UPDATE:

I was able to find the help for that command online.

Get-SqlAgentJobHistory (SqlServer) - SQL Server PowerShell

The good news is it does accept string values. However, you still need to consider that not every instance will match the name of the server hostname itself.

[tonyd]

@Adrian

Expression{}if($_.RunStatus -eq 0) {"Failed"}else{"Succeeded"}}

Did you fix this per greyOut’s suggestion?

[Adrian]

My apologies to all,

The line of code I transposed from my CMS server powershell ISE is missing teh ‘=’ in Expression{} line

Here is the correct line of code

$JobResults = Get-SqlAgentJobhistory -ServerInstance $server -JobName "STIG SQLHasherCheck" -OutcomeType Fail | Sort-Object RunDate -Descending | Select-Object -Property JobName, RunDate @{Name="RunStatus"; Expression={}if($_.RunStatus -eq 0) {"Failed"}else{"Succeeded"}}} -First 1

But shouldn’t the applet call return an error and not hang-up/freeze the process?
I get nothing returned nor any information in the ‘catch’ to what happened to stall the process. The stalling is what I need to resolve, then if my syntax needs tweaking for multiple instances those corrections can be made

[grey0ut]

I know you say the stalling is what you need to resolve, but this code is still wrong:

$JobResults = Get-SqlAgentJobhistory -ServerInstance $server -JobName "STIG SQLHasherCheck" -OutcomeType Fail | Sort-Object RunDate -Descending | Select-Object -Property JobName, RunDate @{Name="RunStatus"; Expression={}if($_.RunStatus -eq 0) {"Failed"}else{"Succeeded"}}} -First 1

there’s no comma after “RunDate” and the Expression statement is still incorrect.

It needs to look like this:

$JobResults = Get-SqlAgentJobhistory -ServerInstance $server -JobName "STIG SQLHasherCheck" -OutcomeType Fail | Sort-Object RunDate -Descending | Select-Object -Property JobName, RunDate, @{Name="RunStatus"; Expression={if($_.RunStatus -eq 0){"Failed"}else{"Succeeded"}}} -First 1

As for the stalling, have you checked on what @vern_anderson said about the SQL server name not necessarily being the same as the actual server name?

Also just to make sure “serverlist.txt” is just a text file of server names right? One name per row?