It's just Emanuele showing you what you can do in the Microsoft Data Platform

A PowerBI Report for SQL Server Agent Jobs

A

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

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

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
  4. Time brush filter: In order to have a finer control over the time interval that you want to analyze

Job Anomalies

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

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

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!

About the author

Emanuele Meazzo

6 comments

  • Hello Emanuele, I guess this is for one particular server, I want to view this data for all of my production servers, We are ready to copy the job history on centralized repository, Do you have any such report for all servers that would be great.
    I really like this power BI report

    • Hi Lakshmi,

      I think I don’t have anything “ready to go”, but it should be pretty easy to implement, just add a column for the Server name in the Job tables of your repository and add a filter at the Report Level in PowerBi filtering for a specific server.
      If you want to see an overall status of all the servers, however, I think you’re better off creating additional report page(s) for that

  • Hi Emanuele,

    This looks great! I’m having an issue with the code though and can’t seem to figure out why.

    “Msg 102, Level 15, State 1, Procedure sp_BlitzJobinfo, Line 24 [Batch Start Line 0]
    Incorrect syntax near ‘;’

    The very first instance of ‘GO’ keeps giving me this error, but I don’t know why. Any ideas? I would LOVE to use this PBI report

It's just Emanuele showing you what you can do in the Microsoft Data Platform

Emanuele Meazzo

My Social Profiles

If you're interested in following me around in the social world, here are some URLs for you