Using the new SQLServer Powershell module to get SQL Agent Job Information

So with the July Release of SSMS everything changed for using PowerShell with SQL. You can read the details here As I mentioned in my previous post the name of the module has changed to sqlserver

This means that if you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to be Import-Module SqlServer in order to take advantage of the new provider functionality and new CMDLETs. The new module will be installed to “%Program Files\WindowsPowerShell\Modules\SqlServer” and hence no update to $env:PSModulePath is required.

You can download the latest SSMS release here Once you have installed and rebooted you can start to look at the new Powershell CMDlets

Take a look at cmdlets

Today I want to look at agent jobs

getcomand sqlagent

So I decided to see how to gather the information I gather for the DBADatabase as described here

This is the query I use to insert the data for the server level agent job information.

So Get-SQLAgentJob looks like the one I need. Lets take a look at the help. This should be the starting point whenever you use a new cmdlet

Which states

Returns a SQL Agent Job object for each job that is present in the target instance of SQL Agent.

That sounds like it will meet my needs. Lets take a look


I can get the information I require like this

NOTE – That code is for PowerShell V4 and V5, if you are using earlier versions of PowerShell you would need to use

But to make the code more performant it is better to do this


Using Measure-Command showed that this completed in
TotalSeconds : 0.9889336
Rather than
TotalSeconds : 2.9045701

Note that

Does not work. I had to check the properties using

Which showed me

IsEnabled Property bool IsEnabled {get;set;}

So I tested this against the various SQL versions I had in my lab using this code

Here are the results
job data table

I also had a look at Get-SQLAgentJobHistory Lets take a look at the help


Returns the JobHistory present in the target instance of SQL Agent.

This cmdlet supports the following modes of operation to return the JobHistory:

  1. By specifying the Path of the SQL Agent instance.
  2. By passing the instance of the SQL Agent in the input.
  3. By invoking the cmdlet in a valid context.

So I ran

And got back a whole load of information. Every job history available on the server. Too much to look it immediately to work out what to do

So I looked at just one job

And got back the last months worth of history for that one job as that is the schedule used to purge the job history for this server So then I added -Since Yesterday to only get the last 24 hours history


The Since Parameter is described as

-Since <SinceType>

A convenient abbreviation to avoid using the -StartRunDate parameter.
It can be specified with the -EndRunDate parameter.

Do not specify a -StartRunDate parameter, if you want to use it.

Accepted values are:
– Midnight (gets all the job history information generated after midnight)
– Yesterday (gets all the job history information generated in the last 24 hours)
– LastWeek (gets all the job history information generated in the last week)
– LastMonth (gets all the job history information generated in the last month)

When I run

I get

Count : 3

And if I run

I get

agent job out gridview

Which matches the view I see in SSMS Agent Job History


So Get-SqlAgentJobHistory will enable you to use PowerShell to gather information about the Job history for each step of the Agent Jobs and also the message which I can see being very useful.

Come and join us in the SQL Community Slack to discuss these CMDLets and all things SQL Community


Microsoft are engaging with the community to improve the tools we all use in our day to day work. There is are two Trello boards set up for YOU to use to contribute for SQLPS sqlserver PowerShell module for SSMS

Go and join them and upvote YOUR preferred choice of the next lot of CMDlets


We have also set up a SQL Community Slack for anyone in the community to discuss all things related to SQL including the Trello board items and already it seems a good place for people to get help with 150+ members in a few days. You can get an invite here

Come and join us

3 thoughts on “Using the new SQLServer Powershell module to get SQL Agent Job Information

  1. Hi Rob, how do you feel this compares to using the SMO for agent access? I’ve used this previously and have a bunch of modules that i use to create/copy jobs and so on, just trying to gauge whether its worth reviewing and amending to take advantage of the new cmdlets.

  2. Hi Matthew,

    Thank you for the comment

    These cmdlets make it easier for people to work with SQL Agent Jobs via Powershell. I believe that under the hood they are using smo anyway. I hope that they will encourage SQL people new or less experienced with Powershell to begin to use Powershell to manage their SQL instances

    Consider where you are running your scripts also. To take advantage of the new cmdlets right now, you have to install the July release of SSMS

    I would say that for now carry on using your modules but keep an eye on the new CMDLets as they come out and get used to using them as well at least in test labs.

    Also, if something isn’t broken do you need to fix it? Your scripts are working now keep using them. I have not re-visited any scripts to make use of the new CMDlets yet but will start developing new ones to use them

    Don’t forget that Microsoft talk about pairs of verbs – If we have a Get-Something then there should be a Set-Something. They have released a load of Get- CMDLets with this release I hope that they will bring some Set- CMDLets out soon (especially if people vote them up on the Trello board and connect

Please feel free to comment on this post. All comments are moderated first before appearing on the site