Powershell, get me job status!
Ever since I heard of Powershell, I have been wanting to learn it and make use of it. Windows’ own powerful command-line scripting, how much more geekier can it get for a geek? When the need to automate some of my daily/weekly tasks became necessary due to the increasing number of instances I have to monitor, I decided to finally learn powershell.
That is a story for another day, I am not going to expound on how to use Windows Powershell, you have better resources than my post; Windows Powershell In Action. Instead, I will show one of the automation project that gave me some difficulties when I first used powershell.
Many sites and books show you how to retrieve job status from multiple servers using powershell. My approach will take on the following :-
1) Retrieve job status from all servers
2) Insert them into a table in a centralized database
3) Create a job or use Reporting Services to export the report to my email every morning
Step 1 and 2 will have to be performed using a powershell-based job in a centralized instance. This instance will have to be using a SQL Server 2008 version since there is no support for powershell jobs in SQL Server 2005. Now, the biggest problem faced by me was the fact that all the servers may be sitting in a different domain.
You can’t use windows authentication to connect to the servers, and so those login credentials will have to be stored in the centralized database itself and retrieved when needed. “Hold on”, some of you may say, “that is a huge security risk!”. While I agree that it is most definitely not best practice to do this, some of you may find that there are no other options and it is really necessary to do this. There may be a better way to do this, if you know of it, please do do contact me and correct me.
Right, now let me show you the script before going in-depth.
| # Include Files . C:\PowershellScripts\OfficialScripts\centralMonitorConfig.ps1 . C:\PowershellScripts\OfficialScripts\fn_get_servers.ps1 # Load SMO extension # Get List of sql servers to check foreach($row in $dt) # Create an SMO Server object #This sets the connection to mixed-mode authentication #This sets the login name #This sets the password # SQL Connection String # For each jobs on the server # Set up SQLCommand object # Configure TSQL $cmd.ExecuteNonQuery() | out-null |
Please bear in my mind, this is just a sample script. If you are going to use this in a production environment, please include all the necessary error-handling and encrypt your stored login credentials.
OK, into the details.
# Include Files
. C:\PowershellScripts\OfficialScripts\centralMonitorConfig.ps1
. C:\PowershellScripts\OfficialScripts\fn_get_servers.ps1
These few lines include some standard configuration values and functions.
CentralMonitorConfig.ps1 – Sets the name of the centralized instance and database that stores the retrieved job status
fn_get_servers.ps1 – Accepts the server name parameter and retrieve the login credentials
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null;
# Get List of sql servers to check
$dt = getServers $centralserver $centraldatabase “Select server_name, login_usr, login_password from server_instance”
Then we load the necessary SMO object and use the aforementioned function, fn_get_servers to retrieve the server login.
foreach($row in $dt)
{
#Set server connection parameters
$sqlserver = $row.server_name
$usr = $row.login_usr
$pwd = $row.login_password
The server information retrieved from the function will be passed to an object variable, $dt and a loop will run through each server in the list, retrieving the login credentials for each server.
# Create an SMO Server object
$srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver;
#This sets the connection to mixed-mode authentication
$srv.ConnectionContext.LoginSecure=$false;
#This sets the login name
$srv.ConnectionContext.set_Login($usr);
#This sets the password
$securePassword = ConvertTo-SecureString $pwd -AsPlainText -Force
$srv.ConnectionContext.set_SecurePassword($securePassword)
Next, we create the SMO object for the server and pass the login credentials into the ConnectionContext property of the server. Before that, we ensure that the mixed mode authentication is set. We also convert the password to a secure string before passing it into the server object.
# SQL Connection String
$conn = New-Object System.Data.SqlClient.SqlConnection(“Server=$centralServer;Database=$centralDatabase;Integrated Security=SSPI”)
$conn.Open() # Open SQL Connection
Here, we open the connection to the centralized instance for inserting the information.
# For each jobs on the server
foreach($job in $srv.JobServer.Jobs) {
$jobName = $job.Name;
$jobLastRunOutcome = $job.LastRunOutcome;
$jobLastRunDate = $job.LastRunDate;
$reportDate = Get-Date;
We have reached the meaty part, this is where we loop through all the jobs in each of the server in our list, and retrieved the job status information that we required. We also used a built-in function to retrieve the current date for reporting purpose.
# Set up SQLCommand object
$cmd = $conn.CreateCommand()
# Configure TSQL
$cmd.CommandText =“INSERT INTO [dbo].[Job_Status] ([server_ip], [job_name], [job_last_run_date], [job_last_run_outcome], [report_date]) `
VALUES (‘$sqlserver’, ‘$jobName’, ‘$jobLastRunDate’, ‘$jobLastRunOutcome’, ‘$reportDate’)”
$cmd.ExecuteNonQuery() | out-null
We then create a SQL command object and pass the T-SQL insert query into it. Finally we told it to execute and do not display any message.
}
# Close SQL Connection
$conn.close();
}
At last, we complete the job retrieval loop and server retrieval loop and close the connection.
That’s it! All’s left is to create the job in the centralized instance and it will insert the data into the defined table as required by your schedule. As mentioned earlier, you can even create a custom report and schedule it to be sent to your email. Don’t forget to create a data archival job to clear old records from the table or else those job status will pile up.
Recent Comments