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.
Right-click the SQL server in the Object Explorer tree and click Properties.
From this page, you will want to click the Security page in the Select a page menu.
Select SQL Server and Windows Authentication mode under the Server authentication section, then click OK.
The SQL service will need to restart if any changes are 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.
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.
You will now need to select SQL Server authentication, provide a Login name and a Password, and uncheck the Enforce password policy option. This will be used as a back-end service account in the next step of this document to set up the integration executable.
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.
Important Note: If the version of your SQL server is less than 13, you will need to complete the following additional steps below in the Configuring and Downloading the SCCM Executable section.
If your SQL server setup is less than version 13, you must right-click the Microsoft SCCM database in the Object Explorer tree and click New Query.
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
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.
Important Note: If the Microsoft SCCM App has not been installed yet, you should select Browse instead and install the app. After the app installation is completed, you may access this app from the Manage menu.
This will take you to your Installed Apps page, where you can select the Options button to access the Microsoft SCCM app management.
From here, click on the Sync Executable tab in the SCCM app. In the Sync Executable tab, begin by selecting the Predefined SQL Command Template to load based on the current version of your SQL server. Once you have chosen the template, you need to click on Load. This will copy the SQL command into the box above the drop-down menu.
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. As mentioned earlier, this executable will need to be downloaded to a machine with access to your SQL server that is left on overnight. Once downloaded, you will need to manually sync the app to ensure that all settings have been configured properly.
Setting up 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.
In the Task Scheduler window, start by clicking on Action > Create Basic Task...
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.
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.
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.
When specifying the action to perform, locate the file IncidentIQ.Connectors.LocalSQL.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.
In the final step, you may review your task’s settings. Once you have completed your review, check Open the Properties dialog for this task when I click Finish option and then click the Finish.
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.
Configuring the Integration for Automatic Asset Importing
If you want to automatically import assets from SCCM into Incident IQ during syncs between the system, you will need to configure the app. Begin by navigating back to the SCCM management page.
From here, the first setting you will need to enable is located in the General Settings tab. You will need to enable the Create Asset option in the mapping section. Please note that you will more than likely also want to allow the Update Asset field; however, this field is not necessary for the initial asset creation.
Next, you will need to navigate to the Model Mappings tab. From here, you will need to indicate what model in Incident IQ matches the models pulling from SCCM. The Asset Model column populates with model information pulled from SCCM, while the drop-down menu will specify the model used for the asset in Incident IQ.
Important: If you have assets in SCCM that do not have an assigned model, then you will not be able to import them through the automatic asset creation feature in the SCCM integration. A model is always required to create an asset in Incident IQ.
If a model exists in SCCM but not Incident IQ, you can use the Create New Model button on the right of the drop-down to enter a new asset model into Incident IQ.
Once you have completed your model mappings, you will need to navigate to the Model Mappings tab. You will, at the very least, need to set a default location from here. Whenever an asset does not match any of your custom location mappings, or you have no custom mapping locations setup, the asset will be placed into the default location during system syncs.
Suppose you want the system to automatically assign assets to locations in Incident IQ based on location information in SCCM. In that case, you can also set up custom mappings on this page. You can match locations between the two systems using either the location name or the OUs/OU fragments.
Once you have Create Asset turned on, the models mapped, and your locations selected, the integration can now import assets during system syncs. Failure to complete configuring any of the above settings can prevent some or all of your assets from importing into the system.
Setting up Automated Asset Creation
First, you must ensure that the Create Asset option is turned on. This is located in the General Settings tab of the app.
Next, you must ensure that locations have been set up in the Locations Tab. Please remember that assets need to have a location when brought into the system. The way this works during a sync is as follows:
If an asset matches a custom mapping, then the asset will get created and assigned to the corresponding location the mapping is set to.
If an asset does not match a custom mapping, or no custom mappings are present, and a default location is selected, then the asset will get created and assigned to the default location.
If an asset does not match a custom mapping, or no custom mappings are present, and no default location set, then the asset will not be created during a sync.
For additional details, please refer to the Locations Tab section of this document.
And finally, you will need to ensure that the particular asset model is mapped in the Model Mapping Tab.
Please note, when you first install your app, this field will be blank as Incident IQ has not yet run a sync to scan which models are stored in Microsoft SCCM. To pull model information in, click Run Sync in the General Settings Tab. Once completed, you should see a full list of all available models in your Microsoft SCCM instance.
---
Hannah Bailey - Director, Customer Engagement