SHIFT

--- Sjoerd Hooft's InFormation Technology ---

User Tools

Site Tools


sccmreports
Differences

This shows you the differences between two versions of the page.

Link to this comparison view

sccmreports [2019/02/15 15:14] (current)
sjoerd created
Line 1: Line 1:
 += SCCM Custom Report Subscriptions =
  
 +Environment: ​
 +* SCCM 2012 (Version 1810) on Windows Server 2012 R2 (Version 6.3 Build 9600)
 +* MS SQL 2012 SP4 (probably) on Windows Server 2012 R2 (Version 6.3 Build 9600)
 +
 +Goal: Create a SCCM Custom Report subscription with custom dates. One of the whole previous month and one of yesterday and today. ​
 +
 += Setup E-Mail Server =
 +
 +I want to get the reports by email, so you need to setup the email server properties on the SQL Reporting Server. Read [[https://​docs.microsoft.com/​en-us/​sql/​reporting-services/​subscriptions/​e-mail-delivery-in-reporting-services?​view=sql-server-2017|here]] and [[https://​docs.microsoft.com/​en-us/​sql/​reporting-services/​install-windows/​e-mail-settings-reporting-services-native-mode-configuration-manager?​view=sql-server-2017#​configure-report-server-e-mail-using-the-reporting-services-configuration-manager|here]] for more information on how to do that, even though the links are for SQL Server 2017 they will get you going for SQL 2012 as well. Follow these steps to configure the email server:
 +
 +* On the SQL server, start Reporting Services Configuration Manager and connect to the reporting server. ​
 +* Go to the E-mail Settings and provide:
 +** Sender address
 +** SMTP server ​
 +
 +Save the settings and exit the Reporting Services Configuration Manager. ​
 +
 += SCCM Reports =
 +
 +SCCM comes with quite a few pre-configured reports. See [[https://​docs.microsoft.com/​en-us/​sccm/​core/​servers/​manage/​list-of-reports#​endpoint-protection|here]] for more information on all available reports, and specifically the endpoint reports. ​
 +
 +== Report Builder ==
 +If you need to make adjustments to a report, never do that to the original report but save a copy under a different name and use that. However, I ran into a bug with SQL 2012 SP4 which caused the report builder unable to start. \\
 +
 +Error:
 +<​code>​
 +Application validation did not succeed. Unable to continue. ​
 +</​code>​
 +
 +Details error:
 +<​code>​
 +...
 +* Activation of http://​chlexm05/​ReportServer/​ReportBuilder/​ReportBuilder_3_0_0_0.application?​ReportPath=/​BERLIN/​Macola/​ActualVsStandardByItem resulted in exception. Following failure messages were detected:
 ++ File, interop.shdocvw.dll,​ has a different computed hash than specified in manifest.
 +...
 +</​code>​
 +
 +Now there are two hotfixes from Microsoft that claim to solve this error: [[https://​support.microsoft.com/​en-hk/​help/​4091266/​on-demand-hotfix-update-package-for-sql-server-2012-sp4|KB4091266]] and [[https://​www.microsoft.com/​en-us/​download/​details.aspx?​id=56490|KB4057116]]. Note that these are quite big software updates, and if you do not want to go through the entire process of patching and testing your SQL servers I recommend to implement the following quick workaround:
 +
 +* Download the patch [[https://​www.microsoft.com/​en-us/​download/​details.aspx?​id=56490|KB4057116]]
 +* Use [[https://​portableapps.com/​apps/​utilities/​7-zip_portable|7zip Portable]] to extract the patch
 +* Navigate to x64 -> setup
 +* Open sql_rs.msp file in 7zip (right click and select Open)
 +* Open PCW_CAB_Family01 in 7zip (right click and select Open)
 +* Extract Interop_ShDocVw_dll_deploy_64 to this directory: C:\Program Files\Microsoft SQL Server\MSRS11.SQL01\Reporting Services\ReportServer\ReportBuilder\RptBuilder_3
 +* Navigate to C:\Program Files\Microsoft SQL Server\MSRS11.SQL01\Reporting Services\ReportServer\ReportBuilder\RptBuilder_3
 +* Make a backup of the existing Interop.ShDocVw.dll.deploy ​
 +* Rename Interop_ShDocVw_dll_deploy_64 to Interop.ShDocVw.dll.deploy
 +
 +Now you can use Report Builder without a problem from SCCM 
 +
 +== Create Custom Report ==
 +
 +Below instructions are valid and tested on the Endpoint Protection Reports "​Infected Computers"​ and "​Antimalware Overall status and history":​
 +
 +* In SCCM, open the Monitoring workspace and navigate to Reporting -> Reports ​
 +* Select the Report you want to modify and click "​Edit" ​
 +* Click on the main button in the left top corner and select Save As to save a copy of the report with a custom name
 +* Expand the parameters and make the following changes:
 +** StartDate -> General -> Visisbility:​ Hidden
 +** EndDate -> General -> Visisbility:​ Hidden
 +* Expand the Datasets and find and open the StartEndDates dataset
 +** For previous month, change the query to: 
 +*** {{{select DATEADD(MONTH,​ DATEDIFF(MONTH,​ 0, GETDATE()) - 1, 0) as StartDate, DATEADD(MONTH,​ DATEDIFF(MONTH,​ 0, GETDATE()), -1) as EndDate}}}
 +** For yesterday and today, change the query to:
 +*** {{{select DATEADD(DAY,​ DATEDIFF(DAY,​ 0, GETDATE()), -1) as StartDate, ​ GETDATE() as EndDate}}}
 +** For the last week, change the query to:
 +*** {{{select DATEADD(DAY,​ DATEDIFF(DAY,​ 0, GETDATE()), -7) as StartDate, ​ GETDATE() as EndDate}}}
 +
 +Now save everything so you can create a subscription (schedule) for the report. ​
 +
 +> See http://​www.dataceptor.com/​sql-tricks for more SQL queries regarding dates 
 +
 += Create Subscription =
 +
 +* In SCCM, open the Monitoring workspace and navigate to Reporting -> Reports ​
 +* Select the Report you just created and click "​Create Subscription"​ and fill in these options:
 +** Report delivered by: Email
 +** To, cc and subject as you need them
 +** Comment: This is the body of the email, so adjust accordingly
 +** Description:​ This is the name of the subscription,​ you will see this name in the subscription properties. ​
 +** Include the report, in MHTML format
 +* Click next to create the schedule
 +* Click next to provide the subscription parameters:
 +** Collection Name: All Desktop and Server Clients
 +* Now save the report by finishing the wizard ​
 +
 +You can now wait for the report to arrive. ​
 +
 +== View Subscriptions ==
 +
 +To view all subscriptions,​ go to the Monitoring workspace and navigate to Reporting -> Subscriptions ​
 +
 +{{tag>​security}}
sccmreports.txt ยท Last modified: 2019/02/15 15:14 by sjoerd