Advertisement

Custom Report to check the Status of Predefined Site Maintenance Tasks

This is a long pending post. I’ve created a custom report to get the status of the SCCM 2007 and ConfigMgr 2012 predefined site maintenance tasks. I’ve uploaded the RDL file to technet gallery which can be imported to your SSRS.  Download RDL file form here. Run the report and be aware of the status of your maintenance tasks on SCCM 2012 and 2007 site servers (on weekly or daily basis).

Completion Status ZERO (0) means the task finished successfully. There is a table called SQLTaskStatus which holds this data. We can also add a column to find the time a task took to run. Checking the status of maintenance tasks is always important and should be part of  Weekly health checks. This post is a continuation of my old post how to find out the status of Predefined Maintenance Tasks.

I couldn’t find the view related to SQLTaskStatus table so I’ve created the custom report with the above mentioned table rather than view.

Two potential issues :-

1. Go to Manage option of the report (from SSRS web report). Click on Manage –> Data Sources –> Click on A Shared Data Source and browse to connect to /ConfigMgr_SiteCode/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602} and APPLY. Run the report. It should work.

RDL Report

RDL Report1

 

 

RDL Report2

2. Another problem could be with permission. Probably, you don’t have access on tables. Can you try running the following query from SQL management studio.

select * from SQLTaskStatus

CUSTOM Report Predefined Maintenance Task

Custom Report for Predefined Maintenance Task

About Author 

Anoop is Microsoft MVP and Veeam Vanguard ! He is a Solution Architect on enterprise client management with more than 13 years of experience (calculation done on the year 2014) in IT. He is Blogger, Speaker and Local User Group Community leader. His main focus is on Device Management technologies like SCCM 2012,Current Branch, Intune. He writes about the technologies like SCCM, SCOM, Windows 10, Azure AD, Microsoft Intune, RMS, Hyper-V etc...

    Find more about me on:
  • googleplus
  • twitter
  • facebook
  • linkedin
  • youtube
Posted in: ConfigMgr (SCCM), SCCM 2012

15 Comments

  1. Dil says:

    I tried to import this rdl file in to our SSRS but getting below error:
    “The report definition is not valid. Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition’ which cannot be upgraded. (rsInvalidReportDefinition) Get Online Help”

  2. Todd Hamlin says:

    I’m getting the following error:

    The SELECT permission was denied on the object ‘SQLTaskStatus’, database ‘CM_DI1’, schema ‘dbo’.
    —————————-
    Query execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand)
    —————————-
    An error has occurred during report processing. (rsProcessingAborted)

    • Anoop says:

      One problem could be :-
      Go to Manage option of the report. Click on Manage –> Data Sources –> Click on A Shared Data Source and browse to connect to /ConfigMgr_SiteCode/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602} and APPLY. Run the report. It may work.

      Another problem could be with permission. Probably, you don’t have access on tables. Can you try running the following query from SQL management studio.
      select * from SQLTaskStatus

      • Todd Hamlin says:

        After uploading the report the first thing I did was configure that data source, so i’m guessing its a permission issue too. Our DBA locks us down pretty good 😉 Bummer. Looks like you built a handy report. Thanks for sharing.

      • Anoop says:

        All the best to you. As I know how it would be (dealing with SQL team) 🙂

  3. Hey Anoop, great report! Are we supposed to be able to see the status of ‘all’ site maintenance tasks?

  4. Ah, I was only seeing 13 tasks in the report. We have a CAS and primary site. It was only pulling the 13 tasks in the CAS site. Any tips on getting it to pull from the primary site as well?

  5. Nayan says:

    Hi Anoop,

    Can you help me !!!

    How to get, Patch installation compliance report including date and time through SCCM custom reporting.

  6. Sendahl says:

    This works great once the data source was changed, many thanks Anoop,

Leave a Comment and Contact Anoop