SCCM (System Center Configuration Manager) Data Collection Configuration

Open iT supports collecting data from Microsoft System Center Configuration Manager for reporting and analysis. It integrates with the SCCM SQL Server database to extract data and push it to the Open iT SQL Server database using SQL Server Integration Services (SSIS).

This document provides information on how to set up the integration with SCCM.

Requirements

• An installed Open iT Analysis Server
• Administrator account for SSIS connection
• Analysis Server Service Account registered in the SQL Server where the SSCM database resides with connect and read access

NOTE

This assumes that you have Analysis Server installed and configured properly.

Integration

To integrate, follow these instructions sequentially:

1. Uploading SSIS Packages. Use these instructions to upload the SSIS packages using SQL Server Management Studio.
2. Configuring SSIS Packages. Use these instructions to configure each individual SSIS package with the correct connection string using SQL Server Management Studio.
3. Testing the configuration. Use these instructions to test if the configuration is working properly.
4. Creating a scheduled job. Use these instructions to create a scheduled job using the SQL Server Agent.
5. Testing the scheduled job. Use these instructions to test if the created scheduled job is working fine.

Uploading SSIS Packages through SSMS

The integration is orchestrated by the SQL Server Integration Services (SSIS). It includes the extraction of data from the SQL Server database of SCCM and pushing it to the Analysis Server SQL Server database to be included in its daily processing.

To upload the projects, follow these instructions:

1. Open SSMS as Administrator, and connect to the server where the Open iT database is hosted.

   NOTE

   It is required to run as an Administrator since SQL Server Integration Services only allows an Administrator account to access its service.

2. Once connected, right-click Integration Services Catalogs folder. Choose Create Catalog.

   

   Creating Catalog in SSIS

   
   

3. The Catalog Creation Wizard will appear. Click Enable CLR Integration.

4. Provide the Password and leave the other values as default. Click OK.

   NOTE

   Make sure to secure a copy of the Password.

   

   Catalog Creation Wizard

   
   

5. Wait until the process is finished. The SSISDB will be created. Right-click the SSISDB and choose Create Folder.

   

   Creating Catalog Folder in SSIS

   
   

6. Name the folder as SCCM. Click OK.

   

   Catalog Name in SSIS

   
   

7. Expand the SCCM folder, and it should contain the Projects folder. Right-click the Projects folder and choose Import Packages.

   

   Import Packages in SSIS

   
   

8. The Integration Services Project Conversion dialog will appear. Click Next.

   

   Integration Services Project Conversion

   
   

9. In the Folder option, browse in the Analysis Server SSIS packages directory. This is commonly located at C:\Program Files\OpeniT\Analysis Server\SSIS. Then, click OK and Next to proceed.

   

   Analysis Server SSIS Packages Directory

   
   

10. Select all available packages.

    • MergeFileUsageSummaryData_v_1_1.dtsx
    • MergeMeteredData_v1_1.dtsx
    
    

    

    Analysis Server SSIS Packages

    Click Next.

11. Name the project as SCCMtoOpeniT, and set the Protection Level to DontSaveSensitive. Click Next and skip the Update Execute Package Task.

    

    ASCCM Data Collection Project Name

    
    

12. Do not select anything in the Select Configurations option, and skip Create Parameters and Configure Parameters dialogs. Click Next.

13. Review the selections and click Convert.

    

    Review Selections

    
    

14. Wait until all the processes are successful. Click Close.

    

    Project Creation Results

    
    

15. The Integration Services Deployment Wizard will appear. Click Next.

    

    Integration Services Deployment Wizard

    
    

16. In the Select Deployment Target, choose SSIS in SQL Server. Click Next.

    NOTE

    If this dialog does not appear, please proceed to the next step.

17. In the Select Destination dialog, provide the Server Name where to deploy the project.

    NOTE

    In some instances, you must click a Connect button before you can proceed. If this is not applicable, please proceed to the next step.

    

    Integration Services Deployment Destination

    Click Next.

