How to install Data warehouse service point in SCCM Configmgr and get the historical data

 

Introduction:

Customer is running on SCCM Configmgr current branch 1806 and configured with site maintenance tasks to delete the aged data for X number days . when you configure the site maintenance task ,the data which is older than X number days get deleted from site database which is expected.

So customer has asked ,is there is way to know the clients that get deleted by site maintenance task or manual or other ways  .So basically whatever get deleted in the site database including devices ,inventory etc.,customer wanted to have a record at later stage.

In earlier versions of configmgr build prior to 1702 ,there is no straight way to do it unless ,you configure out of box solutions like ,bring the AD computer objects into SCCM and do querying but it doesn’t get you the Inventory info of the deleted devices except that,what computers got deleted.

With 1702 ,there is pre-release feature feature introduced called Data warehouse service point .Beginning with version 1706, this feature is no longer a pre-release feature .

Data warehouse service point used to store and report on long-term historical data for your SCCM Configmgr deployment.

Data warehouse service point is not enabled by default when you upgrade your configmgr build to 1706 or  later versions and must be manually configured.

Data warehouse dataflow (captured from Technet article)

For more information about Data warehouse service point ,please read TechNet document https://docs.microsoft.com/en-us/sccm/core/servers/manage/data-warehouse 

In this blog post, we will see how to install Data warehouse service point and query the data that get deleted in the CAS/primary site database but exist in data warehouse for reporting and tracking purpose.

So in this post, I will show you ,how to use data warehouse to pull the information that get deleted from the primary database .

Prerequisites for Data warehouse server (CM01-DW):

1.create windows server 2012 R2 or higher with fully patched (CM01-DW)
2.Join (CM01-DW) to domain.
3.Add the primary site server (CM01) or CAS (if you have) that you are trying to install the data warehouse role computer account as local admin on the server (CM01-DW).

Note: The data warehouse site system role is supported only at the top-tier site of your hierarchy. (A central administration site or stand-alone primary site)

4. The computer where you install the site system (data warehouse) role requires .NET Framework 4.5.2 or later. Since am running server 2012 R2 OS ,I don’t need to install this role and is built-in enabled.

5.The data warehouse database requires the use of SQL Server 2012 or later. The edition can be Standard, Enterprise, or Datacenter. .I installed SQL server 2014 SP1 on CM01-DW server with default options and SQL collation must be: SQL_Latin1_General_CP1_CI_AS (is default as part of the installation)

While installing SQL Server ,choose database engine ,reporting services and management tools (for SQL Studio) in features. Choose default instance .

As part of SQL components installation ,you might hit error with .net framework 3.5 features which you can enable from server manager ,add roles and features .This requires to map server OS sxs files.

SQL server installation summary:

The following SQL Server configurations are supported to host the warehouse database:

• A default instance
• Named instance
• SQL Server Always On availability group
• SQL Server failover cluster

6. The computer account of the computer where you install the site system role (CM01-DW) is used to synchronize data with the data warehouse database. This account requires the following permissions:

• Administrator on the computer that hosts the data warehouse database. 
• DB_Creator permission on the data warehouse database. 
• Either DB_owner or DB_reader with execute permissions to the top-tier site's site database.

As part of this pre-req ,I pre-created DW Database on my remote SQL :CM_PS1_DW and provided the permissions as mentioned in point 6.

7.SQL server port 1433 used by the data warehouse synchronization service to connect to the data warehouse database. By default 1433 SQL Server port is used for communication.

How to Install the data warehouse service point from CAS or Primary Site:

Each hierarchy supports a single instance of this role, on any site system of the top-tier site. The SQL Server that hosts the database for the warehouse (CM01-DW) can be local to the site system role, or remote.

The data warehouse works with the reporting services point installed at the same site (CM01-DW). You do not need to install the two site system roles on the same server.

From CAS server or Primary site ( In my case ,I don’t have CAS) ,click on servers and site system roles to install new Role . Choose create site system server

Enter the remote server name to host the data warehouse database.

also make sure the primary site server computer account (CM01) is added to local admin group on remote server (CM01-DW) as we use site server’s computer account to install the site system.

Click next with default options since this role doesn’t require to connect to internet for syncing

Choose Data warehouse service point ,click Next

Key in the fields as shows below.

SQL Server : Remote server that we installed SQL server 2014: CM01-DW.apac.eskonr.com

SQL server instance : I installed SQL server on CM01-DW with default instance hence I leave it blank

Database Name:Leave with default name: CM_PS1_DW

Data warehouse service point account :This is used to connect to data warehouse database and must have read access to the database CM_PS1_DW.

This account is used to run the reports against data warehouse database and is configured in the data source properties which you can verify later.

Accept the default sync schedule,you can customize it as per the schedule . This schedule will help to sync the data from primary site to data warehouse database.

Review the summary page:

Login to remote server (CM01-DW) to check the logs

• DWSSMSI.log and DWSSSetup.log - Use these logs to investigate errors when installing the data warehouse service point.
• Microsoft.ConfigMgrDataWarehouse.log – Use this log to investigate data synchronization between the site database to the data warehouse database.

With this ,we completed the installation of data warehouse service point on remote computer.

If you hit any issues with database connectivity ,make sure the computer accounts are added with right permissions on the CM_PS1_DW database.

Now we will check if the data from primary site (CM01) is synced to data warehouse (CM01-DW) database or not .

Open SQL server management studio , run select * From system_disc (if you are doing it in prod, then try select top 10 * from system_disc)

As you can see above, in system_disc table ,there is one attribute value (Operationtype_DW)that refers the system deleted or not from Primary site .

Operationtype_DW basically contains information as listed below:

I: New Record

U:Updated record

D: Deleted Record

So once you know the information ,you can easily create SQL reports with above attribute type with operationType_DW=D and let customer device what they want to do.

With the data warehouse ,we also get some default reports and they are available in Primary site .But these reports will run against data warehouse database .These data warehouse reports datasource is pointed to CM01-DW SQL.

Data warehouse reports can be found in the SCCM console-reporting node or using SSRS webURL using primary site SSRS URL.

There are about 7 main reports and 7 linked reports (_) .

The data warehouse site system role includes the following reports, which have a Category of Data Warehouse:

• Application Deployment - Historical: View details for application deployment for a specific application and machine.
• Endpoint Protection and Software Update Compliance - Historical: View computers that are missing software updates.
• General Hardware Inventory - Historical: View all hardware inventory for a specific machine.
• General Software Inventory - Historical: View all software inventory for a specific machine.
• Infrastructure Health Overview - Historical: Displays an overview of the health of your Configuration Manager infrastructure
• List of Malware Detected - Historical: View malware that has been detected in the organization.
• Software Distribution Summary - Historical: A summary of software distribution for a specific advertisement and machine.

using SSRS report URL using primary site :

There will be a default data source created with name: {39B693BB-524B-47DF-9FDB-9000C3118E82} with connecting string and is configured with an account CM_SR to run the reports against with.

this CM_SR is used while installing the role.

Connection string: Persist Security Info=False;Initial Catalog=CM_PS1_DW;Data Source=CM01-DW.apac.eskonr.com;Encrypt=true;TrustServerCertificate=false

I tried to run one of the data warehouse report but I get the following error which is known issue:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

To fix this error ,please follow the guide https://www.ronnipedersen.com/2018/01/15/sccm-unable-to-run-data-warehouse-reports-from-remote-sql/ and https://docs.microsoft.com/en-us/sccm/core/servers/manage/data-warehouse

Hope this guide help you to install and create custom reports .

In the next blog post, we will see what are the objects/information that get stored in data warehouse.