none
SCCM 2012 SP1 upgrade report issues RRS feed

  • Question

  • Hi All,

    I have upgraded SCCM 2012 to SP1. I have reporting role on CAS. After upgrade of SCCM 2012 to SP1, I found most of the report names has been prefixed with underscore and those reports are duplicated(means with same name there are 2 reports.One starting with underscore and other without underscore)

    Example :

    __All system resources for a task sequence deployment in a specific state

    __All system resources for a task sequence deployment that is in a specific state and that is available to unknown computers

    I would like to know whether this is a bug with SP1 of SCCM 2012 or anything can be done to resolve this.


    Thanks & Regards Uttam

    Thursday, February 7, 2013 6:03 PM

Answers

  • Hi Uttam,

    I ran into this issue as well. Like in your case, not all of our reports were duplicated, but a significant portion were. I haven't looked into why these duplicate reports were created during the upgrade, but I wrote a script to just get rid of them all. The duplicate reports were prefixed by double underscores in my environment and that's what this script will look for. If your reports are prefixed by a single underscore, you'll need to adjust the where clause in the 'Find all reports...' section by removing one of the two underscores.

    You'll need to know your SCCM site code and the server name to run this script.

    Good luck, hopefully this solves your problem.

    #######################################################################################################################
    # SCCM2012SP1-RemoveDuplicateSSRSReports.ps1
    # This script will connect to SSRS on a specified server and delete all reports that begin with a double underscore
    # Used for SSRS cleanup after SCCM 2012 SP1 installation
    # Script must be run from an account that has access to modify the SSRS instance
    # 2/15/2013 - Mike Laughlin
    #
    # Resources used in writing this script:
    # Starting point: http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell
    # API Documentation: http://msdn.microsoft.com/en-us/library/ms165967%28v=sql.90%29.aspx
    #######################################################################################################################
    
    # Define variables
    	$SiteCode = ""
    	$serverName = ""
    
    # Set the value of $noConfirm to $True only if you don't want to manually confirm report deletion. Use with caution.
    	$noConfirm = $False
    
    # Safeguard	
    	If ( $SiteCode -eq "" -or $serverName -eq "" ) { Write-Host "Enter the required information for the SiteCode and serverName variables before running this script." -ForegroundColor Red -BackgroundColor Black ; Exit }
    
    # Connect to SSRS
    	$ssrs = New-WebServiceProxy -uri http://$serverName/ReportServer/ReportService2005.asmx?WSDL -UseDefaultCredential
    
    # Get a listing of all reports in SSRS
    	$reportFolder = "/ConfigMgr_" + $SiteCode
    	$reports = $ssrs.ListChildren($reportFolder, $True)
    	
    # Find all reports starting with double underscores
    	$reportsToDelete = $reports | Where { $_.Name.Substring(0,2) -eq "__" }
    	
    # Quit if no reports are found
    	If ( $reportsToDelete.Count -eq 0 ) { Write-Host "No reports found. Quitting." ; Exit }
    	
    # Show a listing of the reports that will be deleted
    	Write-Host "The following reports will be deleted from SSRS on" $serverName":`n"
    	$reportsToDelete.Name
    	Write-Host "`nTotal number of reports to delete:" $reportsToDelete.Count "`n"
    	
    # Get confirmation before deleting if $noConfirm has not been changed
    	If ( $noConfirm -eq $False ) 
    	{ 
    		$userConfirmation = Read-Host "Delete these reports from" $serverName"? Enter Y or N"
    		If ( $userConfirmation.ToUpper() -ne "Y" ) { Write-Host "Quitting, reports have not been deleted." ; Exit }
    	}
    	
    # Delete the reports
    	$deletedReportCount = 0
    	
    	Write-Host "Beginning to delete reports now. Please wait."
    	ForEach ( $report in $reportsToDelete ) { $ssrs.DeleteItem($report.Path) ; $deletedReportCount++ } 
    	Write-Host "Reports have been deleted. Total number of deleted reports:" $deletedReportCount

    Standard disclaimer: While this script worked just fine for me in my environment, I make no guarantees that it will work anywhere else. I've attempted to make this script as user friendly and generic as possible, but it may require slight tweaking to work properly.

    • Marked as answer by Uttam blog Thursday, March 21, 2013 4:52 PM
    Friday, February 15, 2013 8:48 PM

