Installing the Microsoft SCCM App

Guide Overview

The following guide is designed to give district administrators an overview of the following topics:

  • Installing the Microsoft SCCM app integration

Not what you were looking for? Perhaps one of these other guides will help:

  • Managing Incident IQ Apps - A guide designed to provide a brief overview of Incident IQ apps and how to access app management.

 

Guide Index

You can use the following links below to quickly navigate to a specific section in this document. To quickly return to this index, use the "Return to Index" link located at the end of any section.

  1. App SQL Setup
  2. Configuring and Downloading the SCCM Executable
  3. Setting up a Schedule Task

 

App SQL Setup

In SQL Management Studio, connect to the SCCM database. Please take note of the version number next to the server, as this will be needed later during the setup process.

SCCM_Setup_001.png

Right-click the SQL server in the Object Explorer tree and click Properties.

SCCM_Setup_002.png

From this page, you will want to click the Security page in the Select a page menu.

SCCM_Setup_003.png

Select SQL Server and Windows Authentication mode under the Server authentication section, then click OK.

SCCM_Setup_004.png

The SQL service will need to restart if any changes were made. This is done by right-clicking the SQL server node, clicking Restart, and clicking OK. If the SQL Server Agent service is running, you will need to click OK to confirm that it should also be restarted.

SCCM_Setup_005.png

Once the server has restarted, you will want to expand the Security folder in the Object Explorer tree. Next, right-click on the Logins folder and select New Login.

SCCM_Setup_006.png

You will now need to select SQL Server authentication, provide a Login name, a Password, and uncheck the Enforce password policy option.

SCCM_Setup_007.png

Click User Mapping in the Select a page menu. Locate the SCCM database in the primary list, and check the checkbox in the Map column next to it. In the Database role membership-list, select db_datareader and click OK.

SCCM_Setup_008.png

warning.pngImportant Note: If the version of your SQL server is less than 13, you will need to complete the following additional steps below. If your version of SQL is 13 or higher, you can continue your setup here.

If your SQL server setup is less than version 13, you will need to right-click the Microsoft SCCM database in the Object Explorer tree and click New Query.

SCCM_Setup_009.png

Next you will need to run the following SQL script to patch the missing JSON support:

CREATE FUNCTION [dbo].ToJSON (@XML xml)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @HEAD VARCHAR(MAX) = '',@JSON VARCHAR(MAX) = ''
; WITH CTEEAV AS (SELECT RowNr=ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
,Entity = xRow.value('@*[1]','VARCHAR(100)')
,Attribute = xAtt.value('local-name(.)','VARCHAR(100)')
,Value = REPLACE(REPLACE(xAtt.value('.','VARCHAR(MAX)'),'\','\\'),'"','\"')
FROM @XML.nodes('/row') As R(xRow)
Cross Apply R.xRow.nodes('./@*') As A(xAtt) )
,CTESUM AS (SELECT Records=COUNT(DISTINCT Entity)
,Head = '[[getResults]]'

FROM CTEEAV)
,CTEBLD AS (SELECT *
,NewRow=IIF(LAG(Entity,1) OVER (PARTITION BY Entity ORDER BY (SELECT NULL))=Entity,'',',{')
,EndRow=IIF(LEAD(Entity,1) OVER (PARTITION BY Entity ORDER BY (SELECT NULL))=Entity,',','}')
,JSON=Concat('"',Attribute,'":','"',Value,'"')
FROM CTEEAV )
SELECT @JSON = @JSON+NewRow+JSON+EndRow,@HEAD = Head FROM CTEBLD, CTESUM
RETURN REPLACE(@HEAD,'[getResults]',STUFF(@JSON,1,1,''))
END

GO

CREATE FUNCTION [dbo].RemoveInvalidXMLCharacters (@InputString VARCHAR(MAX))

RETURNS VARCHAR(MAX)
AS
BEGIN
IF @InputString IS NOT NULL
BEGIN
DECLARE @Counter INT, @TestString NVARCHAR(40)

SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) + ']%'

SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)

WHILE @Counter <> 0
BEGIN
SELECT @InputString = STUFF(@InputString, @Counter, 1, ' ')
SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
END
END
RETURN(@InputString)
END


Then, allow access to the newly created functions to the iiQ user by replacing SparkSccm with the database name and abc with the username we connect to the SCCM database with.

GRANT EXECUTE ON OBJECT::SparkSccm.dbo.ToJSON TO abc;
GRANT EXECUTE ON OBJECT::SparkSccm.dbo.RemoveInvalidXMLCharacters TO abc;

[Return to Index]

 

Configuring and Downloading the SCCM Executable

You will now need to configure and download the executable file for the Microsoft SCCM app to a local machine with access to your SQL server that runs overnight. To do so, begin by selecting Manage on the left navigation bar.

warning.pngImportant Note: If the Microsoft SCCM App has not been installed yet, you should select Browse instead and install the app. After app installation is completed, you may access this app from the Manage menu. 

SCCM_Setup_010.png

This will take you to your Installed Apps page where you can select the Options button to access the Microsoft SCCM app management.

Manage_SCCM_App.png

From here, click on the Sync Executable tab in the SCCM app.

SCCM_Setup_014.png

In the Sync Executable tab, begin by selecting the Predefined SQL Command Template to load based on the current version of your SQL server.

SCCM_Setup_012.png

Once you have loaded your predefined template, you will need to enter the following information in the Database Connection Settings:

  • Server Name/IP
  • Database Name
  • Username
  • Password
  • Page Size

After these fields have been filled out, click on Download Executable. You will then need to run a manual sync of the app to ensure that all settings have been configured properly.

SCCM_Setup_013.png

[Return to Index]

 

Creating a Scheduled Sync Task

To schedule the sync to occur automatically, you'll need to create a task in Windows Task Manager. You can do so by searching for Administrative Tools and selecting Task Scheduler. This will open the Task Scheduler window.

Task_Scheduler_1.png

In the Task Scheduler window, start by clicking on Action > Create Basic Task...

Task_Scheduler_2.png

This will open the Create Basic Task Wizard. At the very least, you will need to provide a name for the new task. You can also add a task if desired. Once this is completed, click Next.

Task_Scheduler_3.png

In the next step, you will be asked to select when this task should Trigger. We recommend running it daily (overnight) for the most accurate user data. Please ensure that you set the task to run regardless of whether a user is logged in or not on the server. Once complete, click Next.

Task_Scheduler_4.png

For the next step, you will need to specify what action the task will take when running. Select Start a Program and then click Next.

Task_Scheduler_5.png

When specifying the action to perform, locate the file IncidentIQ.Connectors.MicrosoftAd.exe in the Program/script file browser. Supply the argument -service in the Add arguments field. And finally, you will need to indicate the path you unzipped the files to in the Start in field. Once complete, click Next.

Task_Scheduler_6.png

In the final step, you may review all of the settings of your task. Once you have completed your review, check Open the Properties dialog for this task when I click Finish option and then click the Finish.

SCCM_Setup_010.png

In the sync properties window, you will need to check Run whether user is logged on or not as well as Run with highest privileges. Click OK to complete the sync setup.

SCCM_Setup_011.png

[Return to Index]

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.