A PowerBI Report for SQL Server Agent Jobs

As my “community service” this month, I’ve worked on this neat (I hope!) PowerBI report (and companion stored procedure) to easily analyze all what you wanted to know about your Agent Jobs execution, but were too afraid to ask to the standard Reports in SSMS.

This is part of the FirstRespondersKit toolkit, which I encourage you to check out.

Installation:

1. Install the sp_BlitzJobInfo stored procedure wherever you want
   • If you install this in the master database you can launch it from any database ( remember, “sp_” stands for “special”, not for “stored procedure”) or you can install it wherever you like.
     
2. Open Up the PowerBI template file
   • It will ask you for the name of your instance, and then the Database name where you’ve installed the procedure
3. Enjoy
   • There wasn’t really anything special as far as the installation goes, but I like making lists.

Report Pages Overview

Jobs Page

1. Job Overview Information: Lists all the major information that you need to know about your job: Name, Status, Schedule, Run Date, Alerts, etc..
2. Performance Graph: Shows you the performance of the selected job(s) over time
3. Duration Overview: A drill-down graph to quickly identify the longest running jobs and the critical steps inside them
4. Time filter, to select the period of time to analyze
5. Job Filter List: A list to select only the jobs that you care about
6. Job Search: Don’t want to scroll down the list? Too many Jobs? Use the search function!
7. Category Filter: Filter by category (!)
8. Run Duration Filter: Filter by Jobs running for longer than X seconds
9. Job Status: Filter Enabled/Disabled Jobs
10. Schedule Filter: To filter only the jobs with an active schedule, or viceversa

Job Execution Timeline

This report page is when you want to know what the hell was going on in a certain period of time, and how things proceeded.

1. Job Steps Timeline: With all the details you need, Job Name, Step Name, Duration, etc..
2. Job Execution Tooltip: By hovering over a row in the table, you’ll see the complete timeline of all the steps of the related job execution, in order to have an overview of the Job on top that specific step execution
3. (to 7): The usual filters
8. Time brush filter: In order to have a finer control over the time interval that you want to analyze

Job Anomalies

This page shows the top 5 Jobs that are deviating from the average (related to their absolute value)

1. Job Graphs: The duration graph of the top 5 deviant jobs
2. Job Impact on overall duration: The overall impact of each job on the overall job duration on your instance
3. The usual filters

Maintenance Plans

If you really have to use maintenance plans, this as least allows you to check on them:

1. Plan Filter/List: All your plans are here
2. DatabaseName: All the databases that apply to the selected plan(s) are here
3. Task: All the tasks that run on the above selected Maintenance Plan(s) and Database(s)
4. Task Details: All the details of the selected stuff above
5. Average Plan Duration: A duration overview of the selected plan(s)
6. Plan Duration Over Time: The plan duration over time

Top N Job List

This is the same as the SSMS “Top 20 Jobs by *” report (literally, I’ve used the same queries that SSMS uses)

1. Top Jobs by Duration in the Last Week:  ¯\_(ツ)_/¯
2. Top Jobs by Frequency in the Last Week:  ¯\_(ツ)_/¯
3. Top Jobs by Failures in the Last Week:  ¯\_(ツ)_/¯
4. Job Duration Graph Tooltip: The added value for this report vs the standard one, by hovering on each row you’ll see the duration graph for the job

Downloads and Support

You can download the package with the stored procedure and the PowerBi template Here:

Head to the FirstRespondersKit GitHub page for support!