18. Review the selections and click Deploy.

    

    Review Integration Services Deployment

    
    

19. Wait until all the processes are successful. Once done, click Close.

    

    Integration Services Deployment Results

    
    

20. Refresh the Projects folder, and the SCCMtoOpeniT project should appear. Expand the SCCMtoOpeniT > Packages, and it should contain the two packages.

    

    Integration Services Project

    
    

Configuring SSIS Packages through SSMS

A manual configuration is necessary to successfully set up the integration. This includes configuring of the connection strings and user accounts for database connection.

To configure the packages, follow these instructions:

1. Open SSMS running as Administrator, and connect to the server where the Open iT database is hosted.

   NOTE

   It is required to run as an Administrator since SQL Server Integration Services only allows an Administrator account to access its service.

2. Once connected, expand Integrated Services Catalogs > SSISDB > Projects > SCCMtoOpeniT > Packages.

3. Right-click the package MergeFileUsageSummaryData_v_1_1.dtsx and choose Configure.

   

   Integrated Services Catalog Project

   
   

4. Go to the Connection Managers tab. Click the first container and configure the ConnectionString by clicking the ellipsis (...) button.

   

   Configuring SSIS Package - ConnectionString

   
   

5. The Set Parameter Value dialog will appear. Click the Edit value option. Copy the current connection string and paste it to the enabled text area.

6. Modify the following:

   • Data Source - server name where Open iT database resides (e.g., Data Source=DESKTOP789WIN)
   • Initial Catalog - database name used for Open iT (e.g., Initial Catalog=OpeniT)
   
   

   

   Configuring SSIS Package - Edit ConnectionString

   Click OK.

7. Click the ellipsis (...) button beside InitialCatalog.

   

   Configuring SSIS Package - Initial Catalog

   
   

8. The Set Parameter Value dialog will appear. Click Edit Value and provide the database name of Open iT. Click OK.

   

   Configuring SSIS Package - Edit Initial Catalog

   
   

9. Click the ellipsis (...) button beside ServerName.

   

   Configuring SSIS Package - Server Name

   
   

10. The Set Parameter Value dialog will appear. Click Edit Value and provide the server name where the Open iT database resides. Click OK.

    

    Configuring SSIS Package - Edit Server Name

    
    

11. Click the ellipsis (...) button beside UserName.

    

    Configuring SSIS Package - User Name

    
    

12. The Set Parameter Value dialog will appear. Click Edit Value and provide the Analysis Server Service Account username. Click OK.

    

    Configuring SSIS Package - Edit User Name

    
    

13. Click the ellipsis (...) button beside Password.

    

    Configuring SSIS Package - Password

    
    

14. The Set Parameter Value dialog will appear. Click Edit Value and provide the Analysis Server Service Account password. Click OK.

    

    Configuring SSIS Package - Edit Password

    
    

15. Click the second container and configure the ConnectionString. Click the ellipsis (...) button.

    

    Configuring SSIS Package - SCCM ConnectionString

    
    

16. The Set Parameter Value dialog will appear. Click the Edit value option. Copy the current connection string and paste it to the enabled text area.

17. Modify the following:

    • Data Source - server name where SCCM database resides (e.g., Data Source=DESKTOP789WIN)
    • Initial Catalog - database name of SCCM (e.g., Initial Catalog=SCCM)
    
    

    

    Configuring SSIS Package - Edit SCCM ConnectionString

    Click OK.

18. Click the ellipsis (...) button beside Initial Catalog.

    

    Configuring SSIS Package - SCCM Initial Catalog

    
    

19. The Set Parameter Value dialog will appear. Click Edit Value and provide the database name of SCCM. Click OK.

    

    Configuring SSIS Package - Edit SCCM Initial Catalog

    
    

20. Click the ellipsis (...) button beside ServerName.

    

    Configuring SSIS Package - Server Name

    
    