All replies

  • In addition to the above query, I would like to add that after upgrade of SCCM 2012 to SP1, now there are 848 reports. All the reports are duplicated and there are 2 entries of those reports.

    Example :

    All drivers for a specific platform

    _All drivers for a specific platform


    Thanks & Regards Uttam

    Thursday, February 7, 2013 6:18 PM
  • I have not seen that before. If you do not have any custom reports, then I suggest you remove the SQL Reporting Services role (that will remove the reports) and install it Again. It takes less than 15 minutes.

    Kent Agerlund | My blogs: blog.coretech.dk/kea and SCUG.dk/ | Twitter: @Agerlund | Linkedin: Kent Agerlund | Mastering ConfigMgr 2012 The Fundamentals

    Thursday, February 7, 2013 7:10 PM
    Moderator
  • Thanks Kent for your answer. I will try your suggestions. But these kind of bugs are really strange. Is there any MS link or any other link where I can get the information regarding the bugs in SCCM 2012 SP1 which people has come acrossed.

    Thanks & Regards Uttam

    Thursday, February 7, 2013 8:04 PM
  • But these kind of bugs are really strange. Is there any MS link or any other link where I can get the information regarding the bugs in SCCM 2012 SP1 which people has come acrossed.


    I've upgraded 2 environments to SP1, never seen this thing. It's strange and something unique to your environment (it seems).  Following are the useful numbers which I found when I did some research.

    There are 59 default Categories available in SCCM 2012 SP1. There are 2 hidden categories. Following are the hidden categories available with CM 2012 SP1 “Endpoint Protection – Hidden”  and  “Software Distribution - Application Monitoring – Hidden”. Total number of reports available with ConfigMgr 2012 SP1 are 541. More details here.


    Anoop C Nair - @anoopmannur :: MY Site:  www.AnoopCNair.com :: FaceBook:  ConfigMgr(SCCM) Page :: Linkedin:  Linkedin<

    Monday, February 11, 2013 3:12 PM
  • Thanks Anoop. I think you are right this problem is specific to my environment. So, now I am planning to remove SQL Reporting Services role from the server and readd it as Kent suggested.

    Thanks & Regards Uttam

    Tuesday, February 12, 2013 12:05 PM
  • Hi,

    I have removed Reporting services point and readded it back but still the problem persists. Any other thoughts?


    Thanks & Regards Uttam

    Thursday, February 14, 2013 3:35 PM
  • Did you delete all the Reports from SSRS in-between deleting the RSP and adding it back in again? 

    http://www.enhansoft.com/

    Thursday, February 14, 2013 4:19 PM
    Moderator
  • No, I have not deleted any reports from SSRS. I have just removed the Reporting services point role and then readded it back.

    Thanks & Regards Uttam

    Thursday, February 14, 2013 6:34 PM
  • I would delete the reports in-between the removal and re-install.

    This blog post should help you out.

    http://support.enhansoft.com/Blogs/post/How-to-delete-folder-with-reports-manually.aspx


    http://www.enhansoft.com/

    Thursday, February 14, 2013 10:30 PM
    Moderator
  • Hi Uttam,

    I ran into this issue as well. Like in your case, not all of our reports were duplicated, but a significant portion were. I haven't looked into why these duplicate reports were created during the upgrade, but I wrote a script to just get rid of them all. The duplicate reports were prefixed by double underscores in my environment and that's what this script will look for. If your reports are prefixed by a single underscore, you'll need to adjust the where clause in the 'Find all reports...' section by removing one of the two underscores.

    You'll need to know your SCCM site code and the server name to run this script.

    Good luck, hopefully this solves your problem.

    #######################################################################################################################
    # SCCM2012SP1-RemoveDuplicateSSRSReports.ps1
    # This script will connect to SSRS on a specified server and delete all reports that begin with a double underscore
    # Used for SSRS cleanup after SCCM 2012 SP1 installation
    # Script must be run from an account that has access to modify the SSRS instance
    # 2/15/2013 - Mike Laughlin
    #
    # Resources used in writing this script:
    # Starting point: http://stackoverflow.com/questions/9178685/change-datasource-of-ssrs-report-with-powershell
    # API Documentation: http://msdn.microsoft.com/en-us/library/ms165967%28v=sql.90%29.aspx
    #######################################################################################################################
    
    # Define variables
    	$SiteCode = ""
    	$serverName = ""
    
    # Set the value of $noConfirm to $True only if you don't want to manually confirm report deletion. Use with caution.
    	$noConfirm = $False
    
    # Safeguard	
    	If ( $SiteCode -eq "" -or $serverName -eq "" ) { Write-Host "Enter the required information for the SiteCode and serverName variables before running this script." -ForegroundColor Red -BackgroundColor Black ; Exit }
    
    # Connect to SSRS
    	$ssrs = New-WebServiceProxy -uri http://$serverName/ReportServer/ReportService2005.asmx?WSDL -UseDefaultCredential
    
    # Get a listing of all reports in SSRS
    	$reportFolder = "/ConfigMgr_" + $SiteCode
    	$reports = $ssrs.ListChildren($reportFolder, $True)
    	
    # Find all reports starting with double underscores
    	$reportsToDelete = $reports | Where { $_.Name.Substring(0,2) -eq "__" }
    	
    # Quit if no reports are found
    	If ( $reportsToDelete.Count -eq 0 ) { Write-Host "No reports found. Quitting." ; Exit }
    	
    # Show a listing of the reports that will be deleted
    	Write-Host "The following reports will be deleted from SSRS on" $serverName":`n"
    	$reportsToDelete.Name
    	Write-Host "`nTotal number of reports to delete:" $reportsToDelete.Count "`n"
    	
    # Get confirmation before deleting if $noConfirm has not been changed
    	If ( $noConfirm -eq $False ) 
    	{ 
    		$userConfirmation = Read-Host "Delete these reports from" $serverName"? Enter Y or N"
    		If ( $userConfirmation.ToUpper() -ne "Y" ) { Write-Host "Quitting, reports have not been deleted." ; Exit }
    	}
    	
    # Delete the reports
    	$deletedReportCount = 0
    	
    	Write-Host "Beginning to delete reports now. Please wait."
    	ForEach ( $report in $reportsToDelete ) { $ssrs.DeleteItem($report.Path) ; $deletedReportCount++ } 
    	Write-Host "Reports have been deleted. Total number of deleted reports:" $deletedReportCount

    Standard disclaimer: While this script worked just fine for me in my environment, I make no guarantees that it will work anywhere else. I've attempted to make this script as user friendly and generic as possible, but it may require slight tweaking to work properly.

    • Marked as answer by Uttam blog Thursday, March 21, 2013 4:52 PM
    Friday, February 15, 2013 8:48 PM
  • Had the same issue as Uttam. I reinstalled the Reporting Role and I ran Mike's script. After this the duplicates were gone and my reports started working again (was not able to run any reports after the upgrade). Thanks Mike.

    Wednesday, May 29, 2013 4:26 PM
  • You're welcome, I glad it worked out for you. That was my first TechNet post, so I'm quite fond of it.
    Wednesday, May 29, 2013 6:11 PM
  • I have this same issue after installing Cumulative Update 2 in my production environment (SP1). Not all reports are duplicated, but some of them are.

    This is really a weird issue. I had no problems when installing CU1, but now with CU2 I have this issue.

    Regards,


    Carl

    Tuesday, July 9, 2013 8:06 PM
  • I have this same issue after installing Cumulative Update 2 in my production environment (SP1). Not all reports are duplicated, but some of them are.

    This is really a weird issue. I had no problems when installing CU1, but now with CU2 I have this issue.

    Regards,


    Carl


    I just recently applied CU2 myself and the report duplication issue didn't happen to me this time (nor did it happen when I applied CU1). I still have no idea what causes a few of us to get duplicated reports when we upgrade while other people do not. I never reported the issue to Microsoft directly, since I just deleted the duplicates with my script, so they may not even be aware of the problem.

    Don't retire TechNet!

    Tuesday, July 9, 2013 8:28 PM
  • I also deleted the ones that had the underscores in front of them and everything is working. I did not see this issue in my test-environment and I have no idea why this happened on the production-side. Anyway, it does not seem to be a big issue, just somewhat annoying.

    Regards,


    Carl

    Thursday, July 11, 2013 12:15 PM
  • Mike,

    Your script worked perfect for me. Ran the script and all the duplicates are gone. I did not have to do any other steps as mentioned in this thread. Thanks alot!

    Bryan McCuen

    Thursday, July 18, 2013 8:56 PM
  • Cheers Bryan, I'm glad it helped.

    Don't retire TechNet!

    Friday, July 19, 2013 3:39 AM
  • I can confirm I also had numerous duplicate reports (73).  With a bit of tweaking for SSL and SQL 2012 however the script above worked like a champ.

    Thanks Mike!

    Friday, November 1, 2013 4:43 PM
  • Tuesday, November 12, 2013 1:09 AM
  • Worked a treat,

    Thank you for the good work!!

    Thursday, November 10, 2016 3:53 PM
  • This seems like an upgrade bug to me. We manage two different hierarchies and we had report names starting with __ in both enviroments. Strange enough, in hierarchy A the __ reports were different than those in hierarchy B.

    According to the names they were duplicates but some got a little renamed (All Windows 8 Apps > All Windows Apps)

    I've deleted all ___ ones and everything looks fine :)

    Monday, May 29, 2017 12:08 PM