<style>p { margin: 0; }span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left:0; padding-left:5px;}blockquote blockquote{ border-color: #00bcd4; color: #00bcd4;}blockquote blockquote blockquote{ border-color: #43a047; color: #43a047;} table.grid{ border-collapse: collapse;} table.grid td, table.grid th { border: 1px solid #ddd;} .fr-fic.fr-dib{ display: block; margin: 5px auto;}.fr-fic.fr-dib.fr-fir{ text-align: right; margin: 5px 0 5px auto;}.fr-fic.fr-dib.fr-fil{ text-align: left; margin: 5px auto 5px 0;}.fr-fic.fr-dii{ float: none; margin: 5px auto;}.fr-fic.fr-dii.fr-fil{ float: left; margin: 5px auto;}.fr-fic.fr-dii.fr-fir{ float: right; margin: 5px auto;}img.fr-dib.fr-fir { margin-right: 0; text-align: right;}img.fr-dib.fr-fil { margin-left: 0; text-align: left;}img.fr-dib { margin: 5px auto; display: block; float: none;}img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC;}img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc;}img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;}</style><style>
p {
margin: 0;
}
span.fr-emoticon.fr-emoticon-img {
background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle;
}
span.fr-emoticon {
font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0;
}
blockquote {
border-left: solid 2px #5e35b1; color: #5e35b1; margin-left: 0; padding-left: 5px;
}
blockquote blockquote {
border-color: #00bcd4; color: #00bcd4;
}
blockquote blockquote blockquote {
border-color: #43a047; color: #43a047;
}
table.grid {
border-collapse: collapse;
}
table.grid td,
table.grid th {
border: 1px solid #ddd;
}
.fr-fic.fr-dib {
display: block; margin: 5px auto;
}
.fr-fic.fr-dib.fr-fir {
text-align: right; margin: 5px 0 5px auto;
}
.fr-fic.fr-dib.fr-fil {
text-align: left; margin: 5px auto 5px 0;
}
.fr-fic.fr-dii {
float: none; margin: 5px auto;
}
.fr-fic.fr-dii.fr-fil {
float: left; margin: 5px auto;
}
.fr-fic.fr-dii.fr-fir {
float: right; margin: 5px auto;
}
img.fr-dib.fr-fir {
margin-right: 0; text-align: right;
}
img.fr-dib.fr-fil {
margin-left: 0; text-align: left;
}
img.fr-dib {
margin: 5px auto; display: block; float: none;
}
img.fr-bordered {
box-sizing: content-box; border: solid 5px #CCC;
}
img.fr-shadow {
box-shadow: 10px 10px 5px 0px #cccccc;
}
img.fr-rounded {
border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;
}
</style><style>
p {
margin: 0;
}
span.fr-emoticon.fr-emoticon-img {
background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle;
}
span.fr-emoticon {
font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0;
}
blockquote {
border-left: solid 2px #5e35b1; color: #5e35b1; margin-left: 0; padding-left: 5px;
}
blockquote blockquote {
border-color: #00bcd4; color: #00bcd4;
}
blockquote blockquote blockquote {
border-color: #43a047; color: #43a047;
}
table.grid {
border-collapse: collapse;
}
table.grid td,
table.grid th {
border: 1px solid #ddd;
}
.fr-fic.fr-dib {
display: block; margin: 5px auto;
}
.fr-fic.fr-dib.fr-fir {
text-align: right; margin: 5px 0 5px auto;
}
.fr-fic.fr-dib.fr-fil {
text-align: left; margin: 5px auto 5px 0;
}
.fr-fic.fr-dii {
float: none; margin: 5px auto;
}
.fr-fic.fr-dii.fr-fil {
float: left; margin: 5px auto;
}
.fr-fic.fr-dii.fr-fir {
float: right; margin: 5px auto;
}
img.fr-dib.fr-fir {
margin-right: 0; text-align: right;
}
img.fr-dib.fr-fil {
margin-left: 0; text-align: left;
}
img.fr-dib {
margin: 5px auto; display: block; float: none;
}
img.fr-bordered {
box-sizing: content-box; border: solid 5px #CCC;
}
img.fr-shadow {
box-shadow: 10px 10px 5px 0px #cccccc;
}
img.fr-rounded {
border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;
}
</style><p><strong>In this guide we will cover:</strong></p><p><strong>- Setting Up a SQL Import</strong></p><p><strong>- Halo Integrator</strong></p><p id="isPasted"><strong>- Importing large datasets into custom tables</strong></p><p><br></p><p><br></p><p><strong><span style="font-size: 14pt;">Setting Up a SQL Import</span></strong></p><p>Head to Configuration > Integrations to find the "SQL Imports" module. Hover over this to ensure it is enabled - click the "+" icon if not.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImJjM2ZmMTVlLTFjMWEtNDZhOC04MDA4LTYwYzkyNGU0MTBlNSJ9.YCu0XOX6UZsCbRoK8UpyxpY_RfhbCpEX1fdDVemP4J0" class="fr-fic fr-fil fr-dib" width="211" height="149"></p><p><strong><span style="font-size: 10pt;">Fig 1. Enabling the module.</span></strong></p><p><br></p><p>Click into the module. You can add one, or multiple records (equating to the importing of different entities/from different databases). Click "New" in the top right to create a new record.</p><p><br></p><p>Give this a sensible name and select the entity you're looking to import. In this example, the assets entity has been picked.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImFhMWUyNDVlLWRlMmEtNGJiZC1hNDMwLWNkNzI1YzczOGM4ZSJ9.uECFFc77kW2-ziLD7B-tvG4aDm44ENqD5APDXuHYjzY" class="fr-fic fr-fil fr-dib" width="608" height="654"></p><p><strong><span style="font-size: 10pt;">Fig 2. New SQL import.</span></strong></p><p><br></p><p><strong><span style="font-size: 14pt;">Halo Integrator<br></span></strong></p><p>You then have the option to choose the integrator that will import data on a scheduled basis.</p><p><br></p><p data-pasted="true">If hosted with us, use the Halo Integrator with SIS SQL Provider 6. The Integrator also gives the option to restrict and filter these within the "Integrations" tab. If this is left empty, all imports will be run.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImMxNDdiMTg4LTJjZTYtNDNkMS04Njg5LWI1NWNjOTk3MWViNiJ9.5xyq5cOy_xhTHaKbcj2akSsN8RTH-krpM502AdVlEyE" class="fr-fic fr-fil fr-dib" width="443" style="width: 445px; height: 435.729px;" height="436"></p><p><strong><span style="font-size: 10pt;">Fig 3. Integrator import.</span></strong></p><p><br></p><p>If you have an On-Prem instance, use the DB Integrator instead.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjdhMzYyY2NiLTJiNzQtNGQ2OS05Y2ZiLWVkZGU1YWM3NTFhOCJ9.XNO1uAlxkSWP-Lxq2YkSDJ7Vw-COtNF6Dxj8WvPvfb4" class="fr-fic fr-fil fr-dib" width="746" style="width: 748px; height: 252.522px;" height="253"></p><p><strong><span style="font-size: 10pt;">Fig 4. Integrator selection</span></strong></p><p><br></p><p>Once you have chosen your integrator you will need to choose the method used for password storage in the 'Password Storage Method' field. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjE0OGNhY2EyLTFkYjctNDIzZS1hNjM4LWNjOGQ1YmNlZmU1OSJ9.cge0ePRE2Gtm78N7fvDrbXckXWBZjC2yMgN8LsNMfPE" class="fr-fic fr-fil fr-dib" width="440" style="width: 442px; height: 437.526px;" height="438"></p><p><strong><span style="font-size: 10pt;">Fig 5. Password storage method</span></strong></p><p><br></p><p><strong>Halo</strong>- When this method is chosen your password will be stored by Halo, it will be accessible via Halo's API if the Halo Integrator is chosen as the integrator too. If using the Halo DB integrator your password will not be accessible via the API. </p><p><strong>Azure Key Vault</strong> - When this method is chosen your password will be stored by Azure key Vault, it will not be accessible via Halo's API if the Halo Integrator is chosen as the integrator too. If you would like to use this method of storage you will need to configure a key vault in the Azure Key Vault integration. For information on setting up the Azure Key Vault integration, check out our guide: <a data-fr-linked="true" href="https://usehalo.com/haloitsm/guides/1715" id="isPasted" target="_blank" rel="noopener noreferrer">Azure Key Vault</a>. Once you have configured a Key Vault in Halo you will be able to choose which Key Vault to store the password in. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjA0MjFiMTA5LTQ5M2EtNDg2ZS04Y2E5LWE2YzU1NWRmMTkwMiJ9.byoaeMQUnqjDMQH2AXbhLr5u56WGT8I0l3Auz_8wd6g" class="fr-fic fr-fil fr-dib" width="321" height="166"></p><p><strong><span style="font-size: 10pt;">Fig 6. Choose Key vault to store password in</span></strong></p><p><br></p><p><strong>Recommended:</strong></p><p>If you are using the 'Halo integrator' to schedule imports we recommend using Azure Key Vault as the password storage method as this will ensure your password is not accessible via the API. If using the Halo DB integrator you can use Azure Key Vault to store passwords for increased security, however, even when Halo is used as the password storage method the password will not be accessible via the API.</p><p><br></p><p>If you have multiple Halo integrator applications you may wish to restrict which of these have access to this integration. By default all client IDs will be able to access On-Prem integrations, however, this can be disabled, allowing you to whitelist which client IDs can access the integration. </p><p><br></p><p>To do this head to configuration > advanced settings > disable 'Allow all client IDs to access all On-Prem integrations which use the Halo Integrator'. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjgwYWNkYzI3LWU0MTgtNDc4Yi1iZWYyLWFiZjJkOTQ0MjJkNyJ9.4zjGpPnlULtd5qHmVf7Ra1lQFP51W0oZ8hrGobnU2sA" class="fr-fic fr-fil fr-dib" width="783" style="width: 785px; height: 214.673px;" height="215"></p><p><strong><span style="font-size: 10pt;">Fig 7. Allow all client IDs to access all On-Prem integrations which use the Halo Integrator</span></strong></p><p><br></p><p>Now, on the integration setup page in Halo, you will have an option to add in the client IDs you would like to whitelist for this integration. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijg1Yjg0NTM3LWFlNWMtNDc3Ny1iZDYzLWNmYzkyNWRlNmJiNiJ9.FEjH7wZmRsKYIjjFV1oYnh0aItTu4ciIfSR9y1wL6Ho" class="fr-fic fr-fil fr-dib" width="644" style="width: 646px; height: 431.538px;" height="432"></p><p><strong><span style="font-size: 10pt;">Fig 8. Allowed client IDs for integration</span></strong></p><p><br></p><p>Here, enter the client IDs for the applications set up to authorise the connection between your Halo instance and your Halo Integrator. Only the integrators authorised using these client IDs will be able to access this integration. </p><p><br></p><p><strong><span style="font-size: 12pt;">SQL Connection</span></strong></p><p><span style="font-size: 11pt;">Input the connection details, such that Halo can communicate with the 3rd party DB.</span></p><p><br></p><p><strong><span style="font-size: 14pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjBiYjc3YmRlLWMwZWQtNGQ0NS05ZTM3LTc2NDcwOGQ4ZTAzYyJ9.m8-6-gZfND-Z1syQTyU7_YnBC_ufmTYKlutDEBoOjfA" class="fr-fic fr-fil fr-dib" width="542" height="523"></span></strong></p><p><strong><span style="font-size: 10pt;">Fig 9. SQL connection details.</span></strong></p><p><strong><br></strong></p><p><strong><span style="font-size: 12pt;">SQL Query</span></strong></p><p><span style="background-color: transparent;">The result of the query entered below will be used for the data import, so it is important that this query returns all records you wish to import & contains all fields (columns) with the data points you're looking to import into Halo:</span></p><p><br></p><p><span style="background-color: transparent;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImI2ODg1NTE2LTEzYzktNGQ4ZS1hNjczLWMwMjc4NzgwMjgzMSJ9.jGeZ6k4hTll0AUAhxFILpj4_4dMk5mUJjnxS_26Epks" class="fr-fic fr-fil fr-dib" width="1195" style="width: 1197px; height: 342.27px;" height="342"></span></p><p><span style="background-color: transparent; font-size: 10pt;"><strong>Fig 10. SQL query field.</strong></span></p><p><br></p><p><span style="font-size: 12pt; background-color: transparent;"><strong>Key Fields</strong></span></p><p><span style="font-size: 11pt; background-color: transparent;background-color: transparent;">There are some important fields you will need to add into your query, these are highlighted below:</span></p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImJmODY1MTljLWU3ZDQtNDM4Ny1iNGUwLTIxZDMwZWYyNmQzNCJ9.B3t9jiXPVVw4MiYPTUOeez8-hGdKa-uQAlroBHie8uI" class="fr-fic fr-fil fr-dib" width="1210" style="width: 1212px; height: 545.304px;" height="545"></p><p><strong><span style="font-size: 10pt;">Fig 11. key fields for query</span></strong></p><p><br></p><p>Add the column names into their respective text fields (screenshot above)</p><p><br></p><p>Directly below these options, you have a table for further field mappings:</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImI5NWRmN2UxLTkyN2ItNGE0OC04MWQwLTI0NTA2ZmM4MmI0MiJ9.E8g6rCoH1VjWz-BLjGH4ztvU4tRsujJLl7tNfrMxme4" class="fr-fic fr-fil fr-dib" width="1219" style="width: 1221px; height: 636.623px;" height="637"></p><p><strong><span style="font-size: 10pt;">Fig 12. Field mappings table</span></strong></p><p><br></p><p>Add to this table & specify the column header from your SQL query and the field it should update in Halo:</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImFlMGZiZGIyLWEzMzUtNDAyMC05YmI0LTY0ZmU4YmU0YmMzMyJ9.iOSQm9hNmGmQ6HzZyr414DxbURPY0FX0B1WKwbD0_I0" class="fr-fic fr-fil fr-dib" style="width: 571px; height: 318.521px;" width="571" height="318.521"></p><p><strong><span style="font-size: 10pt;">Fig 13. Mapping column header to Halo field</span></strong></p><p><br></p><p>If a field name exists with the same name as the column header, the respective field in Halo will be updated. Else, a new field will be created.</p><p><br></p><p>Data can be imported to the 'Departments' field for the User entity. This determines the department your user is in. You may notice another field available to map called 'Department', this is just a free text field against user the user, the 'Departments' field should be mapped to set the user's department. </p><p><br></p><p>Finally, you have some additional options regarding the behaviour of the import:</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjM5Y2U4Y2Y1LWU3NWEtNGU0NC05ZjgwLTFkNWZiY2FkZTlkYyJ9.L6PDR6U6192TVlNWTcHVbYiTt7DOeqYfLNzny3abS4I" class="fr-fic fr-fil fr-dib" width="598" height="373"></p><p><strong><span style="font-size: 10pt;">Fig 14. Additional options</span></strong></p><p><br></p><p><span style="font-size: 11pt;">For more information on completing SQL imports for software licences/subscriptions, check out <a data-fr-linked="true" href="https://usehalo.com/haloitsm/guides/1567/" id="isPasted" target="_blank" rel="noopener noreferrer">SQL Imports - Licences and Subscriptions</a>.</span></p><p><br></p><p><strong><span style="font-size: 14pt;">Importing large datasets into custom tables</span></strong></p><p>This section will be relevant if you are looking to import a large set of data into a custom table in Halo. </p><p><br></p><p>Large sets of SQL data can be imported into a Halo custom table in one go as this version supports the batching of imports. If attempting imports prior to this version you will need to ensure the dataset being imported is not too large, if an overly large dataset is imported at one time the import may timeout before it can complete. Timeout should not occur as data will be imported in batches.</p><p><br></p><p>This applies to both table import types 'Update all rows, including removing rows not in the import' and 'Only add/update imported rows, don't remove rows not in the import'. </p><p><br></p><p><br></p>