21. The Set Parameter Value dialog will appear. Click Edit Value and provide the server name where the SCCM database resides. Click OK.

    

    Configuring SSIS Package - Edit Server Name

    
    

22. Click the ellipsis (...) button beside UserName.

    

    Configuring SSIS Package - SCCM User Name

    
    

23. The Set Parameter Value dialog will appear. Click Edit Value and provide the Analysis Server Service Account username. Click OK.

    

    Configuring SSIS Package - Edit SCCM User Name

    
    

24. Click the ellipsis (...) button beside Password.

    

    Configuring SSIS Package - SCCM Password

    
    

25. The Set Parameter Value dialog will appear. Click Edit Value and provide the Analysis Server Service Account password. Click OK.

    

    Configuring SSIS Package - Edit SCCM Password

    
    

26. Click OK to save the configurations in the package.

    

    Configuring SSIS Package - Save Changes

    
    
    NOTE

    Do these instructions on the other package MergeMeteredData_v1_1.dtsx.

    

    Configuring SSIS Package

Testing the Configurations

To test if the configurations are working fine, follow these instructions:

1. Open SSMS running as Administrator, and connect to the server where the Open iT database is hosted.

   NOTE

   It is required to run SSMS as an Administrator since SQL Server Integration Services only allows an Administrator account to access its service.

2. Once connected, expand Integration Services Catalogs > SSISDB > Projects > SCCMtoOpeniT > Packages.

3. Right-click the package MergeFileUsageSummaryData_v_1_1.dtsx. Choose Execute.

   

   Testing Package Configurations

   
   

4. The Execute Package dialog will appear. Click the Connection Managers tab, and review the connection string. Modify if necessary. Click OK.

   

   Execute Package

   
   

5. Wait until the process is finished. A message prompt will appear, click Yes to open the Overview Report.

   

   Show Execution Overview Report

   
   

6. The Overview Report will appear. Review the Execution Information and see the Status, which can be Failed or Success. More detailed information about the package execution is in the Execution Overview.

   

   Execution Overview Report

   
   
   warning

   Do these instructions on the other package MergeMeteredData_v1_1.dtsx.

   

   Testing Package Configurations

Creating Scheduled Jobs through SSMS

It is essential to create a scheduled jobs using the SQL Server Agent to automate data extraction daily, ensuring that the data processed by the Analysis Server is up-to-date.

To create a scheduled job, follow these instructions:

1. Open SSMS running as Administrator, and connect to the server where the Open iT database is hosted.

2. Right-click the SQL Server Agent. Click Start. Wait until the status of the service turns active.

3. Expand the SQL Server Agent and right-click the Jobs folder. Choose New Job.

   

   Creating Scheduled Jobs

   
   

4. Name the job as SCCMtoOpeniT. Choose Data Collector as Category.

   

   Scheduled Job Details

   
   

5. In the left pane, click Steps, then click New.

   

   Scheduled Job New Step

   
   

6. The New Job Step dialog will appear. Provide the following details:

   • Step Name - MergeFileUsageSummaryData
   • Type - SQL Server Integration Services Package
   • Package source - SSIS Catalogs
   • Server - the server name where the SSIS package is uploaded
   • Package - browse the package MergeFileUsageSummaryData_v_1_1.dtsx
   
   

   

   Scheduled Job Steps

   Click OK.

7. Click New. The New Job Steps dialog will appear. Provide the following details:

   • Step Name - MergeMeteredData
   • Type - SQL Server Integration Services Package
   • Run as - SQL Server Agent Service Account
   • Package source - SSIS Catalogs
   • Server - the server name where the SSIS package is uploaded
   • Package - browse the package MergeMeteredData_v1_1.dtsx
   
   

   

   Scheduled Job Steps

   Click OK.

8. In the left pane, click Schedules, then click New.

   

   Scheduled Job Schedule

   
   

