# Collecting Data from SCCM (System Center Configuration Manager)

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

# 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 running as Administrator, and connect to the server where the Open iT database is hosted.

   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.

   

   SCCM Integration: 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.

   Make sure to secure a copy of the Password.

   

   SCCM Integration: Catalog Creation Wizard
   
   

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

   

   SCCM Integration: Creating Catalog Folder in SSIS
   
   

6. Name the folder as SCCM. Click OK.

   

   SCCM Integration: 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.

   

   SCCM Integration: Import Packages in SSIS
   
   

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

   

   SCCM Integration: 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. Click OK and Next.

   

   SCCM Integration: Analysis Server SSIS Packages Directory
   
   

10. Select all available packages.

    • MergeFileUsageSummaryData_v_1_1.dtsx
    • MergeMeteredData_v1_1.dtsx
      
      
    SCCM Integration: 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.

    

    SCCM Integration: SCCM 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.

    

    SCCM Integration: Review Selections
    
    

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

    

    SCCM Integration: Project Creation Results
    
    

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

    

    SCCM Integration: Integration Services Deployment Wizard
    
    

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

    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.

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

    

    SCCM Integration: Integration Services Deployment Destination
    
    

    Click Next.

18. Review the selections and click Deploy.

    

    SCCM Integration: Review Integration Services Deployment
    
    

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

    

    SCCM Integration: 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.

    

    SCCM Integration: 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.

   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.

   

   SCCM Integration: Integration Services Catalog Project
   
   

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

   

   SCCM Integration: 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=MNL1581WIN)
   • Initial Catalog - database name used for Open iT (e.g., Initial Catalog=OpeniT)
     
     
   SCCM Integration: Configuring SSIS Package - Edit ConnectionString
   
   

   Click OK.

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

   

   SCCM Integration: 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.

   

   SCCM Integration: Configuring SSIS Package - Edit Initial Catalog
   
   

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

   

   SCCM Integration: 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.

    

    SCCM Integration: Configuring SSIS Package - Edit Server Name
    
    

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

    

    SCCM Integration: 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.

    

    SCCM Integration: Configuring SSIS Package - Edit User Name
    
    

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

    

    SCCM Integration: 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.

    

    SCCM Integration: Configuring SSIS Package - Edit Password
    
    

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

    

    SCCM Integration: 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=MNL1580WIN)
    • Initial Catalog - database name of SCCM (e.g., Initial Catalog=SCCM)
      
      
    SCCM Integration: Configuring SSIS Package - Edit SCCM ConnectionString
    
    

    Click OK.

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

    

    SCCM Integration: 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.

    

    SCCM Integration: Configuring SSIS Package - Edit SCCM Initial Catalog
    
    

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

    

    SCCM Integration: Configuring SSIS Package - Edit 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.

    

    SCCM Integration: Configuring SSIS Package - Edit Server Name
    
    

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

    

    SCCM Integration: Configuring SSIS Package - SCCM UserName
    
    

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

    

    SCCM Integration: Configuring SSIS Package - Edit SCCM User Name
    
    

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

    

    SCCM Integration: 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.

    

    SCCM Integration: Configuring SSIS Package - Edit SCCM Password
    
    

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

    

    SCCM Integration: Configuring SSIS Package - Save Changes
    
    

# 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.

   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.

   

   SCCM Integration: 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.

   

   SCCM Integration: Execute Package
   
   

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

   

   SCCM Integration: 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.

   

   SCCM Integration: Execution Overview Report
   
   

# 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.

   

   SCCM Integration: Creating Scheduled Jobs
   
   

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

   

   SCCM Integration: Scheduled Job Details
   
   

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

   

   SCCM Integration: 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
     
     
   SCCM Integration: 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
     
     
   SCCM Integration: Scheduled Job Steps
   
   

   Click OK.

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

   

   SCCM Integration: 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
     
     
   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.

    

    SCCM Integration: Scheduled Job Schedule Frequency
    
    

11. Click OK to save the configurations.

    

    SCCM Integration: 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.

   

   SCCM Integration: Testing the Scheduled Job
   
   

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

   

   SCCM Integration: Start the Scheduled Job
   
   

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

   

   SCCM Integration: 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.

   

   SCCM Integration: 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.

   

   SCCM Integration: 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.

   

   SCCM Integration: 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).

   

   SCCM Integration: 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).

   

   SCCM Integration: 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.

   

   SCCM Integration: Report Filters
   
   

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

   

   SCCM Integration: 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.

   

   SCCM Integration: Report Table View
   
   

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

   

   SCCM Integration: 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).

   

   SCCM Integration: 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.

   

   SCCM Integration: Report Filters
   
   

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

   

   SCCM Integration: 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.

   

   SCCM Integration: User Level Report
   
   

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