9. The New Schedule dialog will appear. Provide the following details:

   • Name - SCCMCollection
   • Schedule Type - Recurring
   • Occurs - Daily
   • Recurs every - 1 day
   • Occurs once at - 4:00 AM
   • Start Date - today
   • End Date - No end date
   
   
   warning

   The job schedule should be before the daily execution of the Analysis Server ETL process. Please adjust accordingly.

   Make sure that the SQL Server Agent service account has access to the SCCM server and database.

10. Review the Summary at the bottom of the dialog. Click OK.

    

    Scheduled Job Schedule Frequency

    
    

11. Click OK to save the configurations.

    

    Save Scheduled Job Schedule

    
    

Testing the Scheduled Jobs

To make sure that the configured scheduled jobs are working fine, follow these instructions to test:

1. Open SSMS running as Administrator, and connect to the server where the Open iT database is hosted.

2. Expand the SQL Server Agent > Jobs and right-click the job SCCMtoOpeniT. Choose Start Job at Step.

   

   Testing the Scheduled Job

   
   

3. The Start Job dialog will appear. Click Start.

   

   Start the Scheduled Job

   
   

4. Wait until the process succeeds. If the process fail, follow the instructions in the section Viewing the History Logs.

   

   Scheduled Job Results

   
   

Viewing the History Logs

There will be times the job will fail. To see the history logs, follow these instructions:

1. Open SSMS running as Administrator, and connect to the server where the Open iT database is hosted.

2. Expand the SQL Server Agent > Jobs and right-click the job SCCMtoOpeniT. Choose View history.

   

   Viewing the History Logs

   
   

3. The Log File Viewer will appear. Under the list of Log File Summary, click an item to review the details.

4. Review details at the bottom of the dialog. Use the details to learn more about the execution.

   

   History Logs Details

   
   

Reporting SCCM Data in Analysis Server

Use these instructions to report the collected SCCM data in Analysis Server for analysis and inventory.

Application Level Report

To report, follow these instructions:

1. Open the Analysis Server web interface and navigate to the Analysis Page.

2. In the Pivot Field List, expand Measures.

   

   Report Table View

   
   

3. In the Pivot Field List, drag and drop Elapsed Time, SMS Distinct User, and SMS Max Concurrent User (one-by-one) into the Pivot Table (Drop Data Items Here).

   

   Report Measures

   
   

4. In the Pivot Field List, expand the Application dimension. Drag and drop the Application Hierarchy in the Pivot Table (Drop Rows Fields Here).

   

   Report Applications

   
   

5. In the Pivot Field List, drag and drop the Application Type in the Pivot Table (Drop Filter Fields Here).

6. Filter the Application Type, choose only SMS, then click OK. This will filter the list of rows in the Pivot Table.

   

   Report Filters

   
   

7. Expand the list of data in the Pivot Table. Application Type > Vendor License > Name > Version.

   

   Application Level Report

   
   

The report shows how long the application was used and how many distinct users are using a certain application. It also gives details on how the maximum number of concurrent users are using a certain application.

User Level Report

To report, follow these instructions:

1. Open the Analysis Server web interface and navigate to the Analysis Page.

2. In the Pivot Field List, expand Measures.

   

   Report Table View

   
   

3. In the Pivot Field List, drag and drop Elapsed Time into the Pivot Table (Drop Data Items Here).

   

   Report Measures

   
   

4. In the Pivot Field List, expand the Application dimension. Drag and drop the Application Hierarchy in the Pivot Table (Drop Rows Fields Here).

   

   Report Applications

   
   

5. Drag the Application Type in the Pivot Table (Drop Filter Fields Here).

6. Filter the Application Type, choose only SMS, then click OK. This will filter the list of rows in the Pivot Table.

   

   Report Filters

   
   

7. Expand the list of data in the Pivot Table. Application Type > Vendor License > Name > Version.

   

   Application Level Report

   
   

8. In the Pivot Field List, expand the User dimension. Drag and drop the User Id classification beside the Version.

9. Expand a specific Version to see the list of users.

   

   User Level Report

   
   

This report shows the list of users using a certain application and how long the user used the